Forum Discussion
Handling huge amounts of data
I ran into something similar recently. The lessons I learned were
- don't try to be cute and use List.Generate or something like that. Create eight (yes, eight) separate Power Query queries, one for each export. This will yield maximum possible performance (don't forget to set parallelism to 8 or more).
- Use Binary.Buffer when fetching the files from SharePoint. That will tell the SharePoint server that you are impatient and not willing to wait for the chunking.
- Do as few transforms as possible. Maybe change column types, but that's it.
- load all queries into Power BI and do a UNION calculated table there.
Super UserI ran into something similar recently. The lessons I learned were
- don't try to be cute and use List.Generate or something like that. Create eight (yes, eight) separate Power Query queries, one for each export. This will yield maximum possible performance (don't forget to set parallelism to 8 or more).
- Use Binary.Buffer when fetching the files from SharePoint. That will tell the SharePoint server that you are impatient and not willing to wait for the chunking.
- Do as few transforms as possible. Maybe change column types, but that's it.
- load all queries into Power BI and do a UNION calculated table there.
Wow, those are some interesting and new findings. Generally, you prefer to keep the data model in Power BI small and clear, but obviously the performance of Power BI (Measures and Calculated Tables) seems to be much more effective. Thank you very much!
- lbendlin
It is important to note that this is a corner case. Usually you want code that is more concise, easier to maintain, even if it is a little slower. But in your scenario when brute force power is required you need to compromise on the code maintainability.