Forum Discussion
Need help to do a Table.join
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.
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"
6 Replies
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)