Need help to do a Table.join

[Anonymous]

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.

[Anonymous]

@Vijay_A_Verma 

Thanks a lot!

[Vijay_A_Verma · Accepted 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"

[Anonymous]

@Vijay_A_Verma 

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

[Vijay_A_Verma]

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) 

[Anonymous]

@Vijay_A_Verma 

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.

[Vijay_A_Verma]

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.