Add Rows to a Dataset based on date range but last entry needs to go to today

[jeffw14]

I have the below table and I need to populate it with rows for every date. I used a previous post to achieve this and it works great:

{ Number.From([PriorDate])..Number.From([StatusDate]) } 

That fills in a row for each truck in the previous row's date range with that row's status. The problem I run into is that the last status entered for each truck is open until another entry is made into the table changing the status. I need to know the total amount of days in a given status up to the current date (Today). For example, truck 47793 has an entry with a status date of 05/22/2023 and status code of 1. The previous code fills in all the rows up to 05/22/2023 so I can calculate the total days in each status prior to 05/22/2023, but then I cannot create the rows for each day from 05/22/2023 to Today. To further complicate this, if the truck retired prior to today's date, I only need to add the rows up to the RetiredDate and not Today. Can anyone provide the code to add the rows from the last StatusDate to Today (or the RetiredDate if it is less than Today). Thanks in advance

TruckNum	StatusDate	StatusCode	PriorDate	PriorStatusCode	HiredDate	RetiredDate
47793	3/18/2023	1	3/15/2023	2	3/16/2023	12/31/2999
47793	3/18/2023	2	3/18/2023	1	3/16/2023	12/31/2999
47793	5/22/2023	1	3/18/2023	2	3/16/2023	12/31/2999
47792	5/1/2023	1	4/30/2023	2	4/30/2023	12/31/2999
47792	5/15/2023	2	5/1/2023	1	4/30/2023	12/31/2999
47792	6/1/2023	1	5/15/2023	2	4/30/2023	12/31/2999
47791	5/1/2023	1	4/30/2023	2	4/30/2023	6/1/2023
47791	5/10/2023	2	5/1/2023	1	4/30/2023	6/1/2023
47791	5/15/2023	1	5/10/2023	2	4/30/2023	6/1/2023

[jeffw14]

That worked perfect, thank you very much. 

[v-jingzhang · Accepted solution]

Hi @jeffw14 

Here is my solution code. I add a new row for each truck. Then you can populate all dates with your current code:

{ Number.From([PriorDate])..Number.From([StatusDate]) } 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lc9LCsAgDATQu7gWYuKvOYt4/2v0IxUNWu3OQebppKRcjGyVVhbwADJ0n7Fk/2YqOdR7AotAzKyyHhM0JlfE9SSJiiS/CHoIbAUH1rRCm+dCN/1DnAihb0hwIeC/FfUx0TebG2Z9LyZsfCCf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TruckNum = _t, StatusDate = _t, StatusCode = _t, PriorDate = _t, PriorStatusCode = _t, HiredDate = _t, RetiredDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TruckNum", Int64.Type}, {"StatusDate", type date}, {"StatusCode", Int64.Type}, {"PriorDate", type date}, {"PriorStatusCode", Int64.Type}, {"HiredDate", type date}, {"RetiredDate", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"TruckNum"}, {{"All Data", each _, type table [TruckNum=nullable number, StatusDate=nullable date, StatusCode=nullable number, PriorDate=nullable date, PriorStatusCode=nullable number, HiredDate=nullable date, RetiredDate=nullable date]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "New Row Values", each let getValues = Record.SelectFields(Table.Last([All Data]), {"StatusDate", "StatusCode", "RetiredDate"}) in Record.TransformFields(Table.Last([All Data]), {{"StatusDate", each List.Min({getValues[RetiredDate], DateTime.Date(DateTime.LocalNow())})}, {"PriorStatusCode", each getValues[StatusCode]}, {"PriorDate", each getValues[StatusDate]}})),
    #"Added Custom" = Table.AddColumn(#"Added Custom1", "New Data", each Table.InsertRows([All Data], Table.RowCount([All Data]), {[New Row Values]})),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"TruckNum", "New Data"}),
    #"Expanded New Data" = Table.ExpandTableColumn(#"Removed Other Columns", "New Data", {"StatusDate", "StatusCode", "PriorDate", "PriorStatusCode", "HiredDate", "RetiredDate"}, {"StatusDate", "StatusCode", "PriorDate", "PriorStatusCode", "HiredDate", "RetiredDate"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded New Data", "Custom", each { Number.From([PriorDate])..Number.From([StatusDate]) })
in
    #"Added Custom2"

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.