Forum Discussion

Casi's avatar
Casi
Helper I
1 year ago
Solved

Work out dates between one column on multi rows

Hi, I have a table 'audit' containing all the different stages of an appeal. I need to work out the dates between the date the status was changed but I am struggling without a start date.   ...
Need Help
  • p45cal's avatar
    p45cal
    1 year ago

    In the attached, the same query adapted to a changed column name from Date Notified to createdon.

    It produces just short of 900k rows so I've reduced it to one row (to be able to attach it here) which means you'll need to refresh the query to see those rows. it takes a few seconds to generate those rows…

V-yubandi-msft's avatar
V-yubandi-msft
Community Support
1 year ago

Hi Casi ,

Thanks for posting your query in the Microsoft Fabric Community.  The solution provided by SundarRaj , effectively addresses your scenario. By utilizing Power Query indexing and sorting capabilities, it enables the calculation of durations between each status change, even in the absence of a dedicated  start date column.

 

So please review the solution shared by SundarRaj and let us know if you need any additional clarification or details.

 

Many thanks to p45cal and SundarRaj  for your valuable contributions.

 

Best regards,
Yugandhar | Community Support Team

  • Casi's avatar
    Casi
    Helper I
    1 year ago

    Sorry i still need assistance

    • p45cal's avatar
      p45cal
      Solution Supplier
      1 year ago

      Seems every obstacle under the sun is being presented to us:

       

       

       

      Meanwile I'm looking at SundarRaj 's Excel file's Power Query query.

      Meanwhile, one question. are you grouping by ceox_auditlogid or ceox_incidentid or something else?

      • Casi's avatar
        Casi
        Helper I
        1 year ago

        Sorry just picked this upim grouping by incident id