Fill ENDDATE column when the value is blank, with the previous STARTDATE value of the next row

Power Query
1

Hi folks!

I don’t came up with any trick or idea to solve the following issue. Any help would be appreciated!

I have the following blue table with 3 columns.

ID_PRODUCT → One product to simplify the example (on the production data, I have many products with many dates)
StartDate → Different days, ascending order. Unique values.
EndDate → blank.

What I need is to fill the blank values of the ENDDATE column as I show in the orange table.

ENDDATE has to be filled with the previous STARTDATE value of the next row, with ONE condition. On the last register, the ENDDATE value has to be TODAY.

It should be solved with PowerQuery.

DOUBT

I attach a PBIX with the blue column upload.

ITEM_Q.pbix (15.8 KB)

Many thanks!

2

Hello @BernatDuran,

Thank You for posting your query onto the Forum.

In order to achieve the results based on the condition which you’ve specified. You can paste the code onto the “Blank Query” as provided below -

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bcyxDcAgDETRXVwj+c7Be0RpEfuvkaOIAoL26eu3ZvcDWrFIx+WBoPXya3XkpgqJTUUH1YFcNMZB3zo0ZmUe9ftK+ws=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID_Product = _t, #"Start Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID_Product", type text}, {"Start Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"ID_Product", Order.Ascending}, {"Start Date", Order.Descending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"ID_Product"}, {{"FullTable", each _, type table [ID_Product=nullable text, Start Date=nullable date]}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows",{{"FullTable", each Table.AddIndexColumn(_, "Index")}}),
    #"Added Custom" = 
    Table.AddColumn(Custom1, "Custom", each 
        let
            AllDataTable = [FullTable],
            PreviousRowValue = 
            Table.AddColumn(
                Table.AddColumn(
                    AllDataTable, "Previous Row Value", each try AllDataTable [Start Date] {[Index] - 1} otherwise null ),
                "End Date", each if [Previous Row Value] = null then Date.From(DateTime.LocalNow()) else Date.AddDays( [Previous Row Value], -1 ))
        in
        PreviousRowValue),
    Custom2 = 
    Table.TransformColumnTypes(
        Table.Sort(
            Table.Combine(#"Added Custom"[Custom], {"ID_Product", "Start Date", "End Date"} ),
        {{"ID_Product", Order.Ascending}, {"Start Date", Order.Ascending}}),
    {{"ID_Product", type text}, {"Start Date", type date}, {"End Date", type date}})
in
    Custom2

Here’s the screenshot of the final results provided for the reference purposes -

Final Results
Final Results777×300 12.4 KB

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Important Note:

1). I’ve just added few new lines into the table as “PR02” in order to check the results for reference purposes.

2). In your example, you’ve showcased date for Today as “13/11/2022” but today’s date is actually as “14/07/2022”.

Thanks and Warm Regards,
Harsh

End Dates - Harsh.pbix (17.0 KB)

2 Likes
3

Dear @Harsh , IMPRESSIVE! Thanks. That’s exactly what I need :slight_smile:

Regarding the notes:

  1. Perfect, that’s even better
  2. Ops, you are right! Didn’t realize it.

Thanks for your help.

4

Hello @BernatDuran,

You’re Welcome!!! :slightly_smiling_face:

I’m glad that I was able to assist you.

Thanks and Warm Regards,
Harsh