Hi
I am using power query to consolidate data from a few underlying data sources to create one large consolidated table to review.
I have been selecting connection only, and add to data model (although I am still not using this functionality yet, I am still practicing getting it in a large table and pivoting). All the data is from other excel workbooks.
When I eventually load the consolidated table, its about 370,000 rows and approx 20 columns, a lot is redundant as there are multiple rows where the value is zero. The file size however is now 69MB.
Is it possible that the table alone is causing it to be this large? Does adding to data model cause the data to be stored in the workbook and is that bloating the file? If so should i not select add to data model until I am ready to tackle power pivot?
Thanks
Hi Declan,
If you're loading the data to both the Power Pivot model and a Table in the Excel worksheet, then you are doubling up on the data stored in the file. Power Pivot is more efficient at compressing data than a table in an Excel worksheet, so Power Pivot should be your preference. Also, you should definitely remove redundant rows from your data in Power Query before loading to Power Pivot.
Mynda
