Forum Discussion

jfarrietaa94's avatar
jfarrietaa94
New Member
4 years ago
Solved

Convert data from minute tu hourly average

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. 
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    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"
ValtteriN's avatar
ValtteriN
Icon for Community Champion rankCommunity Champion
4 years ago

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's avatar
AlexisOlson
Icon for Super User rankSuper User
4 years ago

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"