Forum Discussion
bklyn3
3 years agoAdvocate II
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 mi...
- 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"
ronrsnfld
3 years agoSuper User
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
3 years agoAdvocate II
Thank you some much for your help. You're a genius !