Time Interval Grouping

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

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