Join on multiple columns using Power query

Anonymous 

When you have to join on multiple colums - consider adding a custom column where you concatenate the values that are part of your match criteria.   Use Text.From() to convert any numbers into text so they concatenate properly.  

Once you have a column created in each table - you can use it as the basis for your merge - making sure to select that column in each of your two tables.  

Anonymous - you do not need to concatenate in Power Query to join on multiple columns. In the Merge box, just select your first column, then CTRL-CLICK on the 2nd, 3rd, etc.

It will put numbers next to the columns in the order you click.

THen in your 2nd table, click on the same columns you want to merge on in the same order.

There is not a way to join based on <= using the merge feature. It only supports:

• left/right join
• inner join
• outer join
• left/right anti-join

You can do a cartesian join too outside of the merge tool. 

You can do it though with a Table.SelectRows(). It may not perform well on large data sets. Consider you have two tables:

One called Table:

One called Other Data:

The following code, using the Table.SelectRows() function below will "merge" the [Other Data] table into the [Table] table when Data from Other Data is <= Data from Table.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSBEyagkkzMGkOJi3ApKVSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", Int64.Type}}),
    #"Other Merge" = 
        Table.AddColumn(
            #"Changed Type",
            "Other Merge",
            each
                let
                    varData = [Data]
                in
            Table.SelectRows(
                #"Other Data",
                each [Data] <= varData
            )
        ),
    #"Expanded Other Merge" = Table.ExpandTableColumn(#"Other Merge", "Other Merge", {"Codes"}, {"Codes"})
    
in
    #"Expanded Other Merge"

You can see my PBIX here if you want to play with it.

Hi Anonymous ,

Maybe these blog can help you. 

Join Conditions In Power Query, Part 1 

PowerQuery(M)agic: Conditional Joins using Table.SelectRows()