Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
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)
1 Guest(s)