Work out dates between one column on multi rows

[Casi]

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.

Can anyone please help.

[V-yubandi-msft]

Hi @Casi ,

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

Thanks.

[Casi]

Please find data attached test data 

Thank you

[p45cal]

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?)

[Casi]

Hi, Thank you for getting back to me. 

For the first questions i would like the record for 'ceox_newvalue pending information' not to be there for the 10/03.

Second question no it shouldnt. 

Ive added the query to myreport but I am getting the error on some records when expanding:

Thanks for all your help

[p45cal]

re:"For the first questions i would like the record for 'ceox_newvalue pending information' not to be there for the 10/03."

I've updated the file I attached to previous message, perhaps you can confirm it gives the right dates now.

re: "but I am getting the error on some records when expanding"

I'd really need to see the source data that causes that; I've had a go at guessing what might cause it but without success…

You could replace the existing source data in my file with your error-causing data then save and attach it here.

[Casi]

Hi

Please find test data:

test data 

[p45cal · Accepted solution]

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…

[Casi]

Perfect. Works as expected. Thank you so much for your help

[V-yubandi-msft]

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]

Sorry i still need assistance

[p45cal]

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]

Sorry just picked this upim grouping by incident id

[SundarRaj]

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=10475...

[Casi]

Hi Sundar,

This is what i am wanting but how did you do it?

Thank you

[SundarRaj]

@Casi
Below are the steps that I've done in the adv editor. Do let me know if there are any clarifications or explanations needed. Thanks

• Create an Index Column Starting from 1: The first step is to add an index column that starts from 1 instead of 0. This is necessary because you want to refer to the second row in the [DateNotified] column, which will act as the end date. So, the index will start from 1 to point to the second row

• Create a List of Dates: Use the [DateNotified] and [DateChanged] columns to generate a list of dates for each row. This step creates a range of dates between the two columns.

  Expand into Separate Rows: Finally, expand the list of dates into separate rows to get the desired output.

  Error Handling (Try-Otherwise): Since the last row will have an index of '4' but only 3 i.e. (Row 0 to Row 3) rows exist, the process may not return any data for the last row. To prevent errors, the Try-Otherwise condition is used, ensuring that if no data is returned, the last value is used instead.

[Casi]

Where did you get the date changed column from please as its not in my dataset 

[SundarRaj]

Date Changed column is a custom column created just to make sure we have an end date.
How it's done is through creating an Index Column starting from 1. Post this, I used Source[#"Date Notified"] { _ + 1 }
{ _ + } --> By defualt, Index starts from 0, so this gives me one up to the original index number
Source[#"Date Notified"] --> This part says Go to Source table and specifically to the Date Notified Column
So, put together, this piece of code / Date Changed Column says "Go to Source Table and pick up Date Notified column, row number x ( x being the index that we created)
10-02-2025 --> Go to Source Table, pick up Date Notifed Column, Row number 1

and so on.

[p45cal]

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

[Casi]

Hi

please find file as requested

test data.pbix

Thank you

[p45cal]

Nearly there… this needs a login; can you make it available to all or share elsewhere please?