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

Accepted Solutions
Anonymous
Not applicable

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

@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
Super User I
Super User I

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

@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?

Anonymous
Not applicable

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

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 
Super User I
Super User I

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

@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
Community Support
Community Support

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

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

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

@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

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors