Forum Discussion
groups date/hours
Hello to all,
I'm asking for your help concerning a data grouping according to a time base. Indeed, I would like to use Power Query to create data groupings based on a 10 minute time frame. As you can see on the image below, I have about ten values recorded per minute. I would like to be able to group these values on a 10 minutes basis and calculate the average.
Here is an example of the desired result in Excel.
I know the "Groups" function of Power BI but I would like to first reduce my file with Power Query before manipulating it in Power BI.
Thank you in advance for your help,
Have a nice day,
Joël
Hi Anonymous
If I understand correctly, you have too many entries, so need to "compress" rows, here is one way with the assumption you do have the entry for each 10 minute, paste in Advanced Editor
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldI/D4IwEIfhr2I6ixztlX8rg3E1ukiIYdGYOPr9Y5FLwXDl6Eqe0ML7a1v1aF63vnmfPle1V2BSyFMNOttBVmfgHhVadftVpkd2yJZwIAnkCZgfrGogy7w0aE2ExQhrI2weYQvphxmCpQSRYCWdjgWdLv6rSXK9/o+3BMVYtvIvFS6gHfRWiOUseivEcnb6sDFW/4T8cj+ei9CwDZVagZpguYSB/IZibbG4XVKsbVZHWBNhmftyw0aKtQKRIFMqMFfkYgUt04ubNsqx/LSZbwoM20qxZsO2UqzZsK0Uy1l/2yFW9wU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [item = _t, dt = _t, value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"item", type text}, {"dt", type datetime}, {"value", type number}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{ {"item", Order.Ascending},{"dt", Order.Ascending}}), #"Added Custom" = Table.AddColumn(#"Sorted Rows", "group", each Number.From( DateTime.Date([dt]))*100+ Time.Hour([dt])*10+ Number.RoundDown( Time.Minute([dt])/10)), #"Grouped Rows" = Table.Group(#"Added Custom", {"item", "group"}, {{"minDT", each List.Min([dt]), type nullable datetime}, {"avgValue", each List.Average([value]), type nullable number}}), #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"group"}) in #"Removed Columns"Hi Anonymous ,
Just add the corresponding columns when grouping tables:
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Hi Anonymous
If I understand correctly, you have too many entries, so need to "compress" rows, here is one way with the assumption you do have the entry for each 10 minute, paste in Advanced Editor
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldI/D4IwEIfhr2I6ixztlX8rg3E1ukiIYdGYOPr9Y5FLwXDl6Eqe0ML7a1v1aF63vnmfPle1V2BSyFMNOttBVmfgHhVadftVpkd2yJZwIAnkCZgfrGogy7w0aE2ExQhrI2weYQvphxmCpQSRYCWdjgWdLv6rSXK9/o+3BMVYtvIvFS6gHfRWiOUseivEcnb6sDFW/4T8cj+ei9CwDZVagZpguYSB/IZibbG4XVKsbVZHWBNhmftyw0aKtQKRIFMqMFfkYgUt04ubNsqx/LSZbwoM20qxZsO2UqzZsK0Uy1l/2yFW9wU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [item = _t, dt = _t, value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"item", type text}, {"dt", type datetime}, {"value", type number}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{ {"item", Order.Ascending},{"dt", Order.Ascending}}), #"Added Custom" = Table.AddColumn(#"Sorted Rows", "group", each Number.From( DateTime.Date([dt]))*100+ Time.Hour([dt])*10+ Number.RoundDown( Time.Minute([dt])/10)), #"Grouped Rows" = Table.Group(#"Added Custom", {"item", "group"}, {{"minDT", each List.Min([dt]), type nullable datetime}, {"avgValue", each List.Average([value]), type nullable number}}), #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"group"}) in #"Removed Columns"Hello Anonymous and v-yingjl thank you very much for your help!
Do you think it is possible to do the same thing by displaying 3 columns values:
- The average
- The min
- The MaxThanks in advance,
Joël
Hi Anonymous ,
Just add the corresponding columns when grouping tables:
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
To group time each 10 minutes and get the average value of it, you can try this query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZZLbsMwDESvUngdIKY+ltVdeo0g979GU4Q0oEZvHCCrPAxNkRxR9/tyWy5LWtN6tWv7WtP3uj5/f/9ty+MicJbYANsLl11iM63uc5xeOBeJCwQPdZrj7OcGteMMBwsM6qIP5tiaxBlqXj04NNSxQdUCg3rz4JDapjPfdGrND1YlNuiY47rO8a4H2XGBsjjOELy7GoJ37ZIuZ83cY3le1APPhykweMxMZh4YMjdtYHOPpXlRA4OJDjwfB8s68zAwBD9RF515YGhJ0VXTFgwMl2bgNB9FCwvCOGy6YyfYPZagJU1nrh1q4TGo2v6RmlILh4K6629rA6ew4PxeS9rAgf1gP7Bi/WZCXCU2UNsw54RP1L4s3vC4YhE3iQuoR38jzhpDzcPAUNSwf5eYGlp1Q8PfcO6qMx8NjBjKsg3vljfctLrpcQgM/Y4NDEUdNzCqIXj/CNOkdtlvG5/QhCH4mdrkuW18YaN6PmuxYg1SS3JSjwUNmY9PaMJFY5iWfyuWMFgwMMz5saChLFUXVfs7ViwNk3ZoYAquLXjg+b0WOENqu27o+MImTKmFBSG1D/E8tTQ+ggnDwQ78/PbjFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, dt = _t, value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"dt", type datetime}, {"value", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.From([dt]) & Time.From(Number.RoundDown(144*Number.From(Time.From([dt])) / 1 ) / 144)), #"Grouped Rows" = Table.Group(#"Added Custom", {"Type", "Custom"}, {{"avg", each List.Average([value]), type nullable number}}), #"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"Custom", "dt"}}), #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Type", type text}, {"dt", type datetime}, {"avg", type number}}) in #"Changed Type1"Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.