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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors