Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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.
Solved! Go to 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
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
To learn more about Power BI, follow me on Twitter or subscribe 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"
To learn more about Power BI, follow me on Twitter or subscribe 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
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.
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
User | Count |
---|---|
24 | |
13 | |
11 | |
10 | |
7 |
User | Count |
---|---|
43 | |
26 | |
21 | |
16 | |
12 |