{"id":672,"date":"2025-05-17T06:56:31","date_gmt":"2025-05-17T06:56:31","guid":{"rendered":"https:\/\/datadandies.nl\/?p=672"},"modified":"2025-05-30T08:37:46","modified_gmt":"2025-05-30T08:37:46","slug":"672","status":"publish","type":"post","link":"https:\/\/datadandies.nl\/index.php\/2025\/05\/17\/672\/","title":{"rendered":"Multi column or multi condition JOIN using Power Query M"},"content":{"rendered":"\n

Ever wondered how to do a Multi-Column-JOIN in Power Query M?<\/p>\n\n\n\n

It is so simple to join TABLE_A to TABLE_B in SQL using multiple conditions on multiple columns:<\/p>\n\n\n\n

SQL<\/span><\/span><\/path><\/path><\/svg><\/span>
SELECT<\/span> <\/span><\/span>\n*<\/span><\/span>\nFROM<\/span> TABLE_A A<\/span><\/span>\nJOIN<\/span> TABLE_B B <\/span><\/span>\n  <\/span>ON<\/span> A.ID = B.ID<\/span><\/span>\n  <\/span>AND<\/span> A.DATE <\/span>BETWEEN<\/span> B.STARTDATE <\/span>AND<\/span> B.ENDDATE<\/span><\/span><\/code><\/pre>SQL<\/span><\/div>\n\n\n\n
But how do we replicate this in Power Query M?<\/p>\n\n\n\n
Turns out we can add a column using the Power Query M function Table.SelectRows. Take a look at the image below.<\/p>\n\n\n\n
One downside to this approach is that the performance is terrible for large datasets. This is likely due to the fact that as far as I understand, a nested loop is used: each row of TABLE_A is checked against each row of TABLE_B.<\/p>\n\n\n\n
In SQL, JOINs like these perform much better for several reasons: indexing, stats, more efficient query plans like hash JOINs, etc. One thing worth exploring in order to improve performance of this method in Power Query M might be to first JOIN TABLE_A to TABLE_B using the ID column, and then apply the second date-based condition. I haven\u2019t explored this option yet, but if you\u2019re limited to Power Query M and need to make a multi column JOIN happen, that is something I would try.<\/p>\n\n\n\n
Power Query<\/span><\/span><\/path><\/path><\/svg><\/span>
let<\/span><\/span>\n  Source = ...<\/span><\/span>\n<\/span>\n  etc.<\/span><\/span>\n<\/span>\n  <\/span>\/\/ Start logic multi condition JOIN<\/span><\/span>\n  #"AddColumnMultiConditionJoin" = Table.AddColumn(<\/span><\/span>\n    <\/span>\/\/ Giving the fact table the "alias": "c" (which is technically not an alias, but a lambda function parameter representing rows of the fact table. It functions as a loop where each row from the fact table "p" is checked against each row from the dimension table "c")<\/span><\/span>\n    #"TableFromPreviousStep", <\/span>"Custom"<\/span>, (c)=>             <\/span><\/span>\n      Table.SelectRows(<\/span><\/span>\n        <\/span>\/\/ Giving the dimension table containing an ID-column and a start- and enddate column an "alias": "p"<\/span><\/span>\n        #"DimensionTableWithIdColumnAndStartdateAndEnddate", (p)=> <\/span><\/span>\n            <\/span>\/\/ Multi condition JOIN using...<\/span><\/span>\n            c[ID_COLUMN] = p[ID_COLUMN]                     <\/span>\/\/ ... ID_COLUMN and...<\/span><\/span>\n            and c[DATE] >= p[STARTDATE]                     <\/span>\/\/ ... DATE must be larger than STARTDATE and... <\/span><\/span>\n            and c[DATE] <= p[ENDDATE]                       <\/span>\/\/ ... DATE must be smaller than ENDDATE<\/span><\/span>\n            )<\/span><\/span>\n          )<\/span><\/span>\nin<\/span><\/span>\n  #"AddColumnMultiConditionJoin"<\/span><\/span><\/code><\/pre>Power Query<\/span><\/div>\n","protected":false},"excerpt":{"rendered":"
Ever wondered how to do a Multi-Column-JOIN in Power Query M? It is so simple to join TABLE_A to TABLE_B in SQL using multiple conditions on multiple columns: But how do we replicate this in Power Query M? Turns out we can add a column using the Power Query M function Table.SelectRows. Take a look…<\/p>\n
Read More<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[3,11,4],"class_list":["post-672","post","type-post","status-publish","format-standard","hentry","category-blog","tag-powerbi","tag-powerquery","tag-sql"],"_links":{"self":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts\/672","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/comments?post=672"}],"version-history":[{"count":3,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts\/672\/revisions"}],"predecessor-version":[{"id":676,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts\/672\/revisions\/676"}],"wp:attachment":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/media?parent=672"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/categories?post=672"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/tags?post=672"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}