Forum Discussion
Merging tables with Condition in a large right table
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 ?
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.
3 Replies
Power 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.
doing 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.
