Power Query to Determine Consecutive and Non-Consecutive Dates

[legalhustler]

Hello Everyone,

I'm working with a column called "DateCol Transposed," which has nested table values. The table values contains dates which a process failed for each unique ID (see image below). Note the ID column is not shown in the image.

So as an example, if I expand the "DateCol Transposed" column the table will look like below (I disregard the other columns for now):

ID	Column1	Column2	Column3	Column4	Column5	Column6
123	6/17/2024	6/19/2024	6/20/2024	6/21/2024	6/24/2024	6/26/2024
999	6/17/2024
888	6/18/2024	6/19/2024
777	6/10/2024	6/12/2024	6/13/2024

I need to transform the table above (or the nested table values if I do not expand it) to look like the table below using the following rules:

For non-consecutive/single dates (those with no succeeding date), the correction date is the same as the failed date. For consecutive dates (those with succeeding dates), only the first and last dates should be picked. The duration is the difference between the Correction Date and Failed Date.

ID	Failed Date	Correction Date	Duration (Excluding weekends)
123	6/17/2024	6/17/2024	1
123	6/19/2024	6/24/2024	4
123	6/26/2024	6/26/2024	1
999	6/17/2024	6/17/2024	1
888	6/18/2024	6/19/2024	2
777	6/10/2024	6/10/2024	1
777	6/12/2024	6/13/2024	2

I cross posted on another forum but didn't get any replies. You can disregard it because the example had the wrong info.

Excel Forum

Determine Duration for Consecutive and Non-Consecutive Dates

www.myonlinetraininghub.com

Any help is appreciated. Thanks!

 

[legalhustler]

I don’t see any “amount” in the sample data you provided.

I didn’t include it in my original post nor did I show it in the image above but there is a Amount column in my dataset and I need show the most current amount for each consecutive date as shown in my post #9 example. Post #9 shows the data before I grouped the dates into table values.

Your solution will be applied after I expand the date table values and since you grouped the columns I also need to include the Amount column, at least that’s what I think should happen in order for the grouping to work properly.

Makes sense? Thanks!

 

[Matt Allington]

I can help. If you want to know how to add this data column, you need to show where the data is in the source.

 

[Matt Allington]

As promised, if anyone wants to see how I went about solving this problem using the UI, you can see it here Finding the Date of the Next Record in Power Query

 

[legalhustler]

I can help. If you want to know how to add this data column, you need to show where the data is in the source.

My raw data look like below before I grouped them and extracted the dates and transposed them. I showed the grouped data and the transposed dates for each row ID in the image I showed in my Post #1 (I mentioned I did not show the IDs). The amount column is to the left of that but I also did not show that.

Basically, before I do the grouping I need to transform the raw data below so that the consecutive dates (spanning over weekends) shows the most current Amount for each preceding date for each ID. If the date is not consecutive (i.e. single date) then show the amount from the raw data. The Date column is sorted ascending within each ID.

This is the raw data:

Date	ID	Amount
6/20/2024	123	$10
6/21/2024	123	$20
6/24/2024	123	$30
6/26/2024	123	$70
6/10/2024	777	$40
6/11/2024	777	$20
6/18/2024	888	$50
6/17/2024	999	$100

The output should look like this:

Date	ID	Amount
6/20/2024	123	$30
6/21/2024	123	$30
6/24/2024	123	$30
6/26/2024	123	$70
6/10/2024	777	$20
6/11/2024	777	$20
6/18/2024	888	$50
6/17/2024	999	$100

Note: for ID 123 the date range 6/20 - 6/24 is consecutive dates that spans over the weekend, so the most current amount of $30 (from 6/24) is applied to the preceding dates. And 6/26 is not a consecutive date (i.e. there is no preceding or succeeding date) therefore it shows the amount of $70.

Let me know if this helps.

 

[Matt Allington]

But this raw data is different to the original "raw" data you shared. The original data I helped you with had been pivoted. So it seems to me that the first thing I did was to unpivot what you had pivoted. Is that correct?

 

[legalhustler]

But this raw data is different to the original "raw" data you shared. The original data I helped you with had been pivoted. So it seems to me that the first thing I did was to unpivot what you had pivoted. Is that correct?

I see what you're saying. You're right, I originally pivoted the the data then you unpivoted it. Now I remember, the reason I did that was because I was using another method to extract the first and last column for consecutive dates which is why you see the column "FirstLastColumns" in my screenshot in Post #1. I may have been thinking you needed the dates grouped/unpivoted like that to build a solution. I should remove those unnecessary steps, right? Thanks for noticing that.

Now can you think you can help with the amount solution issue? Thanks in advance.

 

[Matt Allington]

Ok. There would be a solution. Can you make those changes so it starts with the full set of data including the price, then applies what I did already. Post that book and I will take a look.

 

[legalhustler]

Ok. There would be a solution. Can you make those changes so it starts with the full set of data including the price, then applies what I did already. Post that book and I will take a look.

Here you go. I made the updates.

Dropbox

 

[Matt Allington]

There are few issues here.
1. The data you have linked above is different to the original data. I have used the file I shared earlier. Same link will work.
2. The sample data you posted above in the thread does not match the original data you shared. eg ID 77 has dates 10 and 11 June above, but your original data it has dates 10, 12 and 13 of June

I have used my original file and built what I think is a working solution. It simply matches the resolution date to the table containing the Amount, finds the Amount where the match occurs and then fills this number up. I think if you fix the source data it will do what you want

 

[legalhustler]

There are few issues here.
1. The data you have linked above is different to the original data. I have used the file I shared earlier. Same link will work.
2. The sample data you posted above in the thread does not match the original data you shared. eg ID 77 has dates 10 and 11 June above, but your original data it has dates 10, 12 and 13 of June

I have used my original file and built what I think is a working solution. It simply matches the resolution date to the table containing the Amount, finds the Amount where the match occurs and then fills this number up. I think if you fix the source data it will do what you want

Yeah I just put in random dates but shouldn't matter as long as I have a consecutive date that spans over the weekend. This is the data that I posted:

Date	ID	Amount
6/20/2024	123​	$10​
6/21/2024	123​	$20​
6/24/2024	123​	$30​
6/26/2024	123​	$70​
6/10/2024	777​	$40​
6/11/2024	777​	$20​
6/18/2024	888​	$50​
6/17/2024	999​	$100​

Your output should be like below. The last consecutive date should be the amount that fills up for each corresponding ID. For ID 777, 6/10 and 6/11 are consecutive dates so the amount for 6/11 ($20) should fill up to 6/10. For ID 888 and 999 they are not consecutive dates (i.e. single dates) so it should just return the amount from the data.

Date	ID	Amount
6/20/2024	123​	$30​
6/21/2024	123​	$30​
6/24/2024	123​	$30​
6/26/2024	123​	$70​
6/10/2024	777​	$20​
6/11/2024	777​	$20​
6/18/2024	888​	$50​
6/17/2024	999​	$100​