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

Combine two tables by Column

Hi everyone,

 

I have two tables with a different amount of rows. I am wondering if it is possible to combine them together without interfering with the data in either? Below are examples of the two tables I have in my PBI file.

Customer QuoteProduct QuoteQuantity Quote
ApplePC1
SamsungPhone5

 

Customer SalesSales ProductQuantity Sales
IBMPC3
FacebookPhone1

 

This table is the expected result of what I would like the combination of both tables to be. The row orders do not matter I just wish for them to be combined like below.

 

Customer QuoteCustomer ProductCustomer QuantityCustomer SalesSales ProductQuantity Sales
ApplePC1IBMPC3
SamsungPhone5FacebookPhone1

 

Thank you!

1 ACCEPTED SOLUTION
FrankAT
Community Champion
Community Champion

Hi @Anonymous ,

use Power Query like this:

 

14-07-_2021_23-48-08.png

 

// Table1
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoyElV0lEKcAYShkqxOtFKwYm5xaV56SDBjPw8kKSpUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer Quote" = _t, #"Product Quote" = _t, #"Quantity Quote" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer Quote", type text}, {"Product Quote", type text}, {"Quantity Quote", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

// Table2
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nTyVdJRCnAGEsZKsTrRSm6JyalJ+fnZINGM/LxUIG2oFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer Sales" = _t, #"Sales Product" = _t, #"Quantity Sales" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer Sales", type text}, {"Sales Product", type text}, {"Quantity Sales", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

// MergedTables
let
    Source = Table.NestedJoin(Table1, {"Index"}, Table2, {"Index"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Customer Sales", "Sales Product", "Quantity Sales"}, {"Customer Sales", "Sales Product", "Quantity Sales"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table2",{"Index"})
in
    #"Removed Columns"

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

View solution in original post

2 REPLIES 2
FrankAT
Community Champion
Community Champion

Hi @Anonymous ,

use Power Query like this:

 

14-07-_2021_23-48-08.png

 

// Table1
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoyElV0lEKcAYShkqxOtFKwYm5xaV56SDBjPw8kKSpUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer Quote" = _t, #"Product Quote" = _t, #"Quantity Quote" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer Quote", type text}, {"Product Quote", type text}, {"Quantity Quote", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

// Table2
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nTyVdJRCnAGEsZKsTrRSm6JyalJ+fnZINGM/LxUIG2oFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer Sales" = _t, #"Sales Product" = _t, #"Quantity Sales" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer Sales", type text}, {"Sales Product", type text}, {"Quantity Sales", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

// MergedTables
let
    Source = Table.NestedJoin(Table1, {"Index"}, Table2, {"Index"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Customer Sales", "Sales Product", "Quantity Sales"}, {"Customer Sales", "Sales Product", "Quantity Sales"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table2",{"Index"})
in
    #"Removed Columns"

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

dudeyates
Frequent Visitor

I'm sure there's multiple way to reach your goal. What I often do is open Power Query and use it's merge functionality.

 

In Power Query Home tab, click the Merge Queries drop down arrow and select Merge Queries as New. It will create a new merged table while leaving your 2 original tables as they were.

dudeyates_1-1626297128413.png

You'll have the option to select which tables to merge and which column you want to merge by. 

This Microsoft Documentation Merge queries overview should help 

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.