Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vinicius_ramos
Helper III
Helper III

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:

codnamecolor
1VanessaBlue
2PaoloGray
3MarcosBlack
4BelindaWhite
5WevertonYellow

 

My table 2: 

 

coditem
1A
2B
3C
4D
5

E

 

Whit a table.join I need to merge both table and the result is this:

 

codnamecoloritem
1VanessaBlueA
2PaoloGrayB
3MarcosBlackC
4BelindaWhiteD
5WevertonYellowE

 

Someone can help me?

 

Thanks a lot.

 

 

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

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"

 

View solution in original post

6 REPLIES 6
vinicius_ramos
Helper III
Helper III

@Vijay_A_Verma 

 

Thanks a lot!

Vijay_A_Verma
Super User
Super User

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 

 

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) 

@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.

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors