Forum Discussion

MaddyPena's avatar
MaddyPena
Frequent Visitor
9 years ago
Solved

Compare values from previous rows

Hello guys! I have a problem...hope you can help me.   We have a process in which every step is inserted in the database. The goal is to know how many days the process stuck in a particular status,...
Need Help
Tips and Tricks
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    Solution in Power Query (M):

     

    let
    Source = Table1,
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index.1"},#"Added Index1",{"Index"},"Next",JoinKind.LeftOuter),
    #"Expanded Next" = Table.ExpandTableColumn(#"Merged Queries", "Next", {"Application_ID", "Created_Date"}, {"Next.Application_ID", "Next.Created_Date"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Next", "Days", each Duration.Days(if [Next.Application_ID] = [Application_ID] then [Next.Created_Date] - [Created_Date] else DateTime.Date(DateTime.LocalNow())-[Created_Date]), Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Index.1", "Next.Application_ID", "Next.Created_Date"})
in
    #"Removed Columns"