Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 Pa...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    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"

     

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

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's avatar
Anonymous
Not applicable
4 years ago

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's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    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.