Forum Discussion

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,Fi...

Need Help

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

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

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
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
- ghdunn
  Helper III
  6 years ago
  I like this Anonymous as it seems to be what i had in mind...
  
  But...trying to incorporate your code in mine....just adding the first row
  
  let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUapQitWJVkoCsirBrGQgqwrMSgGyypViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Field1 = _t, Field2 = _t]),
  #"Changed Type" = Table.TransformColumnTypes(Source,{{"Field1", type text}, {"Field2", type text}}),
  #"Added Custom" = Table.AddColumn(tabA, "join", each Table.SelectRows(tabB, (rB)=>rB[Field1]=_[Field1] and (rB[Field2]=_[Field2] or rB[Field3]=_[Field2])))
  in
  #"Added Custom"
  
  I get:
  
  Expression.Error: A cyclic reference was encountered during evaluation.
  - Anonymous
    Not applicable
    6 years ago
    If this code is, as I suppose, inside query which load tableA:
    you should change my tabA with the variable of previuos step namely #"Changed Type".
    Also you should change tabB with the variable referencing tableB value, wich may be the name of the query where table B is loaded in.
    
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUapQitWJVkoCsirBrGQgqwrMSgGyypViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Field1 = _t, Field2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Field1", type text}, {"Field2", type text}}),
    #"Added Custom" = Table.AddColumn(tabA, "join", each Table.SelectRows(tabB, (rB)=>rB[Field1]=_[Field1] and (rB[Field2]=_[Field2] or rB[Field3]=_[Field2])))
    in
    #"Added Custom"
    
    PS
    how and where is the code related to table B?
mahoneypat
Microsoft Employee
6 years ago
Here is another approach to consider. Unpivot the two columns in Table B, then join on two columns (Field1 and the new Unpivoted Column Value). Here are the two example queries:

Table A
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", "Field2"}, #"TableB unpivot", {"Field1", "Value"}, "TableB unpivot", JoinKind.LeftOuter),
#"Expanded TableB unpivot" = Table.ExpandTableColumn(#"Merged Queries", "TableB unpivot", {"Value"}, {"Value"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded TableB unpivot", each ([Value] <> null))
in
#"Filtered Rows1"

Table B
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"}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Field1"}, "Attribute", "Value")
in
#"Unpivoted Columns"
- ghdunn
  Helper III
  6 years ago
  Again mahoneypat ..massively appreciative, but I need to explore whether some kind of conditional join is possible before exploring any more involved approach.
  
  In MS Access I think I can do this as simply as this:
  
  SELECT TableA.*, TableB.*
  FROM TableA INNER JOIN TableB ON TableA.Field1=TableB.[Field1]
  WHERE TableA.Field2=TableB.[Field2] Or TableA.Field2=TableB.[Field3];
  
  So I am looking for something similar to that.
  
  I am building this for non-technical users, so i would like something i can explain in just a few lines of code.
  
  Thanks!
  
  Ged

Forum Discussion

Conditional Merge or Table.SelectRows

Related content

Conditional AggregateTableColumn with List.Sum and Table.SelectRows

Conditional Merge

Filter out row using Table.SelectRows and conditional if/and

MERGE QUERIES

Table.SelectRows wildcards