Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello
I have a table that shows the operating hours of systems by the days of the week. But not all the days of the week are listed, only a range. There are about 1000 distinct systems, with varying schedules.
System ID | From | Start Time | To | End Time |
abcd | Mon | 7:30am | Fri | 11:59pm |
abcd | Tue | 12:00am | Sat | 4:30am |
abcd | Sat | 8:30am | N/A | 11:59pm |
abcd | Sun | 12:00am | N/A | 3:00am |
abcd | Sun | 8:00am | N/A | 11:59pm |
efgh | Sun | 12:00am | Sat | 11:59pm |
ijkl | Mon | 8:00am | Fri | 8:00pm |
ijkl | Sat | 8:00am | Sun | 5:00am |
And I would like to turn it into this. 7 days of the week for each system. If there are multiple time spans for a given day, then they will be in multiple columns, on the same row.
System ID | Day | Start Time 1 | End Time 1 | Start Time 2 | End Time 2 | Start Time 3 | End Time 3 | Duration Minutes |
abcd | Mon | 7:30am | 11:59pm | N/A | N/A | N/A | N/A | 990 |
abcd | Tue | 12:00am | 4:30am | 7:30am | 11:59pm | N/A | N/A | 1260 |
abcd | Wed | 12:00am | 4:30am | 7:30am | 11:59pm | N/A | N/A | 1260 |
abcd | Thu | 12:00am | 4:30am | 7:30am | 11:59pm | N/A | N/A | 1260 |
abcd | Fri | 12:00am | 4:30am | 7:30am | 11:59pm | N/A | N/A | 1260 |
abcd | Sat | 8:30am | 11:59pm | N/A | N/A | N/A | N/A | 930 |
abcd | Sun | 12:00am | 3:00am | 8:00am | 11:59pm | N/A | N/A | 960 |
efgh | Mon | 12:00am | 11:59pm | N/A | N/A | N/A | N/A | 1440 |
efgh | Tue | 12:00am | 11:59pm | N/A | N/A | N/A | N/A | 1440 |
efgh | Wed | 12:00am | 11:59pm | N/A | N/A | N/A | N/A | 1440 |
efgh | Thu | 12:00am | 11:59pm | N/A | N/A | N/A | N/A | 1440 |
efgh | Fri | 12:00am | 11:59pm | N/A | N/A | N/A | N/A | 1440 |
efgh | Sat | 12:00am | 11:59pm | N/A | N/A | N/A | N/A | 1440 |
efgh | Sun | 12:00am | 11:59pm | N/A | N/A | N/A | N/A | 1440 |
etc. | etc. | etc. | etc. | etc. | etc. | etc. | etc. | etc. |
Any help is much appreciated.
Thank you
Hi, @moon_walker ;
You could create a new table by dax ,then create a column.
Table 2 = GENERATE(VALUES('Table'[System ID]),{"Mon","Tue","Wed","Thu","Fri","Sat","Sun"})
create columns:
rank = SWITCH([From],"Mon",1,"Tue",2,"Wed",3,"Thu",4,"Fri",5,"Sat",6,"Sun",0)
start time =
CALCULATE(MIN('Table'[Start Time]),FILTER('Table',[System ID]=EARLIER('Table 2'[System ID])&&[From]=EARLIER('Table 2'[From])))
sarttime2 =
var _max=CALCULATE(MAX('Table 2'[rank]),FILTER(ALL('Table 2'),[rank]<=EARLIER('Table 2'[rank])&& [System ID]=EARLIER('Table 2'[System ID])&&[start time]<>BLANK()))
return CALCULATE(MAX('Table 2'[start time]),FILTER(ALL('Table 2'),[rank]=_max&&[System ID]=EARLIER('Table 2'[System ID])))
End time = CALCULATE(MAX('Table'[End Time]),FILTER('Table',[System ID]=EARLIER('Table 2'[System ID])))
end time2 =
var _max=CALCULATE(MAX('Table 2'[rank]),FILTER(ALL('Table 2'),[rank]<=EARLIER('Table 2'[rank])&& [System ID]=EARLIER('Table 2'[System ID])&&[start time]<>BLANK()))
return CALCULATE(MAX('Table 2'[End time]),FILTER(ALL('Table 2'),[rank]=_max&&[System ID]=EARLIER('Table 2'[System ID])))
The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello @v-yalanwu-msft , thank you for this attempt.
In your solution, it gives system 'abcd' a start and end time as 12:00am-4:30am. But if we see my example, system 'abcd' is online from 12:00-4:30am as well as 7:30am-11:59pm. This is for Tuesday, Wednesday, Thursday, and Friday.
That's why the desired solution, the ranges are split up into multiple start and end columns.
Is there a way where both these time ranges can be shown in the final table?
From my example, see Tue-Fri
System ID | Day | Start Time 1 | End Time 1 | Start Time 2 | End Time 2 | Start Time 3 | End Time 3 | Duration Minutes |
abcd | Mon | 7:30am | 11:59pm | N/A | N/A | N/A | N/A | 990 |
abcd | Tue | 12:00am | 4:30am | 7:30am | 11:59pm | N/A | N/A | 1260 |
abcd | Wed | 12:00am | 4:30am | 7:30am | 11:59pm | N/A | N/A | 1260 |
abcd | Thu | 12:00am | 4:30am | 7:30am | 11:59pm | N/A | N/A | 1260 |
abcd | Fri | 12:00am | 4:30am | 7:30am | 11:59pm | N/A | N/A | 1260 |
abcd | Sat | 8:30am | 11:59pm | N/A | N/A | N/A | N/A | 930 |
abcd | Sun | 12:00am | 3:00am | 8:00am | 11:59pm | N/A | N/A | 960 |
Thank you
Much appreciated
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |