Forum Discussion
Replacing null values with the most recent per category
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 |
Anonymous
after passing the code, click OK, then remove the word "each" at the beginning
6 Replies
Hi Anonymous
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"- Fowmy
Super User
Anonymous
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.