Forum Discussion
Merge tables without a join, placing new column data side by side?
How do you efficiently append a column from one table to another without a join on common fields?
I have two tables with an arbitrary number of columns, all uniquely named, all with data, and all with the same number of rows. I'd like 'tack' a list of columns from the second table onto a list of columns from the first. The background is that I sometimes separate calculations on the same source into different queries for various reasons, but then I need to manipulate the original data and the output from the calculations.
I originally created the calcuation tables with IDs and did joins. This isn't bad, but the steps to do the merge slow the combined query down. My approach now is to output all the queries to tables side-by-side in a worksheet, then surround them with a Named Range I then use as an input for a query connection. This is problematic because the range does not always reliably expand with the query outputs.
Table 1:
| Col1 | Col2 | Col3 |
Arbitrarydata | Arbitrarydata | Arbitrarydata |
| Arbitrarydata | Arbitrarydata | Arbitrarydata |
Table 2:
| Col3 | Col4 | Col5 |
Arbitrarydata | Arbitrarydata | Arbitrarydata |
| Arbitrarydata | Arbitrarydata | Arbitrarydata |
Desired output (for a sample list of Col1 and Col2 from the first table and Col5 from the second):
| Col1 | Col2 | Col5 |
Arbitrarydata | Arbitrarydata | Arbitrarydata |
| Arbitrarydata | Arbitrarydata | Arbitrarydata |
No joins, no matching, just putting the data side by side in the same table in the original order. I did see one simple solution that converted the tables to lists and combined the lists, though this proved significantly slower than a join for large data sets.
Is there an equivalent to this pseudocode?
List 1 = ("Col1","Col2")
List 2 = "(Col5")
Combine(Table1(List1), Table2(List2))
Hi Anonymous ,
You could add an index column in Table 1 at first. Then refer to the following code:
Table.AddColumn(#"Added Index", "Column Name", each #"Table 2"[Col5]{[Index]})Here is the file for your reference.
4 Replies
Hi Anonymous ,
You could add an index column in Table 1 at first. Then refer to the following code:
Table.AddColumn(#"Added Index", "Column Name", each #"Table 2"[Col5]{[Index]})Here is the file for your reference.
Thanks, that's a great start. I modified it to use a list name for a column:
let
ColNames = Table.ColumnNames(Source),
AddIndex = Table.AddIndexColumn(Source, "Index", 0, 1),
MergeCol = Table.AddColumn(AddIndex, ColNames{1}, each Table.Column(Source,ColNames{1}){[Index]})
in
MergeColThat could, in turn, be combined with some loop logic to iterate through the list. It's significantly slower than a join, though. My intuitive understanding of what "should" be fast or slow in Power Query does not always seem to align with reality.
Although late but I found a simple solution by:
demoting the headers of the two tables >> transpose the two tables >> appeand them >> transpose
I used simple filter to select the needed columns before the last transpose
// Table1 let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Demoted Headers" = Table.DemoteHeaders(Source), #"Transposed Table" = Table.Transpose(#"Demoted Headers") in #"Transposed Table" // Table2 let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content], #"Demoted Headers" = Table.DemoteHeaders(Source), #"Transposed Table" = Table.Transpose(#"Demoted Headers") in #"Transposed Table" // Table3 let Source = Table.Combine({Table1, Table2}), #"Filtered Rows" = Table.SelectRows(Source, each ([Column1] <> "Col3" and [Column1] <> "Col4")), #"Transposed Table" = Table.Transpose(#"Filtered Rows"), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]) in #"Promoted Headers"