cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sedki_Jguirim New Member
New Member

RollUp Data per hour

Hello everyone,

 

I'm trying to rollup my data per hour. I searched on the net but the solutions I found doesn't work on my table.

 

Here's the problem:

Capture.PNG

Data is not clear, I want to sum data per hour so that the client can see the elec consumption each hour.

 

Here's my table:

 

Capture.PNG

What I'm trying to do is this: If Date is the same and Hour is the same then SUM rows

 

Could you please help me find a solution for this? I searched on the internet and the codes I found didn't work for me.

 

Thank you,

 

Sedki Jguirim

 

 

PS:

 

Here's an example of my Table, if you want, you can test it on your PowerBI.

 

IndexDate / TimeConsommation electrique Zone 1
106/08/2019 09:315,948522824
206/08/2019 09:326,766433142
306/08/2019 09:336,211663296
406/08/2019 09:345,376119891
506/08/2019 09:355,519907192
606/08/2019 09:365,12529697
706/08/2019 09:376,945520033
806/08/2019 09:386,140920043
906/08/2019 09:396,779599327
1006/08/2019 09:406,432948327
1106/08/2019 09:416,517536355
1206/08/2019 09:425,714900604
1306/08/2019 09:436,505726731
1406/08/2019 09:445,999859712
1506/08/2019 09:455,70772814
1606/08/2019 09:466,281881676
1706/08/2019 09:475,878397585
1806/08/2019 09:485,811343957
1906/08/2019 09:495,13326255
2006/08/2019 09:506,657648965
2106/08/2019 09:525,113794781
2206/08/2019 09:535,933278697
2306/08/2019 09:545,326637876
2406/08/2019 09:556,753087528
2506/08/2019 09:565,425639819
2606/08/2019 09:576,651455556
2706/08/2019 09:586,007931261
2806/08/2019 09:596,124813572
2906/08/2019 10:006,038910238
3006/08/2019 10:015,737940368
3106/08/2019 10:025,054184727
3206/08/2019 10:035,379725418
3306/08/2019 10:046,325855542
3406/08/2019 10:055,267806715
3506/08/2019 10:066,467957888
3606/08/2019 10:075,806715434
3706/08/2019 10:085,856349189
3806/08/2019 10:096,515630914
3906/08/2019 10:105,898059393
4006/08/2019 10:115,148097281
4106/08/2019 10:126,958515673
4206/08/2019 10:136,660789737
4306/08/2019 10:145,095433208
4406/08/2019 10:156,834566745
4506/08/2019 10:166,945210946
4606/08/2019 10:176,310212342
4706/08/2019 10:186,441822736
4806/08/2019 10:196,448927513
4906/08/2019 10:205,619888658
5006/08/2019 10:215,661881314
5106/08/2019 10:225,432615769
5206/08/2019 10:236,532332106
5306/08/2019 10:245,14298253
5406/08/2019 10:255,905884496
5506/08/2019 10:266,011397308
5606/08/2019 10:275,198113861
5706/08/2019 10:285,397255778
5806/08/2019 10:295,08476489
5906/08/2019 10:305,944147191
6006/08/2019 10:316,186872296
6106/08/2019 10:325,186435802
6206/08/2019 10:335,97263229
6306/08/2019 10:346,20642897
6406/08/2019 10:356,015525641
6506/08/2019 10:365,953452802
6606/08/2019 10:375,913060996
6706/08/2019 10:385,576773299
6806/08/2019 10:395,494511461
6906/08/2019 10:406,199051046
7006/08/2019 10:416,27803537
7106/08/2019 10:426,441964937
7206/08/2019 10:436,08190338
7306/08/2019 10:445,873532052
7406/08/2019 10:455,041694054
7506/08/2019 10:465,102056272
7606/08/2019 10:475,000374061
7706/08/2019 10:486,127737048
7806/08/2019 10:496,639695116
7906/08/2019 10:506,710349213
8006/08/2019 10:516,670871042
8106/08/2019 10:525,836667136
8206/08/2019 10:536,012752576
8306/08/2019 10:545,21076754
8406/08/2019 10:555,442539012
8506/08/2019 10:565,111697882
8606/08/2019 10:576,685113699
8706/08/2019 10:585,275187384
8806/08/2019 10:595,667521963
8906/08/2019 11:006,925251914
9006/08/2019 11:015,548028105
9106/08/2019 11:025,048352829
9206/08/2019 11:036,446263091
9306/08/2019 11:045,283872085
9406/08/2019 11:055,315935149
9506/08/2019 11:066,448687387
9606/08/2019 11:075,636994889
9706/08/2019 11:085,869471449
9806/08/2019 11:095,479245709
9906/08/2019 11:105,539064948
 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Anonymous
Not applicable

Re: RollUp Data per hour

The best way to handle dates and times is to create seperate date and time dimensions.  You can transform your date time field via Power Query into seperate date and time fields.

 

2019-08-08_10h01_16.png

 

Once in seperate fields I would create seperate date and time dimensions (tables).  So you have a model that looks like this:

 

2019-08-08_10h01_24.png

 

Once you have modeled the data correctly, you will be able to analyse the data by all aspects of time.  By Hour, Minute, Time Parts eg. every 5 minutes etc...

 

2019-08-08_10h01_37.png

 

I have created a sample workbook you can download here

 

Sample Power BI Workbook

 

You can also download copies of Date and Time dimension Power Query scripts here:

 

Power Query Date Dimension Script

Power Query TIme Dimension Script

 

 

View solution in original post

1 REPLY 1
Highlighted
Anonymous
Not applicable

Re: RollUp Data per hour

The best way to handle dates and times is to create seperate date and time dimensions.  You can transform your date time field via Power Query into seperate date and time fields.

 

2019-08-08_10h01_16.png

 

Once in seperate fields I would create seperate date and time dimensions (tables).  So you have a model that looks like this:

 

2019-08-08_10h01_24.png

 

Once you have modeled the data correctly, you will be able to analyse the data by all aspects of time.  By Hour, Minute, Time Parts eg. every 5 minutes etc...

 

2019-08-08_10h01_37.png

 

I have created a sample workbook you can download here

 

Sample Power BI Workbook

 

You can also download copies of Date and Time dimension Power Query scripts here:

 

Power Query Date Dimension Script

Power Query TIme Dimension Script

 

 

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors