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
Community Support
