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