Power Query Join 2 on 2 columns in OR condition

[lrobb]

Hello,

I need to join 2 tables, but I need to join with an OR condition where it can join on ticket number or join on order number. If either are true I need the join to be made.

is this possible? I know I can do this with "AND" but not sure about OR.

Thank you,

[tackytechtom · Accepted solution]

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/

[lrobb]

I see what you did! two joins and then conditional colums and cleaning up the extras... that should work! that will also account for a prioritization of "use colA first, if null use colB"