Forum Discussion
Data from Power Query loading incredibly slow to BI
Hello,
I have a problem relating to loading data that was transformed in Power Query to Power BI. In short, its currently been 3 days of non-stop loading. I have multiple tables, and there are a lot of tranformations and table merges performed (around 200 steps) in the main table, which is the only one I am trying to load currently.
This is what is currently being shown in the loading screen:
Furthermore, my CPU and RAM are not being used that much comparatively to other times I have tried to load data. The two tables shown in the screenshot have sources that are less than 200mb in size. Other tables utilized only have a couple of rows.
Is this normal behavior? I did expect to have a very long load time due to the amount of heavy transormations present, but is it correct for Power BI to display 651GB (which is still increasing) from a table I'm not trying to load and which wheighs less than 200mb? Have I not configured something correctly?
4 Replies
Do you have a lot of nested joins that get expanded? A lot of sorts? Groups will do it as well. Also, I'm CERTAIN that you could benefit from using a remove duplicates step right before your joins, so that your joins know that they are dealing with unique values. If any of the dimensions are smallish, distinct AND buffer them before the join.
I bet that even just using the "Remove Duplicates" GUI function will dramatically improve your performance. Even if you know that you have no duplicates, removing duplicates makes that column the primary key, and asserts that there are no duplicates.
Give it a shot.
--Nate
Hi Anonymous ,
You may take a look at the official document: Best practices when working with Power Query - Power Query | Microsoft Docs:
It's entirely possible to create a single query that contains all the transformations and calculations that you may need. But if the query contains a large number of steps, then it might be a good idea to split the query into multiple queries, where one query references the next. The goal of this approach is to simplify and decouple transformation phases into smaller pieces so they're easier to understand.
Some blogs/ similar threads:
Speed Up Power Query In Power BI Desktop By Allocating More Memory To Evaluation Containers
Optimising The Performance Of Power Query Merges In Power BI, Part 3: Table.Join And SortMerge
Solved: Power BI in-memory RAM = Overload - Microsoft Power BI Community
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.