cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
callcenterH3lp
Regular Visitor

Time Interval Grouping

Good day. I'm trying to group some call center data by 30 minute intervals. My challenge is, the start time can vary and impact multiple intervals. For instance, the start time could be 8:32, and last for 2 hours. I would want it to show 28 minutes for the first 30 minute interval, then 30 minutes the subsequent intervals until the end time. 

 

The data would look something like this:

 

DateEmployee#StartTimeDurationAgentState
1/5/22205568:326071Inbound
1/5/222055610:143600Unavailable
1/5/222055611:147024Inbound
1/5/22207248:309924Inbound
1/5/222072411:153600Lunch
1/5/222072412:159943

Inbound

 

 

I have accomplished this in Excel. One days worth of data is exhausting for excel to handle, and i'd like to see if Power Bi could handle a bit more data the same way. Here's the calculation i used in excel. 

 

callcenterH3lp_0-1653798548670.png

 

Any suggestions or reccomendations would be appreciated. 

2 ACCEPTED SOLUTIONS
TomMartens
Super User
Super User

Hey @callcenterH3lp ,

 

here you will find a pbix that contains a solution.
As your question is more complex so is the solution :-). Please be aware that the final column does not show minutes but instead seconds, of course, you can convert this into minutes by dividing the value by 60. I consider it more simple to have seconds instead of minutes.


The solution is based on Power Query and uses an anonymous function - if you are not familiar with advanced Power Query stuff, I recommend this amazing blog: Power Query M Primer (part 1): Introduction, Simple Expressions & let | Ben Gribaudo

 

First I created a table "Intervals" that contains all possible intervals of a single day, the following screenshot shows an excerpt of this table:
image.png

 

Than I created some columns

"TheMostCurrentStartInterval"
This Column holds the StartInterval that matches the StartTime, if the StartTime eq 8:32AM the StartInterval becomes 8:30. This is the maximum value of [IntervalStart] that is less or equal to [StartTime].
This step requires an inline function. This is necessary to use the values from the current row (the value of the field [StartTime]) to find the value in an "external" table, the intervals table.

 

"SecondsInFirstInterval"
This column holds the amount of seconds that have to be allocated to the first interval.

 

"NumberOfIntervals"
This column holds the number of intervals that will be spanned by the duration (including incomplete intervals).

"ListOfIntervals"

This column contains a simple list of integers starting from 1 to NumberOfIntervals. This column will expanded.

 

"IntervalAllocatedTo"

This column holds the interval the number will be allocated to, and is also used to create a relationship between the intervals table and the table from your sample data.
image.png

 

"SecondsInAllocatedInterval"

This column holds the seconds that are allocated to the interval.

The data type of this column is changed to decimal.

 

The following screenshot shows an example using a matrix visual, using

the column "IntervalStart" from the table Intervals as column header and

the column  "Employee#" as row header.

The column "SecondsInAllocatedInterval" is used as value:

image.png

 

Hopefully, this provides what you are looking for and helps to tackle your challenge.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

Hey @callcenterH3lp ,

 

I formatted the M code a little different for the first custom column that contains the syntax for the anonymous function.

Make sure that there is no each in this line
, ( row ) =>

 

 

Table.AddColumn(
            // the table
            #"Changed Type"
            // the new column name
            , "TheMostCurrentStartInterval"
            //row is just a name, the current row will be passed to an anonymous function
            , ( row ) =>
            let

 

 

 

Make sure that there is an each in your code
, each [IntervalStart] <= row[StartTime]

 

 

let
                // storing the value of column inside a variable, 
                // basically this is not necessary as column value can be accessed directly see a little below
                //theStartTime = row[StartTime],
                mostCurrentIntervalStart = 
                    Table.Sort( 
                        Table.SelectRows( 
                            #"Intervals"
                            // each is important as allow to reference the inner row, in this example
                            // the filter will appliead to each row to filter the remote table 
                            // referencing the variable from above
                            //, each  [IntervalStart] <= theStartTime
                            // referencing the the value of the "outer" row  
                            , each  [IntervalStart] <= row[StartTime] 
                        )
                        , {{"IntervalStart", Order.Descending}}
                    // the below syntax is referencing the column IntervalStart [] from the first row {},
                    // zero-based     
                    )[IntervalStart]{0}
            // the value of the step mostCurrentIntervalStart will be returned from the anonymous function
            in mostCurrentIntervalStart

 

 

 

Hopefully, this helps to get where you want.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

6 REPLIES 6
TomMartens
Super User
Super User

Hey @callcenterH3lp ,

 

here you will find a pbix that contains a solution.
As your question is more complex so is the solution :-). Please be aware that the final column does not show minutes but instead seconds, of course, you can convert this into minutes by dividing the value by 60. I consider it more simple to have seconds instead of minutes.


The solution is based on Power Query and uses an anonymous function - if you are not familiar with advanced Power Query stuff, I recommend this amazing blog: Power Query M Primer (part 1): Introduction, Simple Expressions & let | Ben Gribaudo

 

First I created a table "Intervals" that contains all possible intervals of a single day, the following screenshot shows an excerpt of this table:
image.png

 

Than I created some columns

"TheMostCurrentStartInterval"
This Column holds the StartInterval that matches the StartTime, if the StartTime eq 8:32AM the StartInterval becomes 8:30. This is the maximum value of [IntervalStart] that is less or equal to [StartTime].
This step requires an inline function. This is necessary to use the values from the current row (the value of the field [StartTime]) to find the value in an "external" table, the intervals table.

 

"SecondsInFirstInterval"
This column holds the amount of seconds that have to be allocated to the first interval.

 

"NumberOfIntervals"
This column holds the number of intervals that will be spanned by the duration (including incomplete intervals).

"ListOfIntervals"

This column contains a simple list of integers starting from 1 to NumberOfIntervals. This column will expanded.

 

"IntervalAllocatedTo"

This column holds the interval the number will be allocated to, and is also used to create a relationship between the intervals table and the table from your sample data.
image.png

 

"SecondsInAllocatedInterval"

This column holds the seconds that are allocated to the interval.

The data type of this column is changed to decimal.

 

The following screenshot shows an example using a matrix visual, using

the column "IntervalStart" from the table Intervals as column header and

the column  "Employee#" as row header.

The column "SecondsInAllocatedInterval" is used as value:

image.png

 

Hopefully, this provides what you are looking for and helps to tackle your challenge.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

@TomMartens , 

 

Thank you so much for this. I've known I've needed to leverage power query a lot more, so that resource is really going to come in handy. 

 

The pbix you sent is doing exactly what I want it to do. When I'm trying to incorporate it into a bigger data set however, I'm getting stumped. It's most likely a user issue. My data set isn't a table, it's an ODBC connetion to Google BigQuery, in the near future I'll be pointing to a data set or data flow instead. When I mimic the custom column that I think you insterted, my column is coming up and saying Function. I'm sure I need to click on it and put something in there, I'm just not sure what. Any insight?

callcenterH3lp_0-1653867242797.png

 

Hey @callcenterH3lp ,

 

I formatted the M code a little different for the first custom column that contains the syntax for the anonymous function.

Make sure that there is no each in this line
, ( row ) =>

 

 

Table.AddColumn(
            // the table
            #"Changed Type"
            // the new column name
            , "TheMostCurrentStartInterval"
            //row is just a name, the current row will be passed to an anonymous function
            , ( row ) =>
            let

 

 

 

Make sure that there is an each in your code
, each [IntervalStart] <= row[StartTime]

 

 

let
                // storing the value of column inside a variable, 
                // basically this is not necessary as column value can be accessed directly see a little below
                //theStartTime = row[StartTime],
                mostCurrentIntervalStart = 
                    Table.Sort( 
                        Table.SelectRows( 
                            #"Intervals"
                            // each is important as allow to reference the inner row, in this example
                            // the filter will appliead to each row to filter the remote table 
                            // referencing the variable from above
                            //, each  [IntervalStart] <= theStartTime
                            // referencing the the value of the "outer" row  
                            , each  [IntervalStart] <= row[StartTime] 
                        )
                        , {{"IntervalStart", Order.Descending}}
                    // the below syntax is referencing the column IntervalStart [] from the first row {},
                    // zero-based     
                    )[IntervalStart]{0}
            // the value of the step mostCurrentIntervalStart will be returned from the anonymous function
            in mostCurrentIntervalStart

 

 

 

Hopefully, this helps to get where you want.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

@TomMartens , 

 

absolutely perfect. I saw the "each" in there earlier, but when i took it out, i seemed like it broke it worse. My data source wasn't called "Table", so i changed that and fixed some capitalization and (I had Duration as duration) and other little quirks and it loaded perfect. Again, this is awesome, I really appreciate your help on this. 

Hey @callcenterH3lp ,

 

my pleasure!

 

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
mahenkj2
Continued Contributor
Continued Contributor

Hi @TomMartens ,

 

Thanks for the link. I have been learning M and information on the link looks to be quite unique. Let me go through.

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors