Excel Forum

How to append columns inside of a same query in PowerQuery?

Felipe Tani

Barcelona

New Member

Members

Forum Posts: 1

Member Since:
February 25, 2020

Offline

February 25, 2020 - 7:12 pm

Hi,

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!

Catalin Bombea

Iasi, Romania

Admin

Forum Posts: 1810

Member Since:
November 8, 2013

Offline

February 25, 2020 - 8:08 pm

Hi Felipe,

Assuming that you have the data in a table named Table1, you can use the following query:

Date	Time	Value	Time2	Value2
25/02/2020	12:34:00	234	16:25:00 PM	3478

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
Unpivot = Table.UnpivotOtherColumns(#"Changed Type", {"Date"}, "Attribute", "Value.1"),
#"Added Index" = Table.AddIndexColumn(Unpivot, "Index", 0, 1),
#"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
#"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index"}, #"Added Index1", {"Index.1"}, "Added Index1", JoinKind.LeftOuter),
#"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Value.1"}, {"Added Index1.Value.1"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Added Index1", each not Text.StartsWith([Attribute], "Time")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute", "Index", "Index.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Added Index1.Value.1", "Time"}, {"Value.1", "Value"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Date", "Time", "Value"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Time", type time}})
in
#"Changed Type1"

Stages used:

Select Date column and Unpivot other columns

Add 2 index columns with a different starting number

Merge the query with itself with Index as key in first table and Index.1 in second table

Expand the Value column, this will bring the values in same line as time

Filter the Attribute column to exclude anything that starts with Time. (we have those values now in a new column)