Filling Gaps between dates for unique IDs

[BrandonRhys]

I feel like this has probably been answered 1000x, but here goes anyway.

I've got a list of Task IDs with a creation date, an updated date, and a remaining time estimate. There are gaps in the updated date column. I would like fill the gaps between the "Issue Created" and the "ChangeLog Timestamp" with the last entered "Remaining Time" value so that I've got a continuous record of the "Remaining Time" for each "Issue ID" from the creation date to the last update.

I would prefer to do this in Power Query.

Thanks in advance!

[BrandonRhys · Accepted solution]

I think I mostly solved it. What I did was pivot the "Issue ID" column against the "Date" column with the "Value" column as the values. I sorted by ascending date and then used Fill Down on the entire table. Finally, I unpivoted the "Issue ID"s and viola! 

[HotChilli]

Thank you for that.  It is all usable in this format.  I won't have time to look at this until later.

I think it might be possible to graph this without generating the additional lines using a date table and a getlastvalue type of measure.  I'll look at it later

[BrandonRhys]

Hey @HotChilli , have you had a chance to take a look at what I need? Any preliminary ideas? I know you are helping out of the goodness of your heart, not because you are getting paid the big bucks to help me, so any ideas/help you can give is greatly appreciated!

Thanks!

[BrandonRhys]

Wow, those tables refuse to format into something particulary readable. Hopefully it gets the gist across? If not, it looks like if you copy and paste them into Excel it preserves the columns.

[BrandonRhys]

Current have data that looks like this:

Issue ID	Updated	Created	Value
A	1/1/2024	1/1/2024	20
A	1/5/2024	1/1/2024	14
B	1/15/2024	1/15/2024	5
C	1/18/2024	1/16/2024	10
A	1/18/2024	1/1/2024	10
C	1/19/2024	1/16/2024	7
B	1/19/2024	1/15/2024	2
C	1/20/2024	1/16/2024	1
A	1/20/2024	1/1/2024	5

As you can see, there are multiple unique IDs with dates when they were created, dates when they were updated, and values assigned to them at those dates. I'm looking for a way to fill the dates between "created" and the various "updates" with the value from the previous update until it is updated again, to create a continuous timeline that I can graph like this: 

Issue ID	NewDate	Created	Value
A	1/1/2024	1/1/2024	20
A	1/2/2024	1/1/2024	20
A	/3/2024	1/1/2024	20
A	1/4/2024	1/1/2024	20
A	1/5/2024	1/1/2024	14
A	1/6/2024	1/1/2024	14
A	1/7/2024	1/1/2024	14
A	1/8/2024	1/1/2024	14
A	1/9/2024	1/1/2024	14
A	1/10/2024	1/1/2024	14
A	1/11/2024	1/1/2024	14
A	1/12/2024	1/1/2024	14
A	1/13/2024	1/1/2024	14
A	1/14/2024	1/1/2024	14
A	1/15/2024	1/1/2024	14
A	1/16/2024	1/1/2024	14
A	1/17/2024	1/1/2024	14
A	1/18/2024	1/1/2024	10
A	1/19/2024	1/1/2024	10
A	1/20/2024	1/1/2024	5
B	1/15/2024	1/15/2024	5
B	1/16/2024	1/15/2024	5
B	1/17/2024	1/15/2024	5
B	1/18/2024	1/15/2024	5
B	1/19/2024	1/15/2024	2
B	1/20/2024	1/15/2024	2
C	1/16/2024	1/16/2024	10
C	1/17/2024	1/16/2024	10
C	1/18/2024	1/16/2024	10
C	1/19/2024	1/16/2024	7
C	1/20/2024	1/16/2024	1

Thanks for taking a look @HotChilli !

[HotChilli]

Please post the sample data as text and show the desired result.  It's not clear from the description what you want here.