Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Mahmed1
Helper IV
Helper IV

Power Query Grouping challenge

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

 

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:

BA_Pete_0-1684500816366.png

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

20 REPLIES 20
Mahmed1
Helper IV
Helper IV

Hi @BA_Pete 

 

thanl you

 

  • i still get an issue at some stage with same error even if i take grouping out increment argument out if range error

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

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:

BA_Pete_0-1684500816366.png

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Thank you

youve been so helpful 

BA_Pete
Super User
Super User

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:

BA_Pete_0-1684404296127.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




@BA_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/2023FinanceOpen08:00:0009:00:006008:00:0030
18/05/2023FinanceOpen08:00:0009:00:006008:30:0030
18/05/2023FinanceOpen08:00:0009:00:006009:00:000
18/05/2023FinanceOpen08:00:0009:00:006009:30:00-30
18/05/2023ITOpen09:00:0009:45:004509:00:0030
18/05/2023ITOpen09:00:0009:45:004509:30:0015

 

Input - DateAreaActivitystartTimeendTimeLength

18/05/2023FinanceOpen08:0009:0060
18/05/2023ITOpen09:0009:4545

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?

@BA_Pete 

 

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:

BA_Pete_0-1684480480635.png

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

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:

 

BA_Pete_0-1684484924646.png

This curly bracket '}' should be square bracket ']'

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors