Forum Discussion
Fill Down based on category
- 3 years ago
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"
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
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"
- bklyn33 years agoAdvocate II
Thank you some much for your help. You're a genius !