How do I sum four values (quarter hours) to get a running hourly sum?

[samba_axe]

Hello all!

I am new to Power BI and I am finding that the simplest things in Excel are a bear in PBI.

I have 15 minute data from a flow/heat meter that I need to sum every hour to give me the hourly kiloWatt hours.

In Excel I would simply =sum(x1,x2,x3,x4) then skip four spaces and do that again, all the way down the column.

How can I do this in Power BI and keep it all referenced to the same date and the proper hour? Below is an example from Excel and in it I am summing (15:15,15:30,15:45,16:00) and displaying the data in the same row as 16:00 under kWh. Any suggestings please?

date	time	kW 15 min	T in	T out	flow	kWh
15/07/2015	15:15:00	0.75	79.68	71.11	3.6
15/07/2015	15:30:00	0.85	79.96	71.72	3.75
15/07/2015	15:45:00	0.95	80.24	72.33	3.9
15/07/2015	16:00:00	0.97	80.62	72.83	3.91	3.52
15/07/2015	16:15:00	0.97	80.87	72.61	3.9
15/07/2015	16:30:00	0.96	80.85	72.61	3.76
15/07/2015	16:45:00	0.95	80.83	72.61	3.62
15/07/2015	17:00:00	265.84	78.51	71.56	32.51	268.72
15/07/2015	17:15:00	232.2	78.17	71.22	31.75
15/07/2015	17:30:00	233.29	78.3	70.5	32.31
15/07/2015	17:45:00	233.29	78.28	70.5	30.36
15/07/2015	18:00:00	237.63	78.69	70.78	29.93	936.41
15/07/2015	18:15:00	239.8	78.74	70.5	30.17
15/07/2015	18:30:00	237.63	78.87	70.83	29.8
15/07/2015	18:45:00	240.89	78.68	70.61	30.7
15/07/2015	19:00:00	243.06	78.71	70.89	29.57	961.38
15/07/2015	19:15:00	246.31	78.72	70.5	30.36
15/07/2015	19:30:00	244.14	78.43	70.06	30.69
15/07/2015	19:45:00	246.31	78.47	70.45	30.19
15/07/2015	20:00:00	246.31	78.28	70.28	30.24	983.07
15/07/2015	20:15:00	235.46	78.69	70.89	28.96
15/07/2015	20:30:00	233.29	79.13	71.45	29.12

[ankitpatira]

@samba_axe Here's what you can do. I created sample data like your scenario. In power bi desktop go to query editor, select your time column, under transform tab under Date & Time Column select Time dropdown and click Hour. This will extract hour out of your date time. Then finally group by that hour by going to Group By.

[Sean]

@ankitpatira I think he says he wants to add 15:15+15:30+15:45+16:00 (implying these are end times)

So maybe he needs to first add a custom start time column (end time - 15min) and then Group By

[ankitpatira]

@Sean Nice one. Thanks for pointing out.

[samba_axe]

Maybe...but I need to make sure that the 15 minute selections are kept in chronoligical order and bound to their proper hour, day, month and year.

[arify]

Here's an example code:

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type text}, {"time", type time}, {"kW 15 min", type number}}),
 #"Extracted Hour" = Table.TransformColumns(#"Changed Type",{{"time", Time.Hour}}),
 #"Changed Type1" = Table.TransformColumnTypes(#"Extracted Hour",{{"time", Int64.Type}}),
 #"Grouped Rows" = Table.Group(#"Changed Type1", {"date", "time"}, {{"kWh", each List.Sum([kW 15 min]), type number}})
in
 #"Grouped Rows"

I assumed the first 3 columns are the inputs, and they're coming from an Excel table, you can alter it.

Essentially there are 2 steps:

• Extracting hour from the time column
  • 
• Group by date&time columns with Sum aggregate
  • 
  • 

I hope this helps

(One catch, this will show the sum of 15:00, 15:15, 15:30, 15:45 unlike your example 😕 otherwise if you want it to show exactly what you wanted, this would be a little bit more complicated (needs to subtract 1 second before you extracted the hour))

[samba_axe]

Thanks for that but where do I enter that code? I assume the code must be entered before I group the cells?

I assume that because i tried to group them and the resulting column gave me the same numbers I had before.

It looks like we are on the right track and I can see what Sean means about subtracting on to end on the hour.

Sorry but I am a totaly noob on Power BI. I just got on yesterday. I need to set it up for a real time display for energy meter data and I need to convert our 15 minute readings into kilowatt hours to do so. Easy as pie in Excel but this is a new game for me.

[Anonymous]

hi sean! can you help me out i actually need to do the exact same thing but on excel with a dataset of a year.

[samba_axe]

Hello and thank you for helping. Yes, I need to sum the 4 parts of an hour and it is not actually the hour I am summing, I want to sum the power data that is taken every 15 minutes and I want to sum those to give a value for that hour. The end time should always be on the hour, not at 15, 30 or 45, only on :00.

Just like below

date	time	kW 15 min	T in	T out	flow	kWh
15/07/2015	15:15:00	0.75	79.68	71.11	3.6
15/07/2015	15:30:00	0.85	79.96	71.72	3.75
15/07/2015	15:45:00	0.95	80.24	72.33	3.9
15/07/2015	16:00:00	0.97	80.62	72.83	3.91	3.52

[Sean]

That's exactly what I meant. Before you apply the steps outlined by Ankit you need to change the hour in the 4th value you want to sum. The 16 needs to be 15 so you can Group By correctly.
And what I was suggesting was a custom start column that would deduct 15 min from each time.
This way every 4th value you will be grouping will match the previous 3 and you'll get the right kW sum.

[samba_axe]

In the end it should look like this one i did on Excel

date	time	kW 15 min	T in	T out	flow	kWh
15/07/2015	15:15:00	0.75	79.68	71.11	3.6
15/07/2015	15:30:00	0.85	79.96	71.72	3.75
15/07/2015	15:45:00	0.95	80.24	72.33	3.9
15/07/2015	16:00:00	0.97	80.62	72.83	3.91	3.52
15/07/2015	16:15:00	0.97	80.87	72.61	3.9
15/07/2015	16:30:00	0.96	80.85	72.61	3.76
15/07/2015	16:45:00	0.95	80.83	72.61	3.62
15/07/2015	17:00:00	265.84	78.51	71.56	32.51	268.72
15/07/2015	17:15:00	232.2	78.17	71.22	31.75
15/07/2015	17:30:00	233.29	78.3	70.5	32.31
15/07/2015	17:45:00	233.29	78.28	70.5	30.36
15/07/2015	18:00:00	237.63	78.69	70.78	29.93	936.41
15/07/2015	18:15:00	239.8	78.74	70.5	30.17
15/07/2015	18:30:00	237.63	78.87	70.83	29.8
15/07/2015	18:45:00	240.89	78.68	70.61	30.7
15/07/2015	19:00:00	243.06	78.71	70.89	29.57	961.38
15/07/2015	19:15:00	246.31	78.72	70.5	30.36
15/07/2015	19:30:00	244.14	78.43	70.06	30.69
15/07/2015	19:45:00	246.31	78.47	70.45	30.19
15/07/2015	20:00:00	246.31	78.28	70.28	30.24	983.07
15/07/2015	20:15:00	235.46	78.69	70.89	28.96
15/07/2015	20:30:00	233.29	79.13	71.45	29.12
15/07/2015	20:45:00	235.46	79.96	71.22	28.77
15/07/2015	21:00:00	236.55	79.74	71.95	29.4	940.76

[Sean]

@samba_axe how do you ultimately plan visualize this in PBI?

You don't really need to do Group By in the Query Editor.

After you import your data just create a Calculated Column

Calculated Column  = HOUR('Table'[time]-(0.25/24))

Then create a Matrix like this...

[samba_axe]

Oh sorry,

To visualize it will be likely graphed along with the other meters on campus relative to the full load. In other words, as a portion of the whole to show the relative energy/flow of each building on campus.

[samba_axe]

Ah ok..that makes some sense to me. Thanks for that. I will give it a try soon and see how it goes. I have a bit on today so it may take a while. I thank you all for your help. Cross fingers!