Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I am stuck with a problem where i have to sum times per different categories. My Data looks roughly like this:
What i need is to sum the hours by "Date" and "User" and between the "outward run" and "Return run" in the type-column, to get the Data like this:
To achive this i think i need a column somewhat like that:
With that i could group the Data by "Date", "User" and "Helper". Yet i do not know how to get that Helper-column.
Can anyone help me?
Thanks in advance.
Regards,
Dennis
Solved! Go to Solution.
Hi @Anonymous
Please check the applied steps in query editor to get the helper column.Attached the file for your reference.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtI30TcyMLRU0lFyBGL/0pLyxKIUhaLSPCDPAohBMoZKsToYSsPzi7Iz89IVSjJzU2HqDIGEETbFQaklpUV5UGPBygyNiDQYotAEl8moLgarMzTDpRjdaJBCsKQxYUeDRI0QHjRGqHUiEHBoSvEHHH7F6EFHQDVYoTGxqo1hQWKMTTVqaIBDzhxiciwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, User = _t, Type = _t, Start = _t, End = _t, Hour = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"User", type text}, {"Type", type text}, {"Start", Int64.Type}, {"End", Int64.Type}, {"Hour", Int64.Type}}), Partition = Table.Group( #"Changed Type", {"Type"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}), #"Expanded Partition1" = Table.ExpandTableColumn(Partition, "Partition", {"Date", "User", "Start", "End", "Hour", "Index"}, {"Date", "User", "Start", "End", "Hour", "Index"}), #"Sorted Rows" = Table.Sort(#"Expanded Partition1",{{"Date", Order.Ascending}, {"Start", Order.Ascending}}), #"Added Conditional Column" = Table.AddColumn(#"Sorted Rows", "Outward_Index", each if [Type] = "Outward run" then [Index] else null), #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column", "Return_Index", each if [Type] = "Return run" then [Index] else null), #"Filled Down" = Table.FillDown(#"Added Conditional Column2",{"Outward_Index"}), #"Filled Up" = Table.FillUp(#"Filled Down",{"Return_Index"}), #"Added Conditional Column1" = Table.AddColumn(#"Filled Up", "Custom", each if [Outward_Index] <> [Return_Index] then [Outward_Index]+[Return_Index] else [Outward_Index]), #"Renamed Columns" = Table.RenameColumns(#"Added Conditional Column1",{{"Custom", "Helper"}}) in #"Renamed Columns"
Regards,
Cherie
Hi @Anonymous
Please check the applied steps in query editor to get the helper column.Attached the file for your reference.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtI30TcyMLRU0lFyBGL/0pLyxKIUhaLSPCDPAohBMoZKsToYSsPzi7Iz89IVSjJzU2HqDIGEETbFQaklpUV5UGPBygyNiDQYotAEl8moLgarMzTDpRjdaJBCsKQxYUeDRI0QHjRGqHUiEHBoSvEHHH7F6EFHQDVYoTGxqo1hQWKMTTVqaIBDzhxiciwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, User = _t, Type = _t, Start = _t, End = _t, Hour = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"User", type text}, {"Type", type text}, {"Start", Int64.Type}, {"End", Int64.Type}, {"Hour", Int64.Type}}), Partition = Table.Group( #"Changed Type", {"Type"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}), #"Expanded Partition1" = Table.ExpandTableColumn(Partition, "Partition", {"Date", "User", "Start", "End", "Hour", "Index"}, {"Date", "User", "Start", "End", "Hour", "Index"}), #"Sorted Rows" = Table.Sort(#"Expanded Partition1",{{"Date", Order.Ascending}, {"Start", Order.Ascending}}), #"Added Conditional Column" = Table.AddColumn(#"Sorted Rows", "Outward_Index", each if [Type] = "Outward run" then [Index] else null), #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column", "Return_Index", each if [Type] = "Return run" then [Index] else null), #"Filled Down" = Table.FillDown(#"Added Conditional Column2",{"Outward_Index"}), #"Filled Up" = Table.FillUp(#"Filled Down",{"Return_Index"}), #"Added Conditional Column1" = Table.AddColumn(#"Filled Up", "Custom", each if [Outward_Index] <> [Return_Index] then [Outward_Index]+[Return_Index] else [Outward_Index]), #"Renamed Columns" = Table.RenameColumns(#"Added Conditional Column1",{{"Custom", "Helper"}}) in #"Renamed Columns"
Regards,
Cherie
Hi @v-cherch-msft ,
Thanks alot for your help. That is exactly what i was looking for 🙂
Regards,
Dennis
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |