Merging tables with Condition in a large right table

[omarelmb123]

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 ?

[edhans]

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.

[omarelmb123]

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 ?

[edhans · Accepted 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.