Convert the start time and end time into multiple …

Scenario:

Sometimes we may want to add one row every 30 minutes (or other intervals) between the start time and end time in xxx table. Through this blog, I will show you how to do it using M query and DAX.

Table used:

Expected result:

M Query Operation:

Create a custom column to get the number of 30 minutes between start time and end time.

Duration.TotalMinutes([End_Time]-[Start_Time]) / 30

Tips:

About the Duration.TotalMinutes function, please refer this document.

Add a custom column to create a list

List.DateTimes([Start_Time],[count_minutes]+1,#duration(0, 0, 30, 0))

We can get the total needed rows after applying the formula.

Tips:

For specific formula information, please refer to this document.

Expand the list and remove the column that you don’t need.

Tips:

If you want to add one row every 10 minutes, 20 minutes or other time intervals, you can change the red line in the following screenshots.

DAX Query Operation:

Create a calculate table like this,

DAX method =  

VAR Time_ = 

    GENERATESERIES ( TIME ( 0, 0, 0 ), TIME ( 23, 59, 59 ), TIME ( 0, 30, 0 ) ) 

VAR Date_ = 

    CALENDAR ( MIN ( 'DAX - raw data'[Start_Time] ), MAX ( 'DAX - raw data'[End_Time] ) ) 

VAR t = 

    SUMMARIZE ( 

        ADDCOLUMNS ( 

            CROSSJOIN ( Date_, Time_ ), 

            "DateTime_", 

                CONVERT ( [Date] & " " & [Value], DATETIME ) 

        ), 

        [DateTime_] 

    ) 

RETURN 

    SUMMARIZE ( 

        FILTER ( 

            CROSSJOIN ( 'DAX - raw data', t ), 

            [DateTime_] >= [Start_Time] 

                && [DateTime_] <= [End_Time] 

        ), 

        [Name], 

        [value], 

        [DateTime_] 

    )