Excel 2016 and Power Pivot file size

[serky]

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.

 

[sandy666]

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

 

[sandy666]

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

 

[serky]

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

 

[sandy666]

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?

 

[serky]

Ok - I will prepare a desensitised file and then I can ask more questions. How do I share the file?

 

[sandy666]

google drive or onedrive or any other free server for sharing file eg. tinyupload.com , without loging
then paste link to the file here