cancel
Showing results for
Did you mean:
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:

 Date Employee# StartTime Duration AgentState 1/5/22 20556 8:32 6071 Inbound 1/5/22 20556 10:14 3600 Unavailable 1/5/22 20556 11:14 7024 Inbound 1/5/22 20724 8:30 9924 Inbound 1/5/22 20724 11:15 3600 Lunch 1/5/22 20724 12:15 9943 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.

Any suggestions or reccomendations would be appreciated.

2 ACCEPTED SOLUTIONS
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:

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.

"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:

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
Super User

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

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.

"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:

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
Regular Visitor

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?

Super User

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
Regular Visitor

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.

Super User

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

Announcements