Expanding day of week and time data

[PH573]

Hello,

I am trying to format availability data for apps in a way that would give each day of the week its own row, as opposed to having availability that starts on one day and end in another be in the same column.

In its current form, the data looks like this:

Application	Start day	Start time	End day	End time
Generic	Sun	00:00:00	Sun	19:00:00
Generic	Mon	00:00:00	Fri	23:59:59
Generic	Sat	00:00:00	Sat	19:00:00
Generic2	Sun	00:00:00	Sat	23:59:59

I would like for it to look like this:

Application	Day	Start time	End time
Generic	Sun	00:00:00	19:00:00
Generic	Mon	00:00:00	23:59:59
Generic	Tue	00:00:00	23:59:59
Generic	Wed	00:00:00	23:59:59
Generic	Thu	00:00:00	23:59:59
Generic	Fri	00:00:00	23:59:59
Generic	Sat	00:00:00	19:00:00
Generic2	Sun	00:00:00	23:59:59
Generic2	Mon	00:00:00	23:59:59
Generic2	Tue	00:00:00	23:59:59
Generic2	Wed	00:00:00	23:59:59
Generic2	Thu	00:00:00	23:59:59
Generic2	Fri	00:00:00	23:59:59
Generic2	Sat	00:00:00	23:59:59

How could I achieve this in Power BI? Thank you in advance for any assistance.

EDIT: updated sample data for better clarity on what I am trying to do

[v-yalanwu-msft · Accepted solution]

Hi, @PH573 ;

You could try to create a new table.

New = GENERATE(VALUES('Table'[Application]),{"Mon","Tue","Wed","Thu","Fri","Sat","Sun"}) 

Then create columns:

rank = SWITCH([Start day],"Mon",1,"Tue",2,"Wed",3,"Thu",4,"Fri",5,"Sat",6,"Sun",0)

start time = 
CALCULATE(MIN('Table'[Start Time]),
FILTER('Table',[Application]=EARLIER('New'[Application])&&[Start day]=EARLIER('New'[Start day])))

sarttime2 = 
var _max=CALCULATE(MAX('New'[rank]),FILTER(ALL('New'),[rank]<=EARLIER('New'[rank])&& [Application]=EARLIER('New'[Application])&&[start time]<>BLANK()))
return CALCULATE(MAX('New'[start time]),FILTER(ALL('New'),[rank]=_max&&[Application]=EARLIER('New'[Application])))

End time = CALCULATE(MAX('Table'[End Time]),FILTER('Table',[Application]=EARLIER('New'[Application])&&[Start day]=EARLIER('New'[Start day])))

end time2 = 
var _max=CALCULATE(MAX('New'[rank]),FILTER(ALL('New'),[rank]<=EARLIER('New'[rank])&&[Application]=EARLIER('New'[Application])&&[start time]<>BLANK()))
return CALCULATE(MAX('New'[End time]),FILTER(ALL('New'),[rank]=_max&&[Application]=EARLIER('New'[Application])))

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.

[PH573]

Hello Yalan,

Thank you very much for this! It has helped immensely.

After some new data was added, I ran into an edge case where an app has three availability ranges, shown below. How can I apply this logic for this particular app?

Application	Start day	Start time	End day	End time
Generic3	Mon	12:00:00AM	Fri	6:30:00AM
Generic3	Mon	8:30:00AM	Fri	6:00:00PM
Generic3	Sun	10:00:00PM	Thu	11:59:59PM

The end result should look like this:

Application	Day	Start Time	End Time	Start Time2	End Time2	Start Time3	End Time3
Generic3	Sun	10:00:00PM	11:59:59PM
Generic3	Mon	12:00:00AM	6:30:00AM	8:30:00AM	6:00:00PM	10:00:00PM	11:59:59PM
Generic3	Tue	12:00:00AM	6:30:00AM	8:30:00AM	6:00:00PM	10:00:00PM	11:59:59PM
Generic3	Wed	12:00:00AM	6:30:00AM	8:30:00AM	6:00:00PM	10:00:00PM	11:59:59PM
Generic3	Thu	12:00:00AM	6:30:00AM	8:30:00AM	6:00:00PM	10:00:00PM	11:59:59PM
Generic3	Fri	12:00:00AM	6:30:00AM	8:30:00AM	6:00:00PM
Generic3	Sat

[vanessafvg]

do you have the actual date time or is it provided to you in a day / time of day format?

[PH573]

As helpful as datetime would be, it is provided to me as just day of the week + time.