• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Reducing File Size

S

Shay A

Member
Hi, I have an Excel workbook which contains about 12 worksheets and 12 Pivot Tables respectively. The database for those pivot tables is externally connected (with Power Query). Now, the file size is about 3MB, is there any way to reduce it? I read online that binary files can't be read by Power Query, but what if I saved the file containing the pivot tables as binary and keeping the database file as xlsx file? Can Power Query still work? Or maybe there is another way to reduce file size?

Many thanks,
Shay
 
Yes it will work. It's better to keep source as xlsx, and change workbook containing model to xlsb. As source workbook file format has no relevance on size of workbook containing connections/pivots. It's solely dependent on model size and what's contained in the workbook itself.

Also, PowerQuery is more stable when reading from xlsx file, rather than xlsb.

There are many ways to reduce size, but it's really dependent on each use case.

Couple of suggestions...
1. Only load tables absolutely needed for analysis to model. All other intermediate tables used in transformation, just keep as connection only.
2. If able to, aggregate data even before loading to model. Ex: Aggregate hourly data to daily level etc.
 
Thank you, I have another question on Power Query connections, but I will post post it separately from this thread.
 
You must log in or register to reply here.
Back
Top