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.
Hello,
For required calculations, I am merging a query 6 times with 3 other queries (each query merged twice with different keys) and adding conditional columns after every two merges.
In Power Query Editor it all works pretty nicely. I can see the correct results. But after I click "Close & Apply", I can see it just keeps on running for over 1.5 Hour and loading about whopping 50+ CRORES of Rows and still continuing!! I eventually had to "cancel" the load to fine tune my query merge steps. Tried Table.Buffer but that did not change the behaviour at all. I can see the "View Native Query" is grayed out right from the first step so probably no folding.
Questions:
1. Why is it attempting to load more than 10,000 times the number of records in source table (50+ crores vs 50k)? Is it internally loading up the entire table of the right side Query for each row of the left side Query during the merge operation?
2. How can I fix this problem so that my model loads normally as it should (2-3 mins should be okay)
Please see steps below:
Any guidance would be deeply appreciated, thanks.
Regards, Nirmit
Solved! Go to Solution.
Thanks for response. My connection mode is all "Import". I have actually solved this problem. Explaining it below for everyone's benefit if at all required.
The issue was due to data quality and not due to merging different source types. The culprit were "null" values. After detailed investigation, I found out that my both the left and right tables join columns/keys contained "null" values (not defined as primary keys). It may happen data-wise. But when you merge such datasets, each "null" record in Left table will basically fetch all the matching "null" records from Right table (which is how left outer join works anyway), latter may run into thousands or more. This results into cartesian product of resulting number of rows and as a result, inflation of output data. Of course processing will take time depending upon data volume. In my case there were hundreds and thousands of null records in Left and Right table respectively, hence the slowness and huge records count. After I handled null (and duplicates) from both tables, it started working very normally, loading up in 2-3 minutes. Hope it helps.
-Nirmit
Thanks for response. My connection mode is all "Import". I have actually solved this problem. Explaining it below for everyone's benefit if at all required.
The issue was due to data quality and not due to merging different source types. The culprit were "null" values. After detailed investigation, I found out that my both the left and right tables join columns/keys contained "null" values (not defined as primary keys). It may happen data-wise. But when you merge such datasets, each "null" record in Left table will basically fetch all the matching "null" records from Right table (which is how left outer join works anyway), latter may run into thousands or more. This results into cartesian product of resulting number of rows and as a result, inflation of output data. Of course processing will take time depending upon data volume. In my case there were hundreds and thousands of null records in Left and Right table respectively, hence the slowness and huge records count. After I handled null (and duplicates) from both tables, it started working very normally, loading up in 2-3 minutes. Hope it helps.
-Nirmit
Hi @Anonymous ,
What is the connection mode? Please try to use composite models in Power BI Desktop.
Please refer to the following document to see if it helps you.
https://docs.microsoft.com/power-bi/transform-model/desktop-composite-models
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |