Forum Discussion
Work out dates between one column on multi rows
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…
Hi Sundar,
This is what i am wanting but how did you do it?
Thank you
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.
Where did you get the date changed column from please as its not in my dataset
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 1and so on.