Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi All
i have a massive data set where i have
Date
Area
Code
start time 09:15
end time 09:45
length (diff between end time - start time) 30mins
what i need to do is look at the length so 30 mins in this example and group by half hour intervals
so based on the above example 15 mins of the length was between 09:00 to 09:30 and other 15 mins was between 09:30 and 10:00
i need to do that for each row and group into half hour intervals for that date, area and code
i hope im making sense but this is a massive challenge for me
essentially i need a revised view that splits the length by 30 mins intervals from 00:00-00:30,01:00....23:30
Solved! Go to Solution.
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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
Hi @BA_Pete
thanl you
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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
@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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
Thank you
youve been so helpful
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
Proud to be a Datanaut!
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?
I think the error or minus figure occurs when the bins doesnt go into next 30 mins
eg if start was 09:15 to 09:29
i get
09:00 = 14
09:30 = -1
If i do 08:45 to 09:00
i get
08:30 = 15
09:00 = 0
09:30 = -30
So needs a slight tweak somewhere 🙂
Again thank you so so much
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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
71 | |
66 | |
24 | |
18 | |
13 |