Power Query Join 2 on 2 columns in OR condition

Hi lrobb ,

 

Not sure whether this satisfies your requirement, but I would have a potential solution here:

 

TableA:

 

 

TableB:

 

We'd like to join TableA and TableB on either JoinCol1 or JoinCol2

 

The result:

 

Firstly, I joined TableA with TableB on just JoinCol1. Then, I joined the resulting table and TableB again but this time on JoinCol2. Lastly, I used an "ISNULL" logic where if AttributeTable2 of the first join (on JoinCol1) was null I used AttributeTable2  (on JoinCol2) and vice versa.

 

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYseCgpxUpVidaCUnIM8IiJ0S84AQLOQM5BoDcUBqYhFYwAXIMQFi78zyTLCAK5BjCsTO+cn5eaUlSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [JoinCol1 = _t, JoinCol2 = _t, AttributeTable1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"JoinCol1", type text}, {"JoinCol2", Int64.Type}, {"AttributeTable1", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"JoinCol1"}, Table23b, {"JoinCol1"}, "Table23b", JoinKind.LeftOuter),
    #"Expanded Table23b" = Table.ExpandTableColumn(#"Merged Queries", "Table23b", {"JoinCol1", "JoinCol2", "AttributeTable2"}, {"Table23b.JoinCol1", "Table23b.JoinCol2", "Table23b.AttributeTable2"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded Table23b", {"JoinCol2"}, Table23b, {"JoinCol2"}, "Table23b", JoinKind.LeftOuter),
    #"Expanded Table23b1" = Table.ExpandTableColumn(#"Merged Queries1", "Table23b", {"JoinCol1", "JoinCol2", "AttributeTable2"}, {"Table23b.JoinCol1.1", "Table23b.JoinCol2.1", "Table23b.AttributeTable2.1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table23b1", "AttributeTable2", each if [Table23b.AttributeTable2] = null then [Table23b.AttributeTable2.1] else [Table23b.AttributeTable2]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Table23b.JoinCol1", "Table23b.JoinCol2", "Table23b.AttributeTable2", "Table23b.JoinCol1.1", "Table23b.JoinCol2.1", "Table23b.AttributeTable2.1"})
in
    #"Removed Columns"

Let me know if this helps 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/