Combine two tables by Column

[Anonymous]

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 Quote	Product Quote	Quantity Quote
Apple	PC	1
Samsung	Phone	5

Customer Sales	Sales Product	Quantity Sales
IBM	PC	3
Facebook	Phone	1

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 Quote	Customer Product	Customer Quantity	Customer Sales	Sales Product	Quantity Sales
Apple	PC	1	IBM	PC	3
Samsung	Phone	5	Facebook	Phone	1

Thank you!

[FrankAT · Accepted solution]

Hi @Anonymous ,

use Power Query like this:

// 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]

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.

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