Forum Discussion

ghdunn's avatar
ghdunn
Helper III
6 years ago
Solved

Conditional Merge or Table.SelectRows

I have just spent several hours working through a range of posts on Merges and Table.SelectRows but cant find anything quite right.

 

TableA

Field1,Field2

a,w

b,x

c,y

d,z

 

TableB

Field1,Field2,Field3

a,w,p

b,x,q

c,r,y

d,s,t

 

I want an inner join, to bring back all rows where:

 

TableA.Field1=TableB.Field1

AND

(TableA.Field2=TableB.Field2 OR TableA.Field2=TableB.Field3)

 

So:

 

Merged Table

Field1,Field2,Field1,Field2,Field3

a,w,a,w,p

b,x,b,x,q

c,y,c,r,y

 

 

I thought something like:

Table.SelectRows(TableB,TableA.Field1=TableB.Field1 AND TableA.Field2=TableB.Field2 OR TableA.Field2=TableB.Field3) 

 

might work, but i can't even get a simpler version of:

 

Table.SelectRows(TableB,TableA.Field1=TableB.Field1) to work so clearly i am not understanding the syntax.

 

I could add some further steps to get the desired output but hoped there was a more elegant solution.

 

Any help appreciated.

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    try this

       cp = Table.AddColumn(tabA, "join", each Table.SelectRows(tabB, (rB)=>rB[Field1]=_[Field1] and (rB[Field2]=_[Field2] or rB[Field3]=_[Field2]))),
    tje = Table.ExpandTableColumn(cp, "join", {"Field1", "Field2", "Field3"}, {"join.Field1", "join.Field2", "join.Field3"}),
    fr = Table.SelectRows(tje, each ([join.Field3] <> null))
in
   fr

     

     

8 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    Please try an approach like this.  Example with your data below.  You can do a merge on Field1, expand the table, then add a custom column that returns 1 if either field 2 or 3 in B matches field 2 in A.  Then just filter out the rows that don't equal 1.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUorViVZK1CkH02AiSacCwUnWqURwUnSqlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Field1,Field2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Field1,Field2", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([#"Field1,Field2"] <> "")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Field1,Field2", Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.Csv, false), {"Field1,Field2.1", "Field1,Field2.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Field1,Field2.1", type text}, {"Field1,Field2.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Field1,Field2.1", "Field1"}, {"Field1,Field2.2", "Field2"}}),
    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"Field1"}, TableB, {"Field1"}, "TableB", JoinKind.LeftOuter),
    #"Expanded TableB" = Table.ExpandTableColumn(#"Merged Queries", "TableB", {"Field2", "Field3"}, {"Field2.1", "Field3"}),
    #"Added Custom" = Table.AddColumn(#"Expanded TableB", "Custom", each if [Field2]=[Field2.1] or [Field2]=[Field3] then 1 else 0),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Custom"})
    in
    #"Removed Columns"

     

    Table B data (included only since the query above references this query, called TableB)

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUorViVZK1CnXKQCzwESSToVOIYKbrFOkU4ngpugU65QoxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Field1,Field2,Field3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Field1,Field2,Field3", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([#"Field1,Field2,Field3"] <> "")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Field1,Field2,Field3", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Field1,Field2,Field3.1", "Field1,Field2,Field3.2", "Field1,Field2,Field3.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Field1,Field2,Field3.1", type text}, {"Field1,Field2,Field3.2", type text}, {"Field1,Field2,Field3.3", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Field1,Field2,Field3.1", "Field1"}, {"Field1,Field2,Field3.2", "Field2"}, {"Field1,Field2,Field3.3", "Field3"}})
    in
    #"Renamed Columns"

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

    • ghdunn's avatar
      ghdunn
      Helper III
      6 years ago

      Many thanks for the suggestion mahoneypat 

       

      Your approach includes a number of steps at the end which I had considered but was hoping to avoid.  I was hoping there might be some more elegant syntax around a 'conditional join' where the join carried out the the OR test.  Either that or including the conditional logic of Table.SelectRows.

       

      Do you think either of those approaches is possible in any way?

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        try this

           cp = Table.AddColumn(tabA, "join", each Table.SelectRows(tabB, (rB)=>rB[Field1]=_[Field1] and (rB[Field2]=_[Field2] or rB[Field3]=_[Field2]))),
    tje = Table.ExpandTableColumn(cp, "join", {"Field1", "Field2", "Field3"}, {"join.Field1", "join.Field2", "join.Field3"}),
    fr = Table.SelectRows(tje, each ([join.Field3] <> null))
in
   fr