Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

V-lianl-msft

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

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: 

 

V-lianl-msft_0-1604651295991.png

 

Expected result: 

 

V-lianl-msft_1-1604651295992.png

 

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 

 

zhenbo3.png

 

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

 

  

zhenbo4.png

zhenbo5.png

 

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. 

zhenbo6.png

 

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. 

 

   zhenbo7.png

  zhenbo8.png

 

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

    ) 

 

  

zhenbo9.png

 

  • 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 

Comments