file size - power pivot and data model data connections

[Anonymous]

Hey guys and girls,

I have started using power queries and power pivot quite heavily. One thing really confuses me is the data connections and file sizes.
I have power pivots created in a workbook with all source data in other tabs of the same workbook. I have been importing tables from this workbook into the data model, also tables transformed with power queries in the same workbook too.

However it seems every time when i make a copy of this file, the data model data sources still connect to the old workbook, which makes the whole file bigger and bigger, and now it seems the only way to resolve it is to break all the links and restart…

Could you please help me understand if I’m doing anything wrong? Should I be importing data into data model only through power queries "load to data model"?

I guess the key is I want to understand what's the most efficient way to structure and import data (excel tables), data model and power pivot.

Thanks!

CJ

[v-juanli-msft]

Hi @Anonymous 

As tested, if i copy one excel, i can find the copied excel file uses the data in the current excel not the original excel.

You can check if yours uses the current excel file data as a data source.

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks Maggie,

It's these worksheet connection links when looking at queries and connections. They're linking to prior months worksheets.

Some of them when I go into connection properties i could see they're used in pivots in this current workbook, which means I couldn't get rid of them - tried and it broke the links for data model and power pivots.

Some of them doesn't get used in pivots. When I delete them, they reduce the file size a lot.