Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have a table like below and I want to calculate the days (and time) between each step and from first to last step. How do I do that in m query?
ID | CreatedDate | OldValue | NewValue | User |
1 | 2/13/2025, 4:29:39 PM | Draft | In Review | A |
2 | 2/14/2025, 4:29:39 PM | Draft | In Review | A |
1 | 2/18/2025, 12:21:30 PM | Approved | Presented | A |
2 | 2/18/2025, 12:21:30 PM | Approved | Presented | A |
1 | 2/18/2025, 4:08:18 PM | Presented | Draft | C |
2 | 2/18/2025, 4:08:18 PM | Presented | Draft | C |
1 | 2/18/2025, 8:45:12 AM | In Review | Approved | B |
2 | 2/18/2025, 8:45:12 AM | In Review | Approved | B |
1 | 2/19/2025, 5:14:33 PM | In Review | Approved | B |
1 | 2/19/2025, 8:48:44 AM | Draft | In Review | A |
1 | 2/24/2025, 4:07:45 PM | Approved | Presented | A |
1 | 2/24/2025, 4:19:46 PM | Presented | In Pricing Review | A |
1 | 2/24/2025, 9:23:35 PM | In Pricing Review | Approved By Pricing | D |
2 | 2/25/2025, 5:14:33 PM | In Review | Approved | B |
1 | 2/25/2025, 6:32:18 AM | Approved By Pricing | Accepted | A |
2 | 2/25/2025, 8:48:44 AM | Draft | In Review | A |
2 | 2/26/2025, 4:07:45 PM | Approved | Presented | A |
2 | 2/26/2025, 4:19:46 PM | Presented | In Pricing Review | A |
2 | 2/26/2025, 9:23:35 PM | In Pricing Review | Approved By Pricing | D |
2 | 2/27/2025, 6:32:18 AM | Approved By Pricing | Accepted | A |
Solved! Go to Solution.
Hey there!
here's a solution for you:
Steps in Power Query (M Query):
- Sort Data: Sort by ID and CreatedDate in ascending order.
- Add an Index Column: This will help in referencing previous and next rows.
- Create a Duplicate Table: This will allow self-joining for calculating time differences.
- Merge Queries (Self-Join): Merge the table with itself, joining on ID and Index +1 to get the next step.
- Calculate Time Difference: Subtract the CreatedDate of the previous step from the next step.
- Calculate First-to-Last Step Duration: Group data by ID and calculate the difference between the minimum and maximum CreatedDate.
Here's the code to do this:
let
// Load the table (Assuming your table is named 'StepsTable')
Source = StepsTable,
// Sort the table by ID and CreatedDate
SortedTable = Table.Sort(Source,{{"ID", Order.Ascending}, {"CreatedDate", Order.Ascending}}),
// Add Index column to track order
IndexedTable = Table.AddIndexColumn(SortedTable, "Index", 0, 1, Int64.Type),
// Duplicate the table to merge with itself
NextStepTable = Table.SelectColumns(IndexedTable, {"ID", "CreatedDate", "Index"}),
// Merge the original table with the next step
MergedTable = Table.NestedJoin(IndexedTable, {"ID", "Index"}, NextStepTable, {"ID", "Index"}, "NextStep", JoinKind.LeftOuter),
// Expand the merged column to get the next CreatedDate
ExpandedTable = Table.ExpandTableColumn(MergedTable, "NextStep", {"CreatedDate"}),
// Rename the next step date column
RenamedTable = Table.RenameColumns(ExpandedTable, {{"CreatedDate", "NextCreatedDate"}}),
// Calculate time difference
TimeDiffTable = Table.AddColumn(RenamedTable, "TimeDiff", each try Duration.TotalMinutes([NextCreatedDate] - [CreatedDate]) otherwise null, type number),
// Calculate first and last step duration
GroupedTable = Table.Group(SortedTable, {"ID"}, {{"FirstStep", each List.Min([CreatedDate]), type datetime}, {"LastStep", each List.Max([CreatedDate]), type datetime}}),
// Calculate the total duration from first to last step
DurationTable = Table.AddColumn(GroupedTable, "TotalDuration", each Duration.TotalMinutes([LastStep] - [FirstStep]), type number)
in
DurationTable
Hope this helps!
😁😁
Can you show your desired result?
What would you expect for results from the data you posted?
Hey there!
here's a solution for you:
Steps in Power Query (M Query):
- Sort Data: Sort by ID and CreatedDate in ascending order.
- Add an Index Column: This will help in referencing previous and next rows.
- Create a Duplicate Table: This will allow self-joining for calculating time differences.
- Merge Queries (Self-Join): Merge the table with itself, joining on ID and Index +1 to get the next step.
- Calculate Time Difference: Subtract the CreatedDate of the previous step from the next step.
- Calculate First-to-Last Step Duration: Group data by ID and calculate the difference between the minimum and maximum CreatedDate.
Here's the code to do this:
let
// Load the table (Assuming your table is named 'StepsTable')
Source = StepsTable,
// Sort the table by ID and CreatedDate
SortedTable = Table.Sort(Source,{{"ID", Order.Ascending}, {"CreatedDate", Order.Ascending}}),
// Add Index column to track order
IndexedTable = Table.AddIndexColumn(SortedTable, "Index", 0, 1, Int64.Type),
// Duplicate the table to merge with itself
NextStepTable = Table.SelectColumns(IndexedTable, {"ID", "CreatedDate", "Index"}),
// Merge the original table with the next step
MergedTable = Table.NestedJoin(IndexedTable, {"ID", "Index"}, NextStepTable, {"ID", "Index"}, "NextStep", JoinKind.LeftOuter),
// Expand the merged column to get the next CreatedDate
ExpandedTable = Table.ExpandTableColumn(MergedTable, "NextStep", {"CreatedDate"}),
// Rename the next step date column
RenamedTable = Table.RenameColumns(ExpandedTable, {{"CreatedDate", "NextCreatedDate"}}),
// Calculate time difference
TimeDiffTable = Table.AddColumn(RenamedTable, "TimeDiff", each try Duration.TotalMinutes([NextCreatedDate] - [CreatedDate]) otherwise null, type number),
// Calculate first and last step duration
GroupedTable = Table.Group(SortedTable, {"ID"}, {{"FirstStep", each List.Min([CreatedDate]), type datetime}, {"LastStep", each List.Max([CreatedDate]), type datetime}}),
// Calculate the total duration from first to last step
DurationTable = Table.AddColumn(GroupedTable, "TotalDuration", each Duration.TotalMinutes([LastStep] - [FirstStep]), type number)
in
DurationTable
Hope this helps!
😁😁
Hi @Tavi_ ,This can be achived bu custom column could you follow these please
Sort Data: Sort the table by ID and CreatedDate.
Time Between Steps:
First to Last Duration:
Combine Results: Merge the columns for time between steps and total duration.
If this post helped please do give a kudos and accept this as a solution
Thanks In Advance
Hi, Thanks.
But I'm getting this error.
Hi @Tavi_ Power Query does not support referencing a "previous row" directly using terms like PreviousRow i am attaching the code below i am guessing you want two new additional columns for steps !!
let
Source = YourTableName,
// Sort Data
SortedRows = Table.Sort(Source, {{"ID", Order.Ascending}, {"CreatedDate", Order.Ascending}}),
// Add Index
IndexedRows = Table.AddIndexColumn(SortedRows, "Index", 0, 1, Int64.Type),
// Duplicate for Previous Row
PreviousRows = Table.Skip(IndexedRows, 1),
PreviousRowsWithIndex = Table.AddIndexColumn(PreviousRows, "Index", 0, 1, Int64.Type),
// Merge Tables
MergedTable = Table.NestedJoin(IndexedRows, {"Index"}, PreviousRowsWithIndex, {"Index"}, "PreviousRow", JoinKind.LeftOuter),
ExpandedTable = Table.ExpandTableColumn(MergedTable, "PreviousRow", {"ID", "CreatedDate"}),
// Calculate Time Between Steps
TimeBetweenSteps = Table.AddColumn(ExpandedTable, "TimeBetweenSteps", each
if [ID] = [PreviousRow.ID] then Duration.From([CreatedDate] - [PreviousRow.CreatedDate]) else null),
// Group to Calculate First-to-Last Duration
GroupedTable = Table.Group(SortedRows, {"ID"}, {{"FirstToLastDuration", each
Duration.From(List.Max([CreatedDate]) - List.Min([CreatedDate])), type duration}})
in
TimeBetweenSteps
Check out the July 2025 Power BI update to learn about new features.