Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
jeffw14
Helper I
Helper I

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

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

 

TruckNumStatusDateStatusCodePriorDatePriorStatusCodeHiredDateRetiredDate
477933/18/202313/15/202323/16/202312/31/2999
477933/18/202323/18/202313/16/202312/31/2999
477935/22/202313/18/202323/16/202312/31/2999
477925/1/202314/30/202324/30/202312/31/2999
477925/15/202325/1/202314/30/202312/31/2999
477926/1/202315/15/202324/30/202312/31/2999
477915/1/202314/30/202324/30/20236/1/2023
477915/10/202325/1/202314/30/20236/1/2023
477915/15/202315/10/202324/30/20236/1/2023

 

 

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

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"

vjingzhang_0-1687855462537.png

 

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

View solution in original post

2 REPLIES 2
jeffw14
Helper I
Helper I

That worked perfect, thank you very much. 

v-jingzhang
Community Support
Community Support

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"

vjingzhang_0-1687855462537.png

 

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.