Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
ghdunn
Helper III
Helper III

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.

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

 

 

View solution in original post

8 REPLIES 8
mahoneypat
Employee
Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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?

 

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"





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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
Not applicable

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

 

 

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

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?

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors