Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
Check it out now!Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
Hello,
I try to do a table join, but don't work, i have two tables that have a primary key and I need a help to do a table.join.
My table 1:
cod | name | color |
1 | Vanessa | Blue |
2 | Paolo | Gray |
3 | Marcos | Black |
4 | Belinda | White |
5 | Weverton | Yellow |
My table 2:
cod | item |
1 | A |
2 | B |
3 | C |
4 | D |
5 | E |
Whit a table.join I need to merge both table and the result is this:
cod | name | color | item |
1 | Vanessa | Blue | A |
2 | Paolo | Gray | B |
3 | Marcos | Black | C |
4 | Belinda | White | D |
5 | Weverton | Yellow | E |
Someone can help me?
Thanks a lot.
Solved! Go to Solution.
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test. The second table is named as Table2 in below code.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpLzEstLk4EspxySlOVYnWilYyAnIDE/Jx8IO1elFgJFjQGcnwTi5Lzi8FKE5OzwcImIF5qTmZeCsiI8IzMEogZpiBeallqUUl+HpAZmZqTk1+uFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [cod = _t, name = _t, color = _t]),
#"Merged Queries" = Table.NestedJoin(Source, {"cod"}, Table2, {"cod"}, "Table2", JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"item"}, {"item"})
in
#"Expanded Table2"
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test. The second table is named as Table2 in below code.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpLzEstLk4EspxySlOVYnWilYyAnIDE/Jx8IO1elFgJFjQGcnwTi5Lzi8FKE5OzwcImIF5qTmZeCsiI8IzMEogZpiBeallqUUl+HpAZmZqTk1+uFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [cod = _t, name = _t, color = _t]),
#"Merged Queries" = Table.NestedJoin(Source, {"cod"}, Table2, {"cod"}, "Table2", JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"item"}, {"item"})
in
#"Expanded Table2"
Thanks for help, but the Table.NestedJoin spent a lot of time, can you show me how to do this using a Table.Join? Beacause I need the best performance to do this merge.
Thanks again
You can use Table.Join with following (This performs Inner Join by default)
= Table.Join(Source, "cod", Table2, "cod")
But looks like cod fields are sorted, then you can use following for best performance
= Table.Join(Source, "cod", Table2, "cod",JoinKind.Inner,JoinAlgorithm.SortMerge)
If you want to perform LeftOuterJoin, then rename cod to cod1 in Table2 and use following
= Table.Join(Source, "cod", Table2, "cod1",JoinKind.LeftOuter,JoinAlgorithm.SortMerge)
Hello,
I Don't understand the sintaxe of Table.Join.
In this case the right sintaxe is: Table.Join(#"Table1","cod",#"Table2","cod") ?
I do this but doesnt work.
Can you help me ?
Thanks a lot.
If you are doing within Table1, then #"Table1" will have to be replaced with last step of Table1...
If you are doing it outside Table1 i.e. joining as a new query, then above sytax is right.
If still the problem is, please paste the error message.