Time between steps

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!

😁😁