Forum Discussion
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 Quote | Product Quote | Quantity Quote |
| Apple | PC | 1 |
| Samsung | Phone | 5 |
| Customer Sales | Sales Product | Quantity Sales |
| IBM | PC | 3 |
| 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 | Phone | 1 |
Thank you!
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)
2 Replies
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
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)
