Forum Discussion
Fill missing dates with previous value
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?
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.
6 Replies
You can use the following feature:
https://docs.microsoft.com/en-us/power-query/fill-values-columnPlease let me know if this didn't help.
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.
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.
you're the best! thanks!
