Forum Discussion

bklyn3's avatar
bklyn3
Advocate II
3 years ago
Solved

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...
  • ronrsnfld's avatar
    ronrsnfld
    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"