Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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
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.
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
Thanx for the feedback. Let me check that approach.
Would it be different if datasource was SQL DB or CSV File do you think?
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
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.