Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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_ID | Start Time | End Time |
1 | 15/05/2019 08:30 | 17/05/2019 16:00 |
2 | 15/05/2019 08:30 | 15/05/2019 11:00 |
Meet_ID | Start Time | End Time |
1 | 15/05/2019 08:30 | 16/05/2019 00:00 |
1 | 16/05/2019 00:00 | 17/05/2019 00:00 |
1 | 17/05/2019 00:00 | 17/05/2019 16:00 |
2 | 15/05/2019 08:30 | 15/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?
Solved! Go to Solution.
@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 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?
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).
ID | Room | Start Time | End Time | |
1 | A | 15/05/2019 08:30 | 17/05/2019 16:00 | |
2 | A | 17/05/2019 16:00 | 17/05/2019 17:00 | |
3 | B | 15/05/2019 09:00 | 16/05/2019 11:00 | |
4 | B | 16/05/2019 12:30 | 16/05/2019 15:00 | |
5 | C | 15/05/2019 08:30 | 15/05/2019 16:00 | |
ID | Room | Start Time | End Time | |
1 | A | 15/05/2019 08:30 | 15/05/2019 23:59:59 | |
1 | A | 16/05/2019 00:00 | 16/05/2019 23:59:59 | |
1 | A | 17/05/2019 00:00 | 17/05/2019 16:00 | |
2 | A | 17/05/2019 16:00 | 17/05/2019 17:00 | |
3 | B | 15/05/2019 09:00 | 15/05/2019 23:59:59 | |
3 | B | 16/05/2019 00:00 | 16/05/2019 11:00 | |
4 | B | 16/05/2019 12:30 | 16/05/2019 15:00 | |
5 | C | 15/05/2019 08:30 | 15/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.
The add another Column to extend the rows
Add two new time column
Finally delete the unnessary column and rename what you need
After set the column type, we can get the final version:
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.
@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_ID | Start Time | End Time |
1 | 15/05/2019 08:30 | 16/05/2019 00:00 |
1 | 16/05/2019 00:00 | 17/05/2019 00:00 |
1 | 17/05/2019 00:00 | 17/05/2019 16:00 |
2 | 15/05/2019 08:30 | 15/05/2019 11:00 |
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |