Forum Discussion
Fill Down based on category
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
- If the category doesn’t exist for that date, add a line for the category
- 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
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"
4 Replies
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.
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= 3OK, 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"
