Start End Date to 15 min Time Intervals

[PedervdB]

Hi I have a table looking like this (coming from Outlook Calender Data):

StartTime	EndTime	Room
22-01-2019 09:30	22-01-2019 11:00	Boardroom
23-01-2019 11:00	23-01-2019 12:00	Boardroom
28-01-2019 14:00	28-01-2019 15:15	Boardroom

I'm looking to change it through PowerQuery (M) into a table that looks like this:

Start15MinInterval	Room
22-01-2019 09:30	Boardroom
22-01-2019 09:45	Boardroom
22-01-2019 10:00	Boardroom
22-01-2019 10:15	Boardroom
22-01-2019 10:30	Boardroom
22-01-2019 10:45	Boardroom
23-01-2019 11:00	Boardroom
23-01-2019 11:15	Boardroom
23-01-2019 11:30	Boardroom
23-01-2019 11:45	Boardroom
28-01-2019 14:00	Boardroom
28-01-2019 14:15	Boardroom
28-01-2019 14:30	Boardroom
28-01-2019 14:45	Boardroom
28-01-2019 15:00	Boardroom

The new table therefore has the start time of the 15 minute intervals that, in this case, the "Boardroom" is occupied.

[Anonymous]

I have the exact same request as you. Were you able to accomplish this? Make my life so much easier @PedervdB 

[PedervdB]

Nope. Not yet.

[Anonymous]

@PedervdB This is how I did it for every minute:

In the Power Query editor add a custom column

- List.Times([Start Time], Duration.Minutes([End Time] - [Start Time])+1,#duration(0,0,1,0))

Then you expand it out hopfully that helps!

[v-frfei-msft]

Hi @PedervdB,

We can create a Calendar with time intervals  by DAX. There is a case about that, please refer to it for more details, Hope that helps.

https://community.powerbi.com/t5/Desktop/Calendar-with-time-intervals/m-p/475348

Regards,

Frank

[PedervdB]

Hi Frank, thanks for taking the time. What you’re referring to I already have (although done in Powerquery). This question is actually about manipulating the data I pull from the Outlook Rooms Calendar. This’ll make me able to join the output from the calenders with such a “DateTime” table you are referring to. Next thing is that I can put the calendars in a Matrix. I’m not so happy with the current calendar visuals. By doing this I basically create the Room Calenders in a Martrix visual.