How to calculate daily average based on hourly data in M?

[IEPMost]

Hi guys,

hope you can help out, I didn´t find anything on this for Power Query...

seems pretty simple:

I have two columns [datetime] and [temperature] - for every hour on every day there is a temperature. Now I need to get the average of the temperature for a day. In other words: I want to reduce my rows so there is only one value for date.

One example:

Before:

10.01.2014	8,8
10.01.2014 01:00	6,2
10.01.2014 02:00	5
10.01.2014 03:00	4,4
10.01.2014 04:00	3
10.01.2014 05:00	2,2
10.01.2014 06:00	1,6
10.01.2014 07:00	1,6
10.01.2014 08:00	2
10.01.2014 09:00	2,2
10.01.2014 10:00	3,1
10.01.2014 11:00	3,9
10.01.2014 12:00	5,2
10.01.2014 13:00	6,3
10.01.2014 14:00	5,5
10.01.2014 15:00	5,2
10.01.2014 16:00	4,8
10.01.2014 17:00	3,8
10.01.2014 18:00	2,4
10.01.2014 19:00	1,6
10.01.2014 20:00	1,1
10.01.2014 21:00	0,6
10.01.2014 22:00	0,2
10.01.2014 23:00	0,1

After:

10.01. 2014

3,3336

Any help is much appreciated!!

Cheers:)

[Jimmy801 · Accepted solution]

Hello @IEPMost 

first transform datetime to date and then apply a grouping on your date, averaging the degrees-column. Here an example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddFbCsUgDATQrRS/Q8nEaLVbKd3/Nm5BAxdGfz3k4eR5EvRUnKbwJKlJS6/8Px6KW/WjKkZkgwpBHuDiRD4oE5QBtphTB0Eq0bWnNhsS9O0k6NxOQISgThQ5LBrmSI8+DI8qyg9l37BGtHQpXLEhU2TBB0HfJmgaRGnYTEMXVRZEy1sO+hq+Pw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DateTime = _t, Degrees = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DateTime", type datetime}, {"Degrees", type number}}, "de-DE"),
    #"Extracted Date" = Table.TransformColumns(#"Changed Type",{{"DateTime", DateTime.Date, type date}}),
    #"Grouped Rows" = Table.Group(#"Extracted Date", {"DateTime"}, {{"Average", each List.Average([Degrees]), type number}})
in
    #"Grouped Rows"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy