Replacing null values with the most recent per category

[dannyboc]

Hello,

I have the following product table. The custom column assigns with Power Query the same values for quantity, and replaces null with the most recent value for each Product code, when it's available. Otherwise, the result is null. I tried using column from examples, but there is no solution. Any ideas?

Thank you

Product Code	Date	Quantity	Custom Column
ABC123	1/20/2021	1	1
BED123	2/15/2021	2	2
BED234	2/20/2021	2	2
CED234	3/20/2021	1	1
EFG677	4/1/2021	3	3
EFG677	4/5/2021	null	3
EFG677	4/7/2021	2	2
HFJ123	5/1/2021	null	null
HFJ123	5/4/2021	1	1
UIG888	5/8/2021	1	1
UIG888	5/12/2021	null	1
UIG888	5/20/2021	2	2

[Fowmy]

@dannyboc 

You need to add a custom column:

(t)=>  
List.Last(Table.SelectRows(#"Changed Type", each  _[Product Code] = t[Product Code] and _[Quantity] <> null and _[Date] <= t[Date])[Quantity])

The file is attached below.

[dannyboc]

Hi @Fowmy 

Thank you for your reply! For some reason the result in my end is a function for each row, not a value.

I checked the format of each column and looks fine. Any ideas?

Thank you

[Fowmy · Accepted solution]

@dannyboc 

after passing the code, click OK, then remove the word "each" at the beginning 

[dannyboc]

Thank you so much @Fowmy ! This worked for me

[dannyboc]

Hi @Fowmy ,

Running the code in Power query works, however it takes a lot of time after Close and Apply, after leaving the pc for a lot of hours, the table didn't loaded. Is there any way to calculate the column in DAX rather than Power Query?

Thanks

[Vera_33]

Hi @dannyboc 

I am not a fan of example column, it looks like a Fill Down, you can try this way

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc87DoAgDAbgu3QmwRYUVh/42p2INzBu3l+FgKAx6UDzpX+LtVA3LZIABsipuIrwfsPKLDSm80Qcy0AUiIR09Ex5agOJT6Dph0qpq5Ucg4i3xE37sW1vVPmusZ/9heUTGMcSlPkdyzRorZ3oX0H6JCaa/Xo9AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product Code" = _t, Date = _t, Quantity = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Quantity", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Product Code"}, {{"allrows", each _, type table }}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.FillDown([allrows],{"Quantity"})),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Product Code", "Date", "Quantity"})
in
    #"Expanded Custom"