Multiple date/time columns

[cricks]

I'm trying to work with Excel data that is grouped by date/time. There can be multiple groups, each with their own DateTime column. Each group will have a different start time and interval, and a variable number of data fields. Here is an illustration of what the Excel sheets can look like. I want to combine and transpose all the groups of data to be in a single query with the format DateTime - Attribute - Value. I can split up the groups, unpivot, and recombine manually, but if the number of groups or the number of data columns in each group is different, I have to create a custom query.

Is it possible to make a query that is flexible enough to handle different size or number of data groups?

[v-juanli-msft]

Hi @cricks 

In Edit queries, create a new blank query, enter code in Advanced editor, you could also select each step from "applied steps" pane to see details of each step.

let
    Source1 = Table,
    #"Removed Other Columns1" = Table.SelectColumns(Source1,{"DateTime 1", "Data A"}),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Removed Other Columns1", {"DateTime 1"}, "Attribute", "Value"),
    Group1= Table.RenameColumns(#"Unpivoted Columns1",{{"DateTime 1", "DateTime"}}),

    Source2 = Table,
    #"Removed Other Columns2" = Table.SelectColumns(Source2,{"DateTime2", "Data B", "Data C"}),
    #"Unpivoted Columns2" = Table.UnpivotOtherColumns(#"Removed Other Columns2", {"DateTime2"}, "Attribute", "Value"),
    Group2= Table.RenameColumns(#"Unpivoted Columns2",{{"DateTime2", "DateTime"}}),

    Source3 = Table,
    #"Removed Other Columns3" = Table.SelectColumns(Source3,{"DateTime3", "Data E", "Data F", "Data G", "Data H", "Data I"}),
    #"Unpivoted Columns3" = Table.UnpivotOtherColumns(#"Removed Other Columns3", {"DateTime3"}, "Attribute", "Value"),
    Group3= Table.RenameColumns(#"Unpivoted Columns3",{{"DateTime3", "DateTime"}}),

    Source4 = Table,
    #"Removed Other Columns4" = Table.SelectColumns(Source4,{"DateTime4", "Data J"}),
    #"Unpivoted Columns4" = Table.UnpivotOtherColumns(#"Removed Other Columns4", {"DateTime4"}, "Attribute", "Value"),
    Group4= Table.RenameColumns(#"Unpivoted Columns4",{{"DateTime4", "DateTime"}}),

    Source_final= Table.Combine({Group1, Group2, Group3, Group4})
in
    Source_final

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[cricks]

The problem is that the number of groups may vary, and the number of data fields may vary, and the names of the data fields may vary. The only constant is that the first column in each group will always be named "DateTime".

I'm looking for a way to reliably identify and separate the groups of data, even if the number of groups changes, the number of data fields in each group changes, or the names of the data fields change. With @v-juanli-msft's solution, the query will break if any of these are true. 

[Anonymous]

Does this query work for you?

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    NameChanges = List.Accumulate(Table.ColumnNames(Source), {}, (s,c) => s & {if Text.StartsWith(c,"DateTime") then c   else Text.Start(List.Last(s),9) & "." & c}),
    RenameSource = Table.RenameColumns(Source, List.Zip({Table.ColumnNames(Source) , NameChanges})),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(RenameSource, {}, "Attribute", "Value"),
    Transform = Table.TransformColumns(#"Unpivoted Other Columns",{{"Attribute", each Text.AfterDelimiter(_,"."), type text}}),
    AddDateTime = Table.AddColumn(Transform, "DateTime", each if [Attribute]= "" then [Value] else null, type datetime),
    #"Filled Down" = Table.FillDown(AddDateTime,{"DateTime"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Attribute] <> ""))
in
    #"Filtered Rows"

[Anonymous · Accepted solution]

Hi all,

I did it slightly differently:

let
    Source = myTable,
    Columns =  List.Buffer(List.Accumulate(Table.ColumnNames(Source), {}, (s, a)=> if Text.StartsWith(a,"DateTime") then s & {{a}} else List.RemoveLastN(s,1) & {List.Last(s) & {a}} )),
    Output = Table.Combine(List.Accumulate(Columns, {}, (s, a) => s & {Table.UnpivotOtherColumns(Table.RenameColumns(Table.SelectColumns(Source, a), {a{0}, "DateTime"}) , {"DateTime"}, "Attribute", "Value")}))
in
    Output

The code above first split the columns into groups and then unpivot and combine group-by-group.

Column groups list is buffered, but this can be removed the number of columns is quite large.

Kind regards,

John

[cricks]

This works great! Thanks for your help!

[Anonymous]

Hi John,

Your solution is clever - break each section into its own table and unpivot it and then combine it. I like it a lot.

Regards,

Mike

[v-juanli-msft]

Hi @cricks 

It may be possible.

We need to know that:

is the screenshot the original data in excel or expected format after transforming with Power BI?

is there "date" column in your original data? 

are the "date" for each group same?

Best Regards
Maggie

[Mariusz]

Hi @cricks 

Can you provide a workable data sample and expected outcome?  ( You can paste a copy of an excel table in the body of the comment section )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[cricks]

Here's the input format. The start time, interval, and end time for each group of data can be different. 

I've also uploaded the Excel file here. 

DateTime	Data A	DateTime	Data B	Data C	DateTime	Data E	Data F	Data G	Data H	Data I	DateTime	Data J
08/26/19 09:17:39	46.54	08/26/19 09:39:21	51	57.46	8/26/19 0:00:00	7980.5	2133.83	3.07147	59.6037	2544.58	8/24/18 0:00:00	18.58
08/26/19 09:19:39	46.7	08/26/19 09:40:21	51.1	57.26	8/26/19 0:05:00	8308.8	2252.97	3.23939	59.8167	2659.07	8/24/18 0:01:00	22.61
08/26/19 09:21:39	46.74	08/26/19 09:41:21	56.46	57.7	8/26/19 0:10:00	8456.7	2226.57	3.20645	59.8687	2654.93	8/24/18 0:02:00	14.89
08/26/19 09:23:39	47.24	08/26/19 09:42:21	51.06	57.58	8/26/19 0:15:00	8429	2188.73	3.15329	60.129	2597.15	8/24/18 0:03:00	7.16
08/26/19 09:25:39	46.82	08/26/19 09:43:21	49.06	57.76	8/26/19 0:20:00	8317.7	2134.32	3.08085	60.0408	2549.02	8/24/18 0:04:00	10.20
08/26/19 09:27:39	47.14	08/26/19 09:44:21	52.98	57.52	8/26/19 0:25:00	8458.7	2207.58	3.1789	59.8875	2622.75	8/24/18 0:05:00	12.47
08/26/19 09:29:39	46.96	08/26/19 09:45:21	51.66	57.06	8/26/19 0:30:00	8252.4	2130.67	3.07218	59.94	2535.77	8/24/18 0:06:00	14.74

Desired output format:

DateTime	Attribute	Value
08/26/19 09:17:39	Data A	46.54
08/26/19 09:19:39	Data A	46.7
08/26/19 09:21:39	Data A	46.74
08/26/19 09:23:39	Data A	47.24
08/26/19 09:25:39	Data A	46.82
08/26/19 09:27:39	Data A	47.14
08/26/19 09:29:39	Data A	46.96
08/26/19 09:39:21	Data B	51
08/26/19 09:40:21	Data B	51.1
08/26/19 09:41:21	Data B	56.46
08/26/19 09:42:21	Data B	51.06
08/26/19 09:43:21	Data B	49.06
08/26/19 09:44:21	Data B	52.98
08/26/19 09:45:21	Data B	51.66
08/26/19 09:39:21	Data C	57.46
08/26/19 09:40:21	Data C	57.26
08/26/19 09:41:21	Data C	57.7
08/26/19 09:42:21	Data C	57.58
08/26/19 09:43:21	Data C	57.76
08/26/19 09:44:21	Data C	57.52
08/26/19 09:45:21	Data C	57.06
8/26/19 0:00:00	Data E	7980.5
8/26/19 0:05:00	Data E	8308.8
8/26/19 0:10:00	Data E	8456.7
8/26/19 0:15:00	Data E	8429
8/26/19 0:20:00	Data E	8317.7