Populating days of the week in a table

[moon_walker]

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

[v-yalanwu-msft]

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.

[moon_walker]

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