Fill missing dates with previous value

[ddijstelbloem]

Hi, Already search for a solution but can not find one which actually fits my request. So please find my question below.

I have a date table and a table with inventory, (related by date) I need to have a new table where the missing dates are filled with the previous value.

Partcode  MutationDate   Laatste mutatie  CumInvtQty

So the outcome for the new table should be

MutationDate	Partcode	CumInvQty
1-1-2022	60200000	46
2-1-2022	60200000	46
3-1-2022	60200000	46
4-1-2022	60200000	46
etc.etc
17-1-2022	60200000	44
18-1-2022	60200000	44
19-1-2022	60200000	24

What is the correct way to get this new table?

[ddijstelbloem]

you're the best! thanks!

[rajulshah]

@ddijstelbloem ,

You can use the following feature:
https://docs.microsoft.com/en-us/power-query/fill-values-column

Please let me know if this didn't help.

[ddijstelbloem]

@rajulshah 

The given solution is not working for me unfortunately. Maybe not completely clear but the Column Partcode consists of multiple parts, each with several mutationdates

If we take the first Partcode 42007505, these are the mutations in a certain period and I need the gab between 4-1-2021 and 20-1-2021 to be filled with 5-1-2021, 6-1-2021, 7-1-2021 and the CumInvQuantity should be 572.,7 for all these dates until 20-1-2021 where it changes. 22-1-2021 until 4-2-2021 should display 268,7 etc.

[Icey · Accepted solution]

Hi @ddijstelbloem ,

Try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc5BDsAgCATAv3CWBLZq7VuI//9GpXiqcNpksoAZAaxgKBXqAvFZsXaaxUhvVoYAP62hT6rYysgQH7YD/QEd0ZRc90/tuOrxisU6cl3d+QI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [MutationDate = _t, Partcode = _t, CumInvQty = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"MutationDate", type text}, {"Partcode", Int64.Type}, {"CumInvQty", Int64.Type}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"MutationDate", type date}}, "en-GB"),
    #"Sorted Rows" = Table.Sort(#"Changed Type with Locale",{{"Partcode", Order.Ascending}, {"MutationDate", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "FilledMutationDate", each let EndDate_ =
let NextPartcode_ = try #"Added Index"[Partcode]{ [Index]  + 1 } otherwise null, NextDate_= try #"Added Index" [MutationDate] { [Index]  + 1 } otherwise null
in if [Partcode]=NextPartcode_ then Number.From(NextDate_)-1 else Number.From([MutationDate])
in {Number.From([MutationDate])..EndDate_}),
    #"Expanded FilledMutationDate" = Table.ExpandListColumn(#"Added Custom", "FilledMutationDate"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded FilledMutationDate",{{"FilledMutationDate", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"MutationDate", "Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"FilledMutationDate", "Partcode", "CumInvQty"})
in
    #"Reordered Columns"

Source Table:

Result Table:

...

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ddijstelbloem]

I have replaced the source but now I keep getting an error. Can you see what is wrong?

[Icey]

Hi @ddijstelbloem ,

Please check this error appears on which step.

Best Regards,

Icey