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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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

 

Anonymous
Not applicable

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

Anonymous
Not applicable

@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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Kudoed Authors