Power Query - For null, copy the contents of the row from the previous day

Question

I would like to achieve this effect

input

output

Now my question is how to transform? solution to group data by date but I don't know what to do next, unfortunately I still lack the skills.

I would be very grateful for your help in solving this problem.

Answer 1

1. Group By Date and Operation All Rows named "Product"
2. Replace each _ with each each if [Product] <> {null} then [Product] else null and delete all the type table stuff in the Table.Group formula.

= Table.Group(#"Changed Type", {"Date"}, {{"Product", each if [Product] <> {null} then [Product] else null}}})

3. Fill Down on the Product column
4. Expand The Product Column to New Rows

Key here is you need to replace a null list with just null so you can fill down.

See example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyUdJRMlSK1QFxjSBcKM8YwjNC5RpDuSYoak1ReGYQngmUaw6TjAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Product = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Product", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date"}, {{"Product", each if [Product] <> {null} then [Product] else null}}),
    #"Filled Down" = Table.FillDown(#"Grouped Rows",{"Product"}),
    #"Expanded Product" = Table.ExpandListColumn(#"Filled Down", "Product")
in
    #"Expanded Product"