cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Alirezam Regular Visitor
Regular Visitor

aggregation of rows

Hi dudes,

 

I have tons of data in half-hour format and want to aggregate them to hourly format adding two half-hour data together for each hour. The excel sample is attached. Any idea how to do it in Power bi or even excel?

what I want to see is the "Sum" column which is sum of kWh,

Regards,

 

https://1drv.ms/x/s!Al3r7IU8GY3yxT2t81XOpq0xdmcj?e=lTUTAP

 

Capture.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: aggregation of rows

Once you have the correct time on each row you just need a measure 

Sum = SUM ( table[kWh] )

then you pull the Year, Month, Day and time from the table and add in the measure.

kwh.jpg

3 REPLIES 3
Super User
Super User

Re: aggregation of rows

ello @Alirezam 

In PowerQuery, when you pull the information in, if you select the time column you can transform it to "Start of hour". The do another time transform to go to time only.

startofhour.jpg

startofhourdone.jpg

Alirezam Regular Visitor
Regular Visitor

Re: aggregation of rows

Yes, but this was not my answer, may you please read again what I had asked? Thanks anyway

Super User
Super User

Re: aggregation of rows

Once you have the correct time on each row you just need a measure 

Sum = SUM ( table[kWh] )

then you pull the Year, Month, Day and time from the table and add in the measure.

kwh.jpg

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 267 members 2,838 guests
Please welcome our newest community members: