October 16, 2015
I have an Excel file on a SharePoint folder of about 1 MB. These Excel file contains 10 worksheets with 60 to 90 columns and between 20 to 300 rows of data.
I have another Excel file were I load all the worksheets of this file, via Power Query, to the data model, as connection only. These data model file becomes now about 8 MB. I don't understand why this is. The data is stored only once in the data model and I thought that the data model is able to compress the data. Is there any thing I can do to reduce the file size?
July 16, 2010
Are you able to share the query file and source files?
Power Pivot's compression works best when there are duplicate values in a column. If you have columns of unique values, then there's nothing to compress, so it may be that you have a lot of unique data, as opposed to typical data structures where many columns have repeating values that can be compressed into single records.