2 ACCEPTED SOLUTIONS
Try this version that fixes overnight issue. I've had to create your buckets as DateTime values to account for the date change overnight, but you can easily change this back to Time type afterwards if you prefer:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc3RDcAgCATQXfg2KRxilVUa91+jWisxIbmPx8HzEMs1BgylRNxcbGU26mkxNgu71JniGcEaXJx55dHOwc0VIyFud7Adv7/2SLTgElzn0b22+d4M/dvsvH6rXIKDrUwWR6XeXw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date_ = _t, start = _t, stop = _t]),
chgTypes = Table.TransformColumnTypes(Source,{{"start", type time}, {"stop", type time}, {"Date_", type date}}),
addHalfHourBins =
Table.AddColumn(
chgTypes,
"halfHourBins",
each let
startDateTime = DateTime.From([Date_] & [start]),
stopDateTime = if [stop] < [start] then DateTime.From(Date.AddDays([Date_], 1) & [stop]) else DateTime.From([Date_] & [stop]),
startHalfHour = DateTime.From(Number.RoundDown(48 * Number.From(startDateTime) / 1 ) / 48),
stopHalfHour = DateTime.From(Number.RoundUp(48 * Number.From(stopDateTime) / 1 ) / 48)
in
List.DateTimes(
startHalfHour,
Duration.TotalMinutes(stopHalfHour - startHalfHour) / 30,
#duration(0,0,30,0)
)
),
expandHalfHourBins = Table.ExpandListColumn(addHalfHourBins, "halfHourBins"),
addHalfHourBinDuration =
Table.AddColumn(
expandHalfHourBins,
"halfHourBinDuration",
each let
startDateTime = DateTime.From([Date_] & [start]),
stopDateTime = if [stop] < [start] then DateTime.From(Date.AddDays([Date_], 1) & [stop]) else DateTime.From([Date_] & [stop])
in
Duration.TotalMinutes(
List.Min({stopDateTime, [halfHourBins] + #duration(0,0,30,0)})
- List.Max({startDateTime, [halfHourBins]})
)
)
in
addHalfHourBinDuration
Pete
Ok, so here's the basics:
addHalfHourBins =
//'let..in' lets us declare variables for later use
startDateTime = DateTime.From([Date_] & [start]) //This creates datetime values from date and time (so we can handle date changes).
stopDateTime = if [stop] < [start] then DateTime.From(Date.AddDays([Date_], 1) & [stop]) else DateTime.From([Date_] & [stop]) //This does the same but adds a day to datetime if stop time is before start time.
startHalfHour = DateTime.From(Number.RoundDown(48 * Number.From(startDateTime) / 1 ) / 48) //This rounds down the datetime value to the nearest half hour to provide the first value in our list of half-hours. It essentially converts the time to a decimal value, rounds down to the nearest half hour (this is the 48 bit - there's 48 half-hours in a day), then converts back to datetime.
stopHalfHour = DateTime.From(Number.RoundUp(48 * Number.From(stopDateTime) / 1 ) / 48) //Same as above, but rounds up so we know where the list needs to end.
List.DateTimes( //This creates a list of datetimes
startHalfHour, //List starting value.
Duration.TotalMinutes(stopHalfHour - startHalfHour) / 30, //How many list items to generate.
#duration(0,0,30,0) //What time increments to use for each list item.
)
Hopefully the rest is fairly self-explanatory, but let me know if there'sanything else you're struggling with.
Also, feel free to give a thumbs-up on any posts that have helped you - that helps me keep doing my thang 🙂
Pete
It sounds like the code has got corrupted somewhere.
If you start from the Source step and click through each step one by one, this should identify exactly which step is causing the error. If it's one of my steps, then you'll need to copy the code out of Advanced Editor for the whole query and paste it into a code window ( </> button ) here. Make sure to anonymise any connection strings etc.
Pete
Hi @BA_Pete - for some reason it wouldnt let me sign or respond from my account so had to recreate a username an register to post from a different device
This is the full code but im still getting that same error
let
Source = Access.Database(File.Contents("S:\CR\Secure\P&S Resource Planning\Hub\Databases\Planning Pack Database\Results_Shrinkage.accdb"), [CreateNavigationProperties=true]),
_tbl_Rolling_WFM_agentResults_PP = Source{[Schema="",Item="tbl_Rolling_WFM_agentResults_PP"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(_tbl_Rolling_WFM_agentResults_PP,{{"Date_", type date}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"TZ", "custID", "acdID", "logonID", "ssn", "modify"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"start", type time}, {"stop", type time}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each [muID] = 2005),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each Date.IsInPreviousNDays([Date_], 3)),
addHalfHourBins =
Table.AddColumn(
#"Filtered Rows1",
"halfHourBins",
each let
startHalfHour = Time.From(Number.RoundDown(48 * Number.From([start]) / 1 ) / 48),
endHalfHour = Time.From(Number.RoundUp(48 * Number.From([stop]) / 1 ) / 48)
in
List.Times(
startHalfHour,
Duration.TotalMinutes(endHalfHour - startHalfHour) / 30,
#duration(0,0,30,0)
)
),
expandHalfHourBins = Table.ExpandListColumn(addHalfHourBins, "halfHourBins"),
addHalfHourBinDuration =
Table.AddColumn(
expandHalfHourBins,
"halfHourBinDuration",
each Duration.TotalMinutes(
List.Min({[stop], [halfHourBins] + #duration(0,0,30,0)})
- List.Max({[start], [halfHourBins]})
)
)
in
addHalfHourBinDuration
No problem.
My initial guess is that you have some start/stop times that go over midnight.
Are you able to share an example of your actual data as at your #"Filtered Rows1" step please?
you can select this query step, copy the whole table (if below 3,000 cells total), paste into 'Enter Data' then copy the M code form that query and paste here.
Pete
@BA_Pete - hi yes ive seen a few agents where he stop time over laps into the next day
so if an agents start was 23:00 and end 00:05 it causes an issue
Im trying to copy limited data but having issues
Try this version that fixes overnight issue. I've had to create your buckets as DateTime values to account for the date change overnight, but you can easily change this back to Time type afterwards if you prefer:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc3RDcAgCATQXfg2KRxilVUa91+jWisxIbmPx8HzEMs1BgylRNxcbGU26mkxNgu71JniGcEaXJx55dHOwc0VIyFud7Adv7/2SLTgElzn0b22+d4M/dvsvH6rXIKDrUwWR6XeXw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date_ = _t, start = _t, stop = _t]),
chgTypes = Table.TransformColumnTypes(Source,{{"start", type time}, {"stop", type time}, {"Date_", type date}}),
addHalfHourBins =
Table.AddColumn(
chgTypes,
"halfHourBins",
each let
startDateTime = DateTime.From([Date_] & [start]),
stopDateTime = if [stop] < [start] then DateTime.From(Date.AddDays([Date_], 1) & [stop]) else DateTime.From([Date_] & [stop]),
startHalfHour = DateTime.From(Number.RoundDown(48 * Number.From(startDateTime) / 1 ) / 48),
stopHalfHour = DateTime.From(Number.RoundUp(48 * Number.From(stopDateTime) / 1 ) / 48)
in
List.DateTimes(
startHalfHour,
Duration.TotalMinutes(stopHalfHour - startHalfHour) / 30,
#duration(0,0,30,0)
)
),
expandHalfHourBins = Table.ExpandListColumn(addHalfHourBins, "halfHourBins"),
addHalfHourBinDuration =
Table.AddColumn(
expandHalfHourBins,
"halfHourBinDuration",
each let
startDateTime = DateTime.From([Date_] & [start]),
stopDateTime = if [stop] < [start] then DateTime.From(Date.AddDays([Date_], 1) & [stop]) else DateTime.From([Date_] & [stop])
in
Duration.TotalMinutes(
List.Min({stopDateTime, [halfHourBins] + #duration(0,0,30,0)})
- List.Max({startDateTime, [halfHourBins]})
)
)
in
addHalfHourBinDuration
Pete
Thank you would this still mean that anytime overlapped will still go in previous days bucket or the 18th?
eg
17/05/2023 22:50 00:05 (so the 5 mins even though overlapped) would count towards 18/05/2023 @ 00:00 or 17/05/2023 @ 00:00 (im assuming the 17th)
If you're changing [halfHourBins] to Time type then grouping on [Date_] and [halfHourBins], then it will essentially get summed up within whatever date [Date_] is.
Alternatively, you could leave [halfHourBins] as DateTime type and just group on [halfHourBins] instead, as this now contains the exact/correct date as well as the half hour slot.
Pete
thank you so much - would be awesome if you can break down how the core bit works just so i understand the logic
Crikey, you don't ask for much, do you! 😂
Which bit(s) in particular are you not sure about?
Pete
Thank you haha
just the main core bits how the functions are working and why divide by 1/48 etc
any commentary on main core bits
i understand it briefly but i get you are generating times then lopping over it?
Ok, so here's the basics:
addHalfHourBins =
//'let..in' lets us declare variables for later use
startDateTime = DateTime.From([Date_] & [start]) //This creates datetime values from date and time (so we can handle date changes).
stopDateTime = if [stop] < [start] then DateTime.From(Date.AddDays([Date_], 1) & [stop]) else DateTime.From([Date_] & [stop]) //This does the same but adds a day to datetime if stop time is before start time.
startHalfHour = DateTime.From(Number.RoundDown(48 * Number.From(startDateTime) / 1 ) / 48) //This rounds down the datetime value to the nearest half hour to provide the first value in our list of half-hours. It essentially converts the time to a decimal value, rounds down to the nearest half hour (this is the 48 bit - there's 48 half-hours in a day), then converts back to datetime.
stopHalfHour = DateTime.From(Number.RoundUp(48 * Number.From(stopDateTime) / 1 ) / 48) //Same as above, but rounds up so we know where the list needs to end.
List.DateTimes( //This creates a list of datetimes
startHalfHour, //List starting value.
Duration.TotalMinutes(stopHalfHour - startHalfHour) / 30, //How many list items to generate.
#duration(0,0,30,0) //What time increments to use for each list item.
)
Hopefully the rest is fairly self-explanatory, but let me know if there'sanything else you're struggling with.
Also, feel free to give a thumbs-up on any posts that have helped you - that helps me keep doing my thang 🙂
Pete
Hi @Mahmed1 ,
Try this example query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrC0MjRV0gHRJqZKsTrRSoYGVoYWQBFDQysTI4iImZWBAUjEDK7G0srYCChiZGhlaq4UGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [startTime = _t, endTime = _t]),
chgTypes = Table.TransformColumnTypes(Source,{{"startTime", type time}, {"endTime", type time}}),
// Relevant steps from here ----->
addHalfHourBins =
Table.AddColumn(
chgTypes,
"halfHourBins",
each let startHalfHour = Time.From(Number.RoundDown(48 * Number.From([startTime]) / 1 ) / 48) in
List.Times(
startHalfHour,
Duration.TotalMinutes(Time.EndOfHour([endTime]) - startHalfHour) / 30,
#duration(0,0,30,0)
)
),
expandHalfHourBins = Table.ExpandListColumn(addHalfHourBins, "halfHourBins"),
addHalfHourBinDuration =
Table.AddColumn(
expandHalfHourBins,
"halfHourBinDuration",
each Duration.TotalMinutes(
List.Min({[endTime], [halfHourBins] + #duration(0,0,30,0)})
- List.Max({[startTime], [halfHourBins]})
)
)
in
addHalfHourBinDuration
For this output:
Pete
Hi thank you
It nearly works but ive come across an issue
for eg - in this example - For finance 08:00 - 09:00 - i should get 30 mins from 08:00 to 08:30 and 30 mins from 08:30 - 09:00. I get that however i am getting a minus figure at 09:30
OutPut
DateAreaActivitystartTimeendTimeLengthhalfHourBinshalfHourBinDuration
| 18/05/2023 | Finance | Open | 08:00:00 | 09:00:00 | 60 | 08:00:00 | 30 |
| 18/05/2023 | Finance | Open | 08:00:00 | 09:00:00 | 60 | 08:30:00 | 30 |
| 18/05/2023 | Finance | Open | 08:00:00 | 09:00:00 | 60 | 09:00:00 | 0 |
| 18/05/2023 | Finance | Open | 08:00:00 | 09:00:00 | 60 | 09:30:00 | -30 |
| 18/05/2023 | IT | Open | 09:00:00 | 09:45:00 | 45 | 09:00:00 | 30 |
| 18/05/2023 | IT | Open | 09:00:00 | 09:45:00 | 45 | 09:30:00 | 15 |
Input - DateAreaActivitystartTimeendTimeLength
| 18/05/2023 | Finance | Open | 08:00 | 09:00 | 60 |
| 18/05/2023 | IT | Open | 09:00 | 09:45 | 45 |
Thank you - you are amazing
ive not tried this yet but cant wait to give this a go
If i had additional columns
Date
Area
ActivityType
how can i group so it buckets it like you did it but for the date, area and activity?
Well spotted, sorry about that.
Try this version that fixes the 'same half hour' issue:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PcuxDcAwCETRXagpgBjHsApi/zUMdpTqpMcnAsicFbB3KCQGMDmvEmYfcmU6Ucv8G/NHSoRd3yP1f5pasSura/wumRs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [startTime = _t, endTime = _t]),
chgTypes = Table.TransformColumnTypes(Source,{{"startTime", type time}, {"endTime", type time}}),
addHalfHourBins =
Table.AddColumn(
chgTypes,
"halfHourBins",
each let
startHalfHour = Time.From(Number.RoundDown(48 * Number.From([startTime]) / 1 ) / 48),
endHalfHour = Time.From(Number.RoundUp(48 * Number.From([endTime]) / 1 ) / 48)
in
List.Times(
startHalfHour,
Duration.TotalMinutes(endHalfHour - startHalfHour) / 30,
#duration(0,0,30,0)
)
),
expandHalfHourBins = Table.ExpandListColumn(addHalfHourBins, "halfHourBins"),
addHalfHourBinDuration =
Table.AddColumn(
expandHalfHourBins,
"halfHourBinDuration",
each Duration.TotalMinutes(
List.Min({[endTime], [halfHourBins] + #duration(0,0,30,0)})
- List.Max({[startTime], [halfHourBins]})
)
)
in
addHalfHourBinDuration
Pete
Thank you - seems to be ok but having issues with grouping
=Table.Group(addHalfHourBinDuration, {"Date_","muID","exception","halfHourBins"},{{"Total",each List.Sum([halfHourBinDuration}), type number}})
Getting this error - Expression.error - The increment argument is out of range 0.00:30:00
The code looks fine, apart from this bit:
This curly bracket '}' should be square bracket ']'
Pete
