Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have such a data file.
There is a date, there is a start and end time for the session.
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?
Solved! Go to Solution.
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.
2. Create a new list that contains every minute from beginning to end. Then expand it.
3. Then we need to create a hour column and copy the table.
4. And we need to group the two tables to get the min customer.1 and max customer.1.
5. We need to add an index column for each table and merge them.
6. Then we need to expand the table to get the max column and add another customer column.
7. At last, we can remove the irrelevant columns and rename the new columns.
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.
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.
2. Create a new list that contains every minute from beginning to end. Then expand it.
3. Then we need to create a hour column and copy the table.
4. And we need to group the two tables to get the min customer.1 and max customer.1.
5. We need to add an index column for each table and merge them.
6. Then we need to expand the table to get the max column and add another customer column.
7. At last, we can remove the irrelevant columns and rename the new columns.
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.
Wow. It looks quite complex, thanks for the detailed answer.
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.
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
User | Count |
---|---|
125 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |