Work out dates between one column on multi rows

Can you show how you expect the output to be?

Ideally, attach a simple workbook with just these data (perhaps with a few more incidentids)

Could you save me some time by attaching a workbook with these (source) data in please?

Hi Casi , have a look at this file and let me know if this is what you re looking for? I'll attach the file and images of the output. You can see the code from Advance Editor in the PQ interface. Thanks

https://docs.google.com/spreadsheets/d/1EKubQ_sRc0n-ka1dx6k2xS3uDz0a_c3t/edit?usp=sharing&ouid=104752674875039603034&rtpof=true&sd=true

Hi,

This is the output i would like:

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

In the attached workbook (stolen from SundarRaj !) I've put a variation of his query; it's based on grouping by ceox_incidentid (the only one I can reasonably group on) but I notice in the .pbix file that you might instead want to group on ceox_auditlogid, for which I'll have to tweak.

Your limited source table is on Sheet2 and the query result is on Sheet1.

However there's another question: in the attached, the column DatesLists has inclusive dates, meaning that the cross-over dates appear with both old value and new value:

Is this how you want it?

You should add to the data table on Sheet2 then refresh the query on Sheet1 (right-click somewhere in that table and choose Refresh) to check that it's giving you the results you want.

(There's another thing I haven't so far coded for and that's for the possibility of any incident going 'backwards', that is for example, could it go from Awaiting Validation to Pending Information, and later go back to Awaiting Validation?)

Hi Casi ,

As mentioned  p45cal  earlier, could you please attach the data.  It would be really helpful in accurately resolving the issue. 

Thanks.