RollUp Data per hour

[Sedki_Jguirim]

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:

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:

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.

Index	Date / Time	Consommation electrique Zone 1
1	06/08/2019 09:31	5,948522824
2	06/08/2019 09:32	6,766433142
3	06/08/2019 09:33	6,211663296
4	06/08/2019 09:34	5,376119891
5	06/08/2019 09:35	5,519907192
6	06/08/2019 09:36	5,12529697
7	06/08/2019 09:37	6,945520033
8	06/08/2019 09:38	6,140920043
9	06/08/2019 09:39	6,779599327
10	06/08/2019 09:40	6,432948327
11	06/08/2019 09:41	6,517536355
12	06/08/2019 09:42	5,714900604
13	06/08/2019 09:43	6,505726731
14	06/08/2019 09:44	5,999859712
15	06/08/2019 09:45	5,70772814
16	06/08/2019 09:46	6,281881676
17	06/08/2019 09:47	5,878397585
18	06/08/2019 09:48	5,811343957
19	06/08/2019 09:49	5,13326255
20	06/08/2019 09:50	6,657648965
21	06/08/2019 09:52	5,113794781
22	06/08/2019 09:53	5,933278697
23	06/08/2019 09:54	5,326637876
24	06/08/2019 09:55	6,753087528
25	06/08/2019 09:56	5,425639819
26	06/08/2019 09:57	6,651455556
27	06/08/2019 09:58	6,007931261
28	06/08/2019 09:59	6,124813572
29	06/08/2019 10:00	6,038910238
30	06/08/2019 10:01	5,737940368
31	06/08/2019 10:02	5,054184727
32	06/08/2019 10:03	5,379725418
33	06/08/2019 10:04	6,325855542
34	06/08/2019 10:05	5,267806715
35	06/08/2019 10:06	6,467957888
36	06/08/2019 10:07	5,806715434
37	06/08/2019 10:08	5,856349189
38	06/08/2019 10:09	6,515630914
39	06/08/2019 10:10	5,898059393
40	06/08/2019 10:11	5,148097281
41	06/08/2019 10:12	6,958515673
42	06/08/2019 10:13	6,660789737
43	06/08/2019 10:14	5,095433208
44	06/08/2019 10:15	6,834566745
45	06/08/2019 10:16	6,945210946
46	06/08/2019 10:17	6,310212342
47	06/08/2019 10:18	6,441822736
48	06/08/2019 10:19	6,448927513
49	06/08/2019 10:20	5,619888658
50	06/08/2019 10:21	5,661881314
51	06/08/2019 10:22	5,432615769
52	06/08/2019 10:23	6,532332106
53	06/08/2019 10:24	5,14298253
54	06/08/2019 10:25	5,905884496
55	06/08/2019 10:26	6,011397308
56	06/08/2019 10:27	5,198113861
57	06/08/2019 10:28	5,397255778
58	06/08/2019 10:29	5,08476489
59	06/08/2019 10:30	5,944147191
60	06/08/2019 10:31	6,186872296
61	06/08/2019 10:32	5,186435802
62	06/08/2019 10:33	5,97263229
63	06/08/2019 10:34	6,20642897
64	06/08/2019 10:35	6,015525641
65	06/08/2019 10:36	5,953452802
66	06/08/2019 10:37	5,913060996
67	06/08/2019 10:38	5,576773299
68	06/08/2019 10:39	5,494511461
69	06/08/2019 10:40	6,199051046
70	06/08/2019 10:41	6,27803537
71	06/08/2019 10:42	6,441964937
72	06/08/2019 10:43	6,08190338
73	06/08/2019 10:44	5,873532052
74	06/08/2019 10:45	5,041694054
75	06/08/2019 10:46	5,102056272
76	06/08/2019 10:47	5,000374061
77	06/08/2019 10:48	6,127737048
78	06/08/2019 10:49	6,639695116
79	06/08/2019 10:50	6,710349213
80	06/08/2019 10:51	6,670871042
81	06/08/2019 10:52	5,836667136
82	06/08/2019 10:53	6,012752576
83	06/08/2019 10:54	5,21076754
84	06/08/2019 10:55	5,442539012
85	06/08/2019 10:56	5,111697882
86	06/08/2019 10:57	6,685113699
87	06/08/2019 10:58	5,275187384
88	06/08/2019 10:59	5,667521963
89	06/08/2019 11:00	6,925251914
90	06/08/2019 11:01	5,548028105
91	06/08/2019 11:02	5,048352829
92	06/08/2019 11:03	6,446263091
93	06/08/2019 11:04	5,283872085
94	06/08/2019 11:05	5,315935149
95	06/08/2019 11:06	6,448687387
96	06/08/2019 11:07	5,636994889
97	06/08/2019 11:08	5,869471449
98	06/08/2019 11:09	5,479245709
99	06/08/2019 11:10	5,539064948

[Anonymous · Accepted solution]

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.

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

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...

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