Merge tables without a join, placing new column data side by side?

[alexdi]

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))

[Anonymous]

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"

[v-eachen-msft · Accepted solution]

Hi @alexdi ,

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.

[alexdi]

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
MergeCol

That 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.

[alexdi]

Edit: Spoke too soon, not a good solution.