Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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 |
@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.
@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
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.
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:
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))
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.
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.
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 |
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 |
@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...
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.
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!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
126 | |
78 | |
77 | |
60 | |
52 |
User | Count |
---|---|
165 | |
86 | |
68 | |
68 | |
58 |