Forum Discussion

Tavi_'s avatar
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...
Need Help
  • freginier's avatar
    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!

    😁😁

Tavi_'s avatar
Tavi_
Frequent Visitor
1 year ago

Hi, Thanks.

 

But I'm getting this error.

 

 

Akash_Varuna's avatar
Akash_Varuna
Icon for Super User rankSuper 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