Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
omarelmb123
Helper I
Helper I

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 ?

1 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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

3 REPLIES 3
edhans
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors