Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Adding an hour to the hierarchy, indicating the amount of time spent

I have such a data file.
There is a date, there is a start and end time for the session.

2020-09-18 11_26_13-Lion.xlsx - Excel.png
I want to add an hour to the hierarchy with a date and display data about how much time was spent in that hour.
I can take an hour from the start time, but judging by the first line, it turns out that 08/26/20 at 11 was spent an hour and a half, and 08/26/20 at 12 is already 0.
How to correctly bind this data to some model with date / time or is there some way to get instead of the line 26/08/20 11.35 13.04
lines like
08/26/20 11.35 11.59
08/26/20 12.00 12.59
08/26/20 13.00 13.04?

1 ACCEPTED SOLUTION
v-zhenbw-msft
Community Support
Community Support

Hi @Anonymous ,

 

We can do some transformations in Power Query Editor to meet your requirement.

 

1. Add a custom column to get the duration minutes between Start and Finish.

 

add1.jpg

 

2. Create a new list that contains every minute from beginning to end. Then expand it.

 

add2.jpg

 

add3.jpg

 

3. Then we need to create a hour column and copy the table.

 

add4.jpg

 

add5.jpg

 

4. And we need to group the two tables to get the min customer.1 and max customer.1.

 

add6.jpg

 

add7.jpg

 

5. We need to add an index column for each table and merge them.

 

add8.jpg

 

6. Then we need to expand the table to get the max column and add another customer column.

 

add9.jpg

 

7. At last, we can remove the irrelevant columns and rename the new columns.

 

add10.jpg

 

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

BTW, pbix as attached.

View solution in original post

3 REPLIES 3
v-zhenbw-msft
Community Support
Community Support

Hi @Anonymous ,

 

We can do some transformations in Power Query Editor to meet your requirement.

 

1. Add a custom column to get the duration minutes between Start and Finish.

 

add1.jpg

 

2. Create a new list that contains every minute from beginning to end. Then expand it.

 

add2.jpg

 

add3.jpg

 

3. Then we need to create a hour column and copy the table.

 

add4.jpg

 

add5.jpg

 

4. And we need to group the two tables to get the min customer.1 and max customer.1.

 

add6.jpg

 

add7.jpg

 

5. We need to add an index column for each table and merge them.

 

add8.jpg

 

6. Then we need to expand the table to get the max column and add another customer column.

 

add9.jpg

 

7. At last, we can remove the irrelevant columns and rename the new columns.

 

add10.jpg

 

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

BTW, pbix as attached.

Anonymous
Not applicable

Wow. It looks quite complex, thanks for the detailed answer.

2020-09-22 11_02_21-Book1 - Excel.png

I want to make a heatmap that has the week number in the rows and the hours in the columns. Like in the picture. I tried to find visualizations that accept date / time values, but I couldn't find it. I came to the conclusion that you need to create your own hierarchy with time. So I asked for advice.

Anonymous
Not applicable

I don't understand how to attach a file, so here's an example in text.

 

Date Duration Start time Finish time
26/08/20 1h 28m 11:35 13:04
26/08/20 1h 13m 15:28 16:41
26/08/20 49m 13:22 14:12
26/08/20 30m 22:16 22:47
26/08/20 25m 9:09 9:34
26/08/20 13m 10:55 11:09
25/08/20 47m 11:05 11:52
25/08/20 15m 22:26 22:42
23/08/20 1h 22m 21:43 23:06
23/08/20 48m 20:37 21:26
23/08/20 26m 19:12 19:38
23/08/20 17m 17:03 17:20
21/08/20 59m 20:10 21:09
21/08/20 50m 22:29 23:19
20/08/20 48m 21:30 22:18
18/08/20 1h 49m 11:08 12:57
17/08/20 15m 20:41 20:57
16/08/20 37m 21:03 21:40
16/08/20 26m 22:15 22:42

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.