Convert the start time and end time into multiple rows with 30 minutes interval

[V-lianl-msft]

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_] 

    ) 

• The purpose is to create a time table and date table, and then use the CROSSJOIN function to combine them. 

Tips: 

If you are not clear about this formula, you can create a Calculate table for each variable, and then observe the results of each step. 

Please check the attached file for details. 

Author: Zhenbo Wang 

Reviewer: Kerry & Ula 

Convert the start time and end time into multiple rows with 30 minutes interval _.pbix