Time Interval Grouping

[Anonymous]

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. 

[TomMartens · Accepted solution]

Hey @Anonymous ,

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

[TomMartens · Accepted solution]

Hey @Anonymous ,

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

[MikeFs]

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

[Anonymous]

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

[TomMartens · Accepted solution]

Hey @Anonymous ,

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

[Anonymous]

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

[TomMartens]

Hey @Anonymous ,

my pleasure!

Tom

[mahenkj2]

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.