Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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
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
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.
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"
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
This works great! Thanks for your help!
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
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
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 )
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 |