Model Grows Exponentially - Why???

[pawlowski6132]

In Power BI, I injest an Excel Workbook that's 259KB. I do some minor Power Query edits and the model grows to 500MB in size and takes forever to load to the Editor after "Close and Apply. Only 1600 rows are being loaded.

The PBIX is only 219KB which, is to be expected.

Where should I start to look? Is there an approach to evaluate what's going wrong?

[Jimmy801 · Accepted solution]

Hello @pawlowski6132 

have you been able to solve the problem with the replies given?

If so, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

All the best

Jimmy

[pawlowski6132]

As a prelude, the buffer function works and produces noticeably faster queries. However, they were still too long for me. I ended up with a Python script solution. Much leaner query and worked super fast.

[Jimmy801]

Hello @pawlowski6132 

This is probably caused by an Table.AddColumn or a Table.SelectRows. The reason for this is that depending on how your query works, the Excel-content is queried multiple time, mabe even for every new row. 

To solve this just surround your second step (where you are accessing the sheet-table) with a Table.Buffer

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[pawlowski6132]

Thanx for the feedback. Let me check that approach.

Would it be different if datasource was SQL DB or CSV File do you think?

[Jimmy801]

Hello @pawlowski6132 

i know that some datasources are chached, and therefore there should be no problem (SQL should be part of that). CSV-files should be all the same. Ok... check it and let us know

all the best

Jimmy

[pawlowski6132]

One more quick question, I've been looking high and low for some examples using the Table.Buffer syntax. I just can't seem to find an example that I can use in my code that works. FWIW, here's my query. I will also be trying to lad the dataset in to Azuure SQL or load in CSV to see if that improves things but, would prefer to leave in Excel.

I'll be working on this this afternoon so, will hopefully get somewhere.

[Jimmy801]

Hello @pawlowski6132 

for sure CSV would be faster to query.

just put a Table.Buffer arround your third step like:

#"Changed Type" = Table.Buffer(Table.TransformColumnTypes(Table1_Table,......

Don't forget to put a ")" at the end of the step

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy