cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

3 days activity split into 3 rows with amended start & end times

Hi

 

I have room reservations spanning multiple days and I need to be able to calculate the duration of bookings for each of the three days.

 

I believe the best way to do this is to split the reservation into separate rows, where I amend the start and end time according to the start and end of a day - see table below

Meet_IDStart TimeEnd Time
115/05/2019 08:3017/05/2019 16:00
215/05/2019 08:3015/05/2019 11:00
   
Meet_IDStart TimeEnd Time
115/05/2019 08:3016/05/2019 00:00
116/05/2019 00:0017/05/2019 00:00
117/05/2019 00:0017/05/2019 16:00
215/05/2019 08:3015/05/2019 11:00

 

So far, I can create the multiple rows needed by using a power query Text.Repeat("A",[Duration_Days]) and then split by Character for a calculated number of days (Duration_Days). This is fine to create separate rows but I don't get the appropriate time changes.

 

Any ideas?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@v-lid-msft  - Thank you very much, that has helped enormously - I appreciate the effort

 

I made a small adjustment to the End Date field as initially it was the same as the Start Date. I used the same (Duration)-(Index) calculation you used for the start date to calculate the end date and calculated the endtime value separately before combining.

 

let
Source = #"Fusion Meetings",
#"Removed Columns" = Table.RemoveColumns(Source,{"bunch of columns"}),
#"Added Custom4" = Table.AddColumn(#"Removed Columns", "StartDate", each Date.From([#"StartTime+1"])),
#"Added Custom" = Table.AddColumn(#"Added Custom4", "Custom", each Duration.TotalDays(Date.StartOfDay([#"EndTime+1"])-Date.StartOfDay([#"StartTime+1"]))),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.Generate(()=>[Custom], each _ >= 0, each _ - 1)),
 #"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom1", "Custom.1"),
#"Added Custom2" = Table.AddColumn(#"Expanded Custom.1", "Start", each if [Custom.1]=[Custom] then [#"StartTime+1"] else Date.AddDays(Date.StartOfDay([#"StartTime+1"]),[Custom]-[Custom.1])),
#"Added Custom8" = Table.AddColumn(#"Added Custom2", "EndDate_Calc", each Date.AddDays(Date.StartOfDay([#"StartDate"]),[Custom]-[Custom.1]+1)),
#"Added Custom9" = Table.AddColumn(#"Added Custom8", "End", each if [Custom.1]=0 then [#"EndTime+1"] else #datetime(Date.Year([EndDate_Calc]),Date.Month([EndDate_Calc]),Date.Day([EndDate_Calc]),0,0,0)),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom9",{"StartTime+1","EndTime+1", "Custom.1","StartDate","EndDate_Calc"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Start", "Start Time"}, {"End", "End Time"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Start Time", type datetime}, {"End Time", type datetime}})
in
#"Changed Type1"

View solution in original post

5 REPLIES 5
smpa01
Super User
Super User

@Anonymous  can you please tell how do you want the code to determine the start and end time for each of the days for each of the line items?

 

For example,

MEETID 1 starts on

15/05/2019 08:30

and ends on

17/05/2019 16:00

 

How do I know when it ends on 15th, 16th and starts on 16th and 17th?

 

 What is the assumption here?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


Anonymous
Not applicable

Thank you

 

The assumption is that the room will be reserved for the whole period. So part of Day 1, 24 hours of Day2 and part of Day 3.

I have created a longer example which I hope provides a better view of the query (I have also changed end day time to 23:59:59).

 

IDRoomStart TimeEnd Time 
1A15/05/2019 08:3017/05/2019 16:00 
2A17/05/2019 16:0017/05/2019 17:00 
3B15/05/2019 09:0016/05/2019 11:00 
4B16/05/2019 12:3016/05/2019 15:00 
5C15/05/2019 08:3015/05/2019 16:00 
     
IDRoomStart TimeEnd Time 
1A15/05/2019 08:3015/05/2019 23:59:59 
1A16/05/2019 00:0016/05/2019 23:59:59 
1A17/05/2019 00:0017/05/2019 16:00 
2A17/05/2019 16:0017/05/2019 17:00 
3B15/05/2019 09:0015/05/2019 23:59:59 
3B16/05/2019 00:0016/05/2019 11:00 
4B16/05/2019 12:3016/05/2019 15:00 
5C15/05/2019 08:3015/05/2019 16:00 

Hi @Anonymous ,

 

We can archieve your requirement in the Power Query Editor, Here is the full M Query

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY4xDsAgCEWvQpg1AS1a3doeg3j/a5SFpDJ0YHnvhXxVZEx42RXikUkyC5yz0pd04DaJcCXFEnOXO+reVxP3/n6EvAGz50fMTZYwx5B4Lyae//Xi69cL", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Room = _t, #"Start Time" = _t, #"End Time" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Room", type text}, {"Start Time", type datetime}, {"End Time", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Duration.TotalDays(Date.StartOfDay([End Time])-Date.StartOfDay([Start Time]))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.Generate(()=>[Custom], each _ >= 0, each _ - 1)),
    #"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom1", "Custom.1"),
    #"Added Custom2" = Table.AddColumn(#"Expanded Custom.1", "Start", each if [Custom.1]=[Custom] then [Start Time] else  Date.AddDays(Date.StartOfDay([Start Time]),[Custom]-[Custom.1])),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "End", each if [Custom.1]=0 then [End Time] else  #datetime(Date.Year([Start]),Date.Month([Start]),Date.Day([Start]),23,59,0)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Start Time", "End Time", "Custom", "Custom.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Start", "Start Time"}, {"End", "End Time"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Start Time", type datetime}, {"End Time", type datetime}})
in
    #"Changed Type1"

 

 

1. add a Column to count the days, 0 means do not need to split.

11.PNG11.2.PNG

The add another Column to extend the rows

11.4.PNG

12.PNG

 

Add two new time column

12.3.PNG12.6.PNG

 

Finally delete the unnessary column and rename what you need

 

13.PNG14.PNG

 

After set the column type, we can get the final version:

15.PNG

 


BTW, pbix as attached.

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

@v-lid-msft  - Thank you very much, that has helped enormously - I appreciate the effort

 

I made a small adjustment to the End Date field as initially it was the same as the Start Date. I used the same (Duration)-(Index) calculation you used for the start date to calculate the end date and calculated the endtime value separately before combining.

 

let
Source = #"Fusion Meetings",
#"Removed Columns" = Table.RemoveColumns(Source,{"bunch of columns"}),
#"Added Custom4" = Table.AddColumn(#"Removed Columns", "StartDate", each Date.From([#"StartTime+1"])),
#"Added Custom" = Table.AddColumn(#"Added Custom4", "Custom", each Duration.TotalDays(Date.StartOfDay([#"EndTime+1"])-Date.StartOfDay([#"StartTime+1"]))),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.Generate(()=>[Custom], each _ >= 0, each _ - 1)),
 #"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom1", "Custom.1"),
#"Added Custom2" = Table.AddColumn(#"Expanded Custom.1", "Start", each if [Custom.1]=[Custom] then [#"StartTime+1"] else Date.AddDays(Date.StartOfDay([#"StartTime+1"]),[Custom]-[Custom.1])),
#"Added Custom8" = Table.AddColumn(#"Added Custom2", "EndDate_Calc", each Date.AddDays(Date.StartOfDay([#"StartDate"]),[Custom]-[Custom.1]+1)),
#"Added Custom9" = Table.AddColumn(#"Added Custom8", "End", each if [Custom.1]=0 then [#"EndTime+1"] else #datetime(Date.Year([EndDate_Calc]),Date.Month([EndDate_Calc]),Date.Day([EndDate_Calc]),0,0,0)),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom9",{"StartTime+1","EndTime+1", "Custom.1","StartDate","EndDate_Calc"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Start", "Start Time"}, {"End", "End Time"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Start Time", type datetime}, {"End Time", type datetime}})
in
#"Changed Type1"

@Anonymous  if you can come up to this on your own yo can just create a conditional column for End TIme if the time portion is "00:00" to change it to 23:59:59

 

Meet_IDStart TimeEnd Time
115/05/2019 08:3016/05/2019 00:00
116/05/2019 00:0017/05/2019 00:00
117/05/2019 00:0017/05/2019 16:00
215/05/2019 08:3015/05/2019 11:00




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors