Excel Power Query - How do I append columns inside the same table in power query?

Question

Maybe this is a very simple question, but I'm trying to figure out how to do this, as I have hundreds of columns and the idea of doing it by hand, splitting them into separate queries and then append them doesn't seem to be very practical.

I've been working on a query and it returns me values in the following format:

Date | Time  | Value | Time | Value...

   A   |    B   |    C    |     D  |    E...

But I need to transform it to look like:

Date | Time | Value

   A   |    B   |    C    

   A   |    D  |    E

Thanks for the help!

horseyride horseyride · Accepted Answer · 2020-02-25T12:41:26

Using no custom code:

Load data into powerquery using Data ... From Table/Range...

Right-click Date column, choose unpivot other columns

Add column... index column... use default column name Index

Add column...Custom Column... with formula =Number.Mod([Index],2) and default name Custom

This converts the index column into alternating 0/1s

(Assuming your 2nd column is named Value.1) Add column...Custom Column... with formula =#"Added Custom"{[Index]+1}[Value.1] and default name Custom.1

That will place the value from the row below the current one into current row

Remove alternating row by clicking arrow next to Custom column and removing [x] next the the 1

Click-Select the Attribute, Index and Custom columns, right-click Remove Columns

Load and Close

Assuming your data is loaded as range Table1 you could use this code, pasted into Home...Advanced...

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Date"}, "Attribute", "Value.1"),
#"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 0, 1),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each Number.Mod([Index],2)),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each #"Added Custom"{[Index]+1}[Value.1]),
#"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom] = 0)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute", "Index", "Custom"})
in #"Removed Columns"

If you are willing to use some custom code, this creates two tables, one table with odd columns and one with even columns, unpivots each of them, adds an index to both, then merges them back on that index. Works for any number of columns, might be faster than above for larger data sets.

Assuming your data is loaded as range Table1 you could use this code, pasted into Home...Advanced...

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
OddUnpivot= Table.AddIndexColumn(Table.UnpivotOtherColumns(Table.RemoveColumns(Source,List.RemoveFirstN(List.Alternate(Table.ColumnNames(Source),1,1,1),1)), {"date"}, "Attribute", "Value"), "Index", 0, 1),
EvenUnpivot= Table.AddIndexColumn(Table.UnpivotOtherColumns(Table.RemoveColumns(Source,List.Alternate(Table.ColumnNames(Source),1,1)), {"date"}, "Attribute", "Value"), "Index", 0, 1),
#"Merged Queries" = Table.NestedJoin(OddUnpivot,{"Index"},EvenUnpivot,{"Index"},"Table2",JoinKind.LeftOuter),
#"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Value"}, {"Value.1"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Table",{"Attribute", "Index"})
in #"Removed Columns"

Excel Power Query - How do I append columns inside the same table in power query?

1 Answers