Time between steps

[Tavi_]

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

[Omid_Motamedise]

Can you show your desired result?

[ronrsnfld]

What would you expect for results from the data you posted? 

[freginier · Accepted 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!

😁😁

[Akash_Varuna]

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

Hi, Thanks.

But I'm getting this error.

[Akash_Varuna]

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