Forum Discussion

BrandonRhys's avatar
BrandonRhys
Frequent Visitor
2 years ago
Solved

Filling Gaps between dates for unique IDs

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!

 

Need Help
  • BrandonRhys's avatar
    BrandonRhys
    2 years ago

    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! 

6 Replies

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    2 years ago

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

  • BrandonRhys's avatar
    BrandonRhys
    Frequent Visitor
    2 years ago

    Current have data that looks like this:

    Issue IDUpdatedCreatedValue
    A1/1/20241/1/202420
    A1/5/20241/1/202414
    B1/15/20241/15/20245
    C1/18/20241/16/202410
    A1/18/20241/1/202410
    C1/19/20241/16/20247
    B1/19/20241/15/20242
    C1/20/20241/16/20241
    A1/20/20241/1/20245

     

    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 IDNewDateCreatedValue
    A1/1/20241/1/202420
    A1/2/20241/1/202420
    A/3/20241/1/202420
    A1/4/20241/1/202420
    A1/5/20241/1/202414
    A1/6/20241/1/202414
    A1/7/20241/1/202414
    A1/8/20241/1/202414
    A1/9/20241/1/202414
    A1/10/20241/1/202414
    A1/11/20241/1/202414
    A1/12/20241/1/202414
    A1/13/20241/1/202414
    A1/14/20241/1/202414
    A1/15/20241/1/202414
    A1/16/20241/1/202414
    A1/17/20241/1/202414
    A1/18/20241/1/202410
    A1/19/20241/1/202410
    A1/20/20241/1/20245
    B1/15/20241/15/20245
    B1/16/20241/15/20245
    B1/17/20241/15/20245
    B1/18/20241/15/20245
    B1/19/20241/15/20242
    B1/20/20241/15/20242
    C1/16/20241/16/202410
    C1/17/20241/16/202410
    C1/18/20241/16/202410
    C1/19/20241/16/20247
    C1/20/20241/16/20241

     

    Thanks for taking a look HotChilli !

  • BrandonRhys's avatar
    BrandonRhys
    Frequent Visitor
    2 years ago

    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.

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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's avatar
      BrandonRhys
      Frequent Visitor
      2 years ago

      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's avatar
    BrandonRhys
    Frequent Visitor
    2 years ago

    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!