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?IDCreatedDateOldValueNewValueUser12/13/2025, 4:29:39 PMDraftIn ReviewA22/14/2025, 4:29:39 PMDraftIn ReviewA12/18/2025, 12:21:30 PMApprovedPresentedA22/18/2025, 12:21:30 PMApprovedPresentedA12/18/2025, 4:08:18 PMPresentedDraftC22/18/2025, 4:08:18 PMPresentedDraftC12/18/2025, 8:45:12 AMIn ReviewApprovedB22/18/2025, 8:45:12 AMIn ReviewApprovedB12/19/2025, 5:14:33 PMIn ReviewApprovedB12/19/2025, 8:48:44 AMDraftIn ReviewA12/24/2025, 4:07:45 PMApprovedPresentedA12/24/2025, 4:19:46 PMPresentedIn Pricing ReviewA12/24/2025, 9:23:35 PMIn Pricing ReviewApproved By PricingD22/25/2025, 5:14:33 PMIn ReviewApprovedB12/25/2025, 6:32:18 AMApproved By PricingAcceptedA22/25/2025, 8:48:44 AMDraftIn ReviewA22/26/2025, 4:07:45 PMApprovedPresentedA22/26/2025, 4:19:46 PMPresentedIn Pricing ReviewA22/26/2025, 9:23:35 PMIn Pricing ReviewApproved By PricingD22/27/2025, 6:32:18 AMApproved By PricingAcceptedA

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 CreatedDateSortedTable = Table.Sort(Source,{{"ID", Order.Ascending}, {"CreatedDate", Order.Ascending}}),// Add Index column to track orderIndexedTable = Table.AddIndexColumn(SortedTable, "Index", 0, 1, Int64.Type),// Duplicate the table to merge with itselfNextStepTable = Table.SelectColumns(IndexedTable, {"ID", "CreatedDate", "Index"}),// Merge the original table with the next stepMergedTable = Table.NestedJoin(IndexedTable, {"ID", "Index"}, NextStepTable, {"ID", "Index"}, "NextStep", JoinKind.LeftOuter),// Expand the merged column to get the next CreatedDateExpandedTable = Table.ExpandTableColumn(MergedTable, "NextStep", {"CreatedDate"}),// Rename the next step date columnRenamedTable = Table.RenameColumns(ExpandedTable, {{"CreatedDate", "NextCreatedDate"}}),// Calculate time differenceTimeDiffTable = Table.AddColumn(RenamedTable, "TimeDiff", each try Duration.TotalMinutes([NextCreatedDate] - [CreatedDate]) otherwise null, type number),// Calculate first and last step durationGroupedTable = 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 stepDurationTable = Table.AddColumn(GroupedTable, "TotalDuration", each Duration.TotalMinutes([LastStep] - [FirstStep]), type number)inDurationTable Hope this helps!😁😁

Forum Discussion

Tavi_

Frequent Visitor

1 year ago

Solved

Time between steps

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

Need Help

freginier
1 year ago
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!
😁😁

6 Replies

Akash_Varuna

Super User

1 year ago

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:
- Add an Index Column.
- Add a Custom Column:
  if [ID] = PreviousRow[ID] then Duration.From([CreatedDate] - PreviousRow[CreatedDate]) else null
First to Last Duration:
- Group by ID and calculate:
  Duration.From(List.Max([CreatedDate]) - List.Min([CreatedDate]))
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

Tavi_

Frequent Visitor

1 year ago

Hi, Thanks.

But I'm getting this error.

Akash_Varuna

Super User

1 year ago

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

freginier
Solution Sage
1 year ago
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!
😁😁
ronrsnfld
Super User
1 year ago
What would you expect for results from the data you posted?
Omid_Motamedise
Super User
1 year ago
Can you show your desired result?

Forum Discussion

Time between steps

6 Replies

Related content

Building Real-Time Indian Railways Dashboard in Microsoft Fabric – A step-by-step guide

Rename applied step in Power Query without waiting for step to refresh

Building a Real-Time Weather Dashboard in Microsoft Fabric: Step-by-Step Guide

Time vs Time Scatter Chart

Stepped Layout Option Missing