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"
Greg_Deckler's avatar
Greg_Deckler
Community Champion
9 years ago

So that I understand this, if there is another date for an Application ID, then a status change occurred and you want the difference between this next date and that date. Otherwise you want the difference between that date and today?

MaddyPena's avatar
MaddyPena
Frequent Visitor
9 years ago

Thats right. If the date changes, but the application doesn't, a status change occured.

The data is sorted on Application ID, so we can be sure that when the ID changes, the previous row was the last status of the prior ID.

    • MarcelBeug's avatar
      MarcelBeug
      Community Champion
      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"
      • MaddyPena's avatar
        MaddyPena
        Frequent Visitor
        9 years ago

        MarcelBeug, thank you so much!

        This is what I needed, I mean, I'm still trying to really understand what you did, but this is amazing!

         

        Thanks again :)