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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

7 REPLIES 7
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

Sorry for the thread revival, Im hoping someone can help with the above... 

 

Im trying to use the example but Im getting values over 1800 (30 mins) and if you look in the above table there's one record @ 11:00AM thats showing 1824 seconds, Im seeing similar examples when I put my data in to the model and hoping someone can help resolve the issue.  

 

Thanks

@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

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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