Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
ddijstelbloem
Frequent Visitor

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.

ddijstelbloem_0-1655383629124.png

 

ddijstelbloem_1-1655383704445.png

Partcode  MutationDate   Laatste mutatie  CumInvtQty

ddijstelbloem_3-1655383953068.png

 

 

So the outcome for the new table should be

 

MutationDatePartcodeCumInvQty
1-1-20226020000046
2-1-20226020000046
3-1-20226020000046
4-1-20226020000046
etc.etc  
17-1-20226020000044
18-1-20226020000044
19-1-20226020000024

 

 

What is the correct way to get this new table?

1 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:

Icey_1-1655800215901.png

 

Result Table:

Icey_0-1655800186506.png

...

 

Icey_3-1655800282876.png

 

 

Best Regards,

Icey

 

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

View solution in original post

6 REPLIES 6
ddijstelbloem
Frequent Visitor

you're the best! thanks!

rajulshah
Resident Rockstar
Resident Rockstar

@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.

@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

ddijstelbloem_0-1655457314961.png

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.

 

ddijstelbloem_1-1655457385010.png

 

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:

Icey_1-1655800215901.png

 

Result Table:

Icey_0-1655800186506.png

...

 

Icey_3-1655800282876.png

 

 

Best Regards,

Icey

 

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

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

ddijstelbloem_2-1655887838099.png

 

Hi @ddijstelbloem ,

 

Please check this error appears on which step.

 

 

Best Regards,

Icey

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.