Convert data from minute tu hourly average

[jfarrietaa94]

Hi, I need con convert a minute to minute data to an hourly average, but I don't know how.

This is an example of the data. 

I appreciate any help you could give me. 

[v-easonf-msft]

Hi, @jfarrietaa94 

If your problem has been solved, you could accept the helpful answer as solution.

It will help other community members easily find the solution when they get the similar issue.
If you still need help, please share more information for further research.

Best Regards,
Community Support Team _ Eason

[ValtteriN]

Hi,

You can create a calculated table like this:

Table = summarize(ADDCOLUMNS('Time',"hour",HOUR('Time'[TimeStamp])),[hour],"AVG",AVERAGE('Time'[Chiller1_tin]))

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

[AlexisOlson · Accepted solution]

I think this will result in a table with as many rows as hours. You probably want to include a date column in SUMMARIZE.

@jfarrietaa94 I'd recommend doing this in the query editor so that you reduce your data size before loading to the model.

Create custom columns for Date and Hour and then group by those two columns averaging over the chiller column(s). As an example, try pasting this into the Advanced Editor in a new query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ0lEy1bfQNzIwMlQwtLAyMrIyMFCK1YlWMsKQMoVJGZqCpSyhUmbIulClzGG6YgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Chiller = _t, TimeStamp = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Chiller", Int64.Type}, {"TimeStamp", type datetime}}),
    #"Added Date" = Table.AddColumn(#"Changed Type", "Date", each Date.From([TimeStamp]), type date),
    #"Added Hour" = Table.AddColumn(#"Added Date", "Hour", each Time.Hour([TimeStamp]), Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Hour", {"Date", "Hour"}, {{"Chiller", each List.Average([Chiller]), type nullable number}})
in
    #"Grouped Rows"