Conditional Merge or Table.SelectRows

[ghdunn]

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.

[mahoneypat]

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]

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?

[mahoneypat]

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]

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

[Anonymous · Accepted solution]

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]

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]

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?

[ghdunn]

Many thanks  @Anonymous and @mahoneypat 

Just into my week of work now...I will test both suggestions over the next 48 hoursa days and comment

Thanks guys!

ged