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