Fill Down based on category

[bklyn3]

Hello

In PowrerQuery, how do I fill down a table based on the latest value for the category in another column ? 

For example if I have the table below, what would be the process to add the missing dates for the category (A, B or C) with the latest value for that same category? 

The rules would be 

1. If the category doesn’t exist for that date, add a line for the category 
2. Fill up the inventory column   latest value for the category or 0 if the is no pervious entry for the category 

Original Table

Date	Operator	inventory
1/1/23	A	5
1/1/23	B	3
2/1/23	A	6
2/1/23	C	9
3/1/23	A	7
4/1/23	C	8

Expected Result (bold lines were added)

Date	Operator	inventory
1/1/23	A	5
1/1/23	B	3
1/1/23	C	0
2/1/23	A	6
2/1/23	B	3
2/1/23	C	9
3/1/23	A	7
3/1/23	B	3
3/1/23	C	9
4/1/23	A	7
4/1/23	B	3
4/1/23	C	8

Thank you

P.

Thank you

[ronrsnfld]

I don't understand the calculations in your inventory column for the missing items, given your explanation of carrying over the last value if the date is missing for a particular operator. eg: for A, I would expect 5,6,7,7 and you show 5,6,7,9.  For B I would expect 3,3,3,3 and you show 3,3,7,7 and for C I would expect 0,9,9,8 and you show 0,9,8,8. You also show that 

3/1/23

C

8

not in bold, yet it does not appear in your first table.

[bklyn3]

Hi Ronrsnfld,
Thanks for your reply
You are right, I completly screwed up the exmaple  😣 (edited now) 
On 1/1/23 (d/m/yy), there is no C so line 3/1/23 gets created for C and since there are no previous C value, 0 is added.
On 2/1/23  there is no B, a line is created for B and the last value of B (3 on 1/1) get copied
On 3/1/23 there is no B, a line is creted for ans the last value of B (3 on 2/1) get copied 
On 3/1/23 there is no C, a line is creted for ans the last value of C (9 on 2/1) get copied 
On 4/1/23, A and B gets created with A =7 & B= 3

[ronrsnfld · Accepted solution]

OK, that is in accord with your description.  Try the following code below. 

By following the Applied Steps, you should be able to figure out the algorithm, but, in brief:

• Create a table with all dates and Operators
• Merge with the original table
• Group by Operators
• Fill-Down the Inventory in the sub-group aggregations to cover the nulls
• Expand the Grouped tables
• Sort into desired order
• Replace remaining nulls with 0's

let
    Source = Excel.CurrentWorkbook(){[Name="Table34"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Date", type date}, {"Operator", type text}, {"Inventory", Int64.Type}}),

//Create table with all dates and operators
    #"All Table" = Table.FromRecords({Record.FromList({List.Distinct(#"Changed Type"[Date])},{"Date"})
                  & Record.FromList({List.Distinct(#"Changed Type"[Operator])},{"Operator"})}),
    #"Expanded Date" = Table.ExpandListColumn(#"All Table", "Date"),
    #"Expanded Operator" = Table.ExpandListColumn(#"Expanded Date", "Operator"),

//Merge table with original data
    #"Join Tables" = Table.NestedJoin(#"Expanded Operator", {"Date","Operator"}, #"Changed Type",{"Date","Operator"}, "Join", JoinKind.FullOuter),
    #"Expanded Join" = Table.ExpandTableColumn(#"Join Tables", "Join", {"Inventory"}, {"Inventory"}),
    
    #"Sorted Rows" = Table.Sort(#"Expanded Join",{{"Operator", Order.Ascending}, {"Date", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Operator"}, {
        {"All", each Table.FillDown(_,{"Inventory"})  , type table [Date=date, Operator=text, Inventory=nullable number]}}),
    
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Operator"}),
    #"Expanded All" = Table.ExpandTableColumn(#"Removed Columns", "All", {"Date", "Operator", "Inventory"}, {"Date", "Operator", "Inventory"}),
    #"Sorted Rows1" = Table.Sort(#"Expanded All",{{"Date", Order.Ascending}, {"Operator", Order.Ascending}}),
    #"Replaced Value" = Table.ReplaceValue(#"Sorted Rows1",null,0,Replacer.ReplaceValue,{"Inventory"})
                    
in
    #"Replaced Value"

[bklyn3]

Thank you some much for your help. You're a genius !