Excel 2016 and Power Pivot file size

serky

New Member
Joined
Jun 30, 2014
Messages
39
Hi all
this is just a general question re what drives files size in a Power Pivot model. I need to share my files with other users so they need to be as small as possible as most users only have a basic computer set up. i have created a file with only a very small amount of data in the data model and several Power Query queries (load to data model or connection only). The source data file is only ~1MB.
The analysis was ~ 2MB in size but when I added 2 more queries (dependent on other queries), the file size started to increase significantly (30MB). To try to reduce the size, I started to delete various items:
1. deleted some queries => no change in file size
2. deleted all the queries => no change in file size
3. deleted some tables in the data model => no change in file size
4. deleted all the tables in the data model => no change in file size
5. checked the pivot table caches => multiple caches of 0 records (I assume from the Power Pivot model)
6. deleted some sheets => no change in file size
7. deleted all the sheets except one => file size reduced to very small size

So, I have a number of questions:
1. is it possible to reduce the file size gradually by removing some items only
2. how can I identify what is contributing to the size
3. what is best practice in terms of creating an analysis file to ensure minimum size (I understand about # columns/rows and # unique data but am wondering about other elements eg - creating new pivot tables by copying from existing sheets rather than inserting a new one; creating queries a certain way; creating connections a certain way etc)
4. is there anything else that can help?

I am finding that I have to re-create the file from the beginning and check the file size after every new data import, table creation etc to manage the problem.

This is just not practical.

Any help appreciated.

PS I did not experience this problem with Power Pivot 2010 but I was not using Power Query in those models either.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
PowerPivot and PowerQuery works on references so after delete all queries from PQ or tables from PP it doesn't change size of the file
try to save file as xlsb type.
try to reorganise your data and remove all not necessary source tables or something like that
maybe it will help
 
Upvote 0
don't load all query tables to the sheet, only optimized result(s)
try to work on referensces not copies of table in query editor
 
Upvote 0
Can you explain some of your responses a little more:

What do you mean that PQ and PP work on references? Is that like a cache? If I decide I don't need something in a model, how do I get rid of all references as it's no longer needed? Does the model just keep getting bigger no matter what?
What are the implications of saving as an xlsb?
I don't have any unnecessary source tables so can't reduce the size that way.
No queries are loaded to any sheets; just to the data model if needed, else a connection only.
How do I work on references in PQ and not a copy of a table?

Thanks
 
Upvote 0
What do you mean that PQ and PP work on references?
works on references to original data (same as "as connection" in PQ

What are the implications of saving as an xlsb?
did you try it?

How do I work on references in PQ and not a copy of a table?
in Query editor if you right click on query table you will see Duplicate or Reference so maybe use Reference

I am curious what you did:
but when I added 2 more queries (dependent on other queries), the file size started to increase significantly (30MB)
that the size has grown so dramatically?

edit
any chance to see this (desensitized) file?
 
Last edited:
Upvote 0
Ok - I will prepare a desensitised file and then I can ask more questions. How do I share the file?
 
Upvote 0
google drive or onedrive or any other free server for sharing file eg. tinyupload.com , without loging
then paste link to the file here
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top