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 evryone,
its been days now im trying to improve the peformance of my query:
I want two merge two tables with WHERE condition on a large data set. Here's the working code but it s really bad perfomance. i've tried to buffer but still not working :
BufferFact = Table.Distinct(#"Sales"),
#"Join" = Table.AddJoinColumn(#"Valeur remplacée4", {"Customer_ID"},
BufferFact, {"SalesID"}, "All"),
// Where condition :
#"Condition" = Table.AddColumn(#"Join","All_filtered" ,
(outer)=> let a = outer[#"Start_Date"] in Table.Max(Table.SelectRows( outer[#"All"] , (t)=> t[#"Sales_date"] <= a), "Sales_date" ))
The last action take so long , even if i buffer the sales table it get worse because it s a large data set.
Maybe call a function that will execute the filtering before joining ?
Please any idea how to solve this issue ?
Solved! Go to Solution.
If you don't have a database upstream to do this in, then I suggest using Dataflows to stage and filter the data. A custom function will not work magic. You still are trying to join every single record with a filtered table. Power Query is absolutely horrible at doing that.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingPower Query doesn't work well across rows like this as you've found. Great for 10K records, not so much for millions. I think this needs to be done upstream in a relational database.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reportingdoing it in Power bi will increase my model size. So im thinking how can i add filters on the right table before doing the joining. invoking a custom function maybe ? is there any trick to solve this issue ?
If you don't have a database upstream to do this in, then I suggest using Dataflows to stage and filter the data. A custom function will not work magic. You still are trying to join every single record with a filtered table. Power Query is absolutely horrible at doing that.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering 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.