The Fill Down Hierarchies Challenge

21 comments

1. 

   Andrew Lindfield (@ajlind25) January 12, 2019Reply

   Here’s one solution. It prevents the fill down by replacing the nulls with a temporary value – in this case “(prevent filldown)”.

   let
   Source = Excel.CurrentWorkbook(){[Name=”Backlogs”]}[Content],
   #”Added tmpFeature” = Table.AddColumn(Source, “tmpFeature”, each if ([Feature] = null and [Scenario] null) then “(prevent filldown)” else [Feature], type text),
   #”Added tmpBacklog item” = Table.AddColumn(#”Added tmpFeature”, “tmpBacklog item”, each if ([Backlog item] = null and [tmpFeature] null) then “(prevent filldown)” else [Backlog item], type text),
   #”Removed Columns” = Table.RemoveColumns(#”Added tmpBacklog item”,{“Feature”, “Backlog item”}),
   #”Renamed Columns” = Table.RenameColumns(#”Removed Columns”,{{“tmpFeature”, “Feature”}, {“tmpBacklog item”, “Backlog item”}}),
   #”Filled Down” = Table.FillDown(#”Renamed Columns”,{“Scenario”, “Feature”, “Backlog item”}),
   #”Replaced Value” = Table.ReplaceValue(#”Filled Down”,”(prevent filldown)”,null,Replacer.ReplaceValue,{“Scenario”, “Feature”, “Backlog item”})
   in
   #”Replaced Value”

2. 

   Alex Powers January 12, 2019Reply

   Thanks for the challenge Gil. Looking forward to reading what your proposed solution is.

   My Solution: https://pastebin.com/djVrDHAj

3. 

   Nick M. January 12, 2019Reply

   Here’s how I accomplished the first step (still a little shaky on the custom functions right now)

   1) Duplicated the Feature column
   2) Filled Down the Scenario and original Feature columns
   3) Added a custom column that gave null if Backlog Item and the copy of the Feature columns were null, if not give me the value of the feature column that was filled down:

   =if
   [Backlog item] = null and [#”Feature – Copy”] = null
   then
   null
   else
   [Feature]

   4) Selected Scenario, Backlog Item and the Custom Column from above and removed the other columns
   5) Renamed the custom column to Feature
   6) Reordered the columns
   7) Set data types

   Final table matches the output requirement.

   -Nick

4. 

   Mehdi HAMMADI January 12, 2019Reply

   Thank you for the challenge Gil. The solution I propose can be found here https://1drv.ms/x/s!At0U3V37GjTFhEjFWcuCMtnN-86j

5. 

   James Fancke January 13, 2019Reply

   I didn’t quite make it dynamic but I was getting close! I look forward to your solution.

   I took a subtractive approach where I replaced values with nulls after completing a filldown on the whole table.

   Solution also can be found here: https://1drv.ms/x/s!AgoytmOFSCGLgaxvOMh1htExcXKixg

   let
   Source = Excel.CurrentWorkbook(){[Name=”Backlogs”]}[Content],
   InsertIndex = Table.AddIndexColumn(Source, “Index”, 0, 1),
   InsertLevel = Table.AddColumn(InsertIndex, “Level”, each Table.SelectRows(Table.AddIndexColumn(Record.ToTable(Source{[Index]}), “SubIndex”, 0, 1), each [Value] null)[SubIndex]{0}),
   FilledDown = Table.FillDown(InsertLevel,Table.ColumnNames(InsertLevel)),

   ReplaceScenario = Table.ReplaceValue(FilledDown, each [Scenario], each if [Level] < 0 then null else [Scenario], Replacer.ReplaceValue,{"Scenario"}),
   ReplaceFeature = Table.ReplaceValue(ReplaceScenario, each [Feature], each if [Level] < 1 then null else [Feature], Replacer.ReplaceValue,{"Feature"}),
   ReplaceBacklog = Table.ReplaceValue(ReplaceFeature, each [Backlog item], each if [Level] < 2 then null else [Backlog item], Replacer.ReplaceValue,{"Backlog item"}),
   SelectColumns = Table.SelectColumns(ReplaceBacklog,{"Scenario", "Feature", "Backlog item"})

   in
   SelectColumns

6. 

   FrankT January 13, 2019Reply

   As a one-off:

   let
   Source = Excel.CurrentWorkbook(){[Name=”Backlogs”]}[Content],
   ReplacedValue = Table.ReplaceValue(Source, each [Feature], each if [Scenario]=null then [Feature] else “”, Replacer.ReplaceValue, {“Feature”}),
   FilledDown = Table.FillDown(ReplacedValue,{“Scenario”, “Feature”})
   in
   FilledDown

7. 

   cristianangyal January 14, 2019Reply

   let
   Source = Excel.CurrentWorkbook(){[Name=”Backlogs”]}[Content],
   #”Changed Type” = Table.TransformColumnTypes(Source,{{“Scenario”, type text}, {“Feature”, type text}, {“Backlog item”, type text}}),
   #”Filled Down” = Table.FillDown(#”Changed Type”,{“Scenario”}),
   #”Added Custom” = Table.AddColumn(#”Filled Down”, “Feature_Solved”, each if [Backlog item]null then Table.FillDown(#”Filled Down”,{“Feature”}) else null),
   #”Expanded Feature_Solved” = Table.ExpandTableColumn(#”Added Custom”, “Feature_Solved”, {“Feature”, “Backlog item”}, {“Feature.1”, “Backlog item.1″}),
   #”Added Custom1″ = Table.AddColumn(#”Expanded Feature_Solved”, “Custom”, each [Backlog item]=[Backlog item.1]),
   #”Filtered Rows” = Table.SelectRows(#”Added Custom1″, each ([Custom] = true)),
   #”Merged Columns” = Table.CombineColumns(#”Filtered Rows”,{“Feature”, “Feature.1”},Combiner.CombineTextByDelimiter(“”, QuoteStyle.None),”Feature.2″),
   #”Reordered Columns” = Table.ReorderColumns(#”Merged Columns”,{“Scenario”, “Feature.2”, “Backlog item”, “Backlog item.1”, “Custom”}),
   #”Removed Columns” = Table.RemoveColumns(#”Reordered Columns”,{“Backlog item.1”, “Custom”}),
   #”Renamed Columns” = Table.RenameColumns(#”Removed Columns”,{{“Feature.2”, “Feature”}})
   in
   #”Renamed Columns”

   • 

     Gil Raviv Post authorJanuary 28, 2019Reply

     Thank you for sharing Cristian. Can you make it shorter? Look at the other solutions that were shared here.

8. 

   Kunle SOPEJU January 14, 2019Reply

   Thanks for the challenge, Gil.

   My suggested solution for the first part is as follows:

   Import data into Power BI or Power Query in Excel.

   1. Fill down the Scenario column.
   2. Group the table by Scenario column using All rows.
   3. Fill down the [Features] table column within the filtered table ( ala Bill Szysz, Gil Ravi , et al)
   4. Select last column and remove Other Columns.
   5. Expand the selected column

   Pronto !!! You’re done !

9. 

   Tom Allan January 14, 2019Reply

   I like FrankT’s solution, but think it would be better if ended with a step to clean up remaining empty strings that were introduced into Feature column. Looking forward to learning recursive approach.

10. 

    Shungu January 15, 2019Reply

    This is the M function I used to achieve the filled down table with the demoted headers step accommodating different column names assuming a similar Table structure ,( ie 3 columns) and hierarchy. About the different level of hierarchies, that became a little tricky but was almost there.

    (Table as table) => let

    #”Demoted Headers” = Table.DemoteHeaders(Table),
    #”Added Custom” = Table.AddColumn(#”Demoted Headers”, “Parent”, each if [Column2] = null and [Column3] = null then 0 else null),
    #”Filled Down” = Table.FillDown(#”Added Custom”,{“Column1”, “Column2″}),
    #”Added Custom1″ = Table.AddColumn(#”Filled Down”, “Column2.2″, each if [Parent] = 0 then null else [Column2]),
    #”Removed Other Columns” = Table.SelectColumns(#”Added Custom1″,{“Column1”, “Column2.2”, “Column3″}),
    #”Promoted Headers” = Table.PromoteHeaders(#”Removed Other Columns”, [PromoteAllScalars=true])

    in

    #”Promoted Headers”

11. 

    Shungu January 15, 2019Reply

    As a function that can take a table of similar structure and hierarchy thanks to the Demoted Headers Step.

    (Table as table) => let

    #”Demoted Headers” = Table.DemoteHeaders(Table),
    #”Added Custom” = Table.AddColumn(#”Demoted Headers”, “Parent”, each if [Column2] = null and [Column3] = null then 0 else null),
    #”Filled Down” = Table.FillDown(#”Added Custom”,{“Column1”, “Column2″}),
    #”Added Custom1″ = Table.AddColumn(#”Filled Down”, “Column2.2″, each if [Parent] = 0 then null else [Column2]),
    #”Removed Other Columns” = Table.SelectColumns(#”Added Custom1″,{“Column1”, “Column2.2”, “Column3″}),
    #”Promoted Headers” = Table.PromoteHeaders(#”Removed Other Columns”, [PromoteAllScalars=true])

    in

    #”Promoted Headers”

    • 

      Gil Raviv Post authorJanuary 29, 2019Reply

      Hi Shungu,
      Thank you for sharing. Try to modify this function to accept another parameter for the number of hierarchies and operate on multiple columns.
      I suspect that you would need to use List.Accumulate to iterate over the creation of new columns.

12. 

    stevej January 16, 2019Reply

    //replace the hierarchy columns with versions prepped for a Table.FillDown, then do the FillDown
    // (also minimal args validation)

    (Data as table, HierarchyColumns as list) as table =>
    let
    AllColumns = Table.ColumnNames(Data),
    HierIndexes = List.Transform(HierarchyColumns, each List.PositionOf(AllColumns, _)),
    HierData = List.Transform(HierarchyColumns, each Table.Column(Data, _)),

    ValidateInput =
    let
    N = List.Count(HierarchyColumns),
    Names = Text.Combine(HierarchyColumns, “, “),
    ListTooSmall = error “Hierarchy must be 2 or more columns: ” & Names,
    NeedsTwo = N > 1 or ListTooSmall,

    NameMismatch = error “Hierarchy column(s) not found in input table: ” & Names,
    ColumnsCheck = List.Count(HierIndexes) = N or NameMismatch
    in
    NeedsTwo and ColumnsCheck,

    NullToEmpty = each if _=null or Text.Trim(_)=”” then “” else _,
    EmptyToNull = each if _=null or Text.Trim(_)=”” then null else _,

    PrepLevel = (Prv as list, Index as number) as list =>
    let
    IsHier = not List.IsEmpty(Prv),
    HierCol = HierData{Index},
    PrepHier = List.Transform(HierCol, EmptyToNull),
    New = List.Transform(List.Zip({PrepHier, Prv{0}}),
    each if Value.Is(_{0}, type text) then _{0} else if _{1} “” then null else “”),
    Result = if IsHier
    then {New} & Prv
    else {List.Transform(HierCol, NullToEmpty)}
    in Result,

    DenseHierarchy = List.Accumulate(List.Reverse(List.Positions(HierData)), {}, PrepLevel),
    PreppedDataTable =
    if List.Count(AllColumns) = List.Count(HierarchyColumns)
    then Table.FromColumns(DenseHierarchy, HierarchyColumns)
    else Table.FromColumns(
    List.ReplaceRange(Table.ToColumns(Data), HierIndexes{0}, List.Count(HierIndexes), DenseHierarchy),
    AllColumns),

    Result = Table.FillDown(PreppedDataTable, List.RemoveLastN(HierarchyColumns, 1))

    in if ValidateInput then Result else “”

    • 

      stevej January 16, 2019Reply

      //There’s a “not equals” in the code that got ripped because…HTML
      //Here’s the line with a C language “not equals” (!=) where the equivalent M operator would be:

      New = List.Transform(List.Zip({PrepHier, Prv{0}}),
      each if Value.Is(_{0}, type text) then _{0} else if _{1} != “” then null else “”),

      //Also to use the code:
      let
      Source = Excel.CurrentWorkbook(){[Name=”Sample1″]}[Content],
      Result = SparseToDense(Source, {“Scenario”, “Feature”, “Backlog Item”})
      in
      Result

      //Tested for both examples in the post, but nothing else :/

      • 

        Gil Raviv Post authorFebruary 24, 2019

        SteveJ Thank you for sharing. I will publish your solution in my next blog post. You are welcome to contact me and publish a guest blog post to explain your code.

13. 

    Jeremy Thiele January 17, 2019Reply

    Neat problem. Here is a solution that used recursion and group by.

    // Backlogs_Sln
    let
    Source = Excel.CurrentWorkbook(){[Name=”Backlogs”]}[Content],
    /* Source = table, */
    FillDown = recursiveFillDown(Source, 0),
    Headers = Table.ColumnNames(Source),
    TableExpand = recursiveTableExpand(FillDown, 0, Headers)
    in
    TableExpand

    // recursiveFillDown
    (table, depth) =>

    let
    Headers = Table.ColumnNames(table),
    #”Filled Down” = Table.FillDown(table,{Headers{depth}}),
    #”Grouped Rows” = Table.Group(#”Filled Down”, List.FirstN(Headers,depth + 1), {{“Table”, each _, type table}}),
    nestedTables = Table.SelectColumns(#”Grouped Rows”,{“Table”}),
    recursion = Table.TransformColumns(nestedTables, {{“Table”, each @recursiveFillDown(_, depth + 1)}}),

    result = if depth >= (Table.ColumnCount(table) – 1) then nestedTables else recursion

    in
    result

    // recursiveTableExpand
    (table, depth, headers) =>

    let
    expandTable = Table.ExpandTableColumn(table, “Table”, {“Table”}),
    intermediateTable = @recursiveTableExpand(expandTable, depth + 1, headers),

    finalTable = Table.ExpandTableColumn(table, “Table”, headers),

    result = if depth >= (List.Count(headers) – 1) then finalTable else intermediateTable
    in
    result

    • 

      Gil Raviv Post authorFebruary 24, 2019Reply

      Jeremy, Thank you for sharing. I will publish your solution in my next blog post. You are welcome to contact me and publish a guest blog post to explain your code.

14. 

    Micah Dail January 29, 2019Reply

    Here you go. No recursion or custom functions necessary. Essentially it uses a dumb fill to populate all of the possible cells, but then gets the actual column depth for a record and removes any fills outside of that depth.

    // Smart Hierarchy Fill
    let
    Source = Excel.CurrentWorkbook(){[Name=”Backlogs”]}[Content],
    Demote_Header = Table.DemoteHeaders(Source),
    Set_Row_Nbr = Table.AddIndexColumn(Demote_Header, “Row_Nbr”, 0, 1),
    Unpivot_Data = Table.UnpivotOtherColumns(Set_Row_Nbr, {“Row_Nbr”}, “Column”, “Value”),
    Good_Data = Table.TransformColumns(Unpivot_Data, {{“Column”, each Number.From(Text.Replace(_, “Column”, “”)), Int64.Type}, {“Row_Nbr”, each Number.From(_), Int64.Type}}),
    Remove_Header = Table.SelectRows(Good_Data, each ([Row_Nbr] 0)),
    Repivot_Without_Header = Table.Pivot(Table.TransformColumnTypes(Remove_Header, {{“Column”, type text}}, “en-US”), List.Distinct(Table.TransformColumnTypes(Remove_Header, {{“Column”, type text}}, “en-US”)[Column]), “Column”, “Value”),
    Dumb_Fill_Down = Table.FillDown(Repivot_Without_Header,Table.ColumnNames(Repivot_Without_Header)),
    Unpivot_Dumb_Fill = Table.UnpivotOtherColumns(Dumb_Fill_Down, {“Row_Nbr”}, “Column”, “Value”),
    Dumb_Data = Table.TransformColumnTypes(Unpivot_Dumb_Fill,{{“Column”, Int64.Type}}),
    Combine_Good_And_Dumb = Table.NestedJoin(Good_Data,{“Row_Nbr”},Dumb_Data,{“Row_Nbr”},”Dumb_Data”,JoinKind.Inner),
    Expand_Dumb_Data = Table.ExpandTableColumn(Combine_Good_And_Dumb, “Dumb_Data”, {“Column”, “Value”}, {“Dumb_Data.Column”, “Dumb_Data.Value”}),
    Filter_Dumb_Records_For_Brilliance = Table.SelectRows(Expand_Dumb_Data, each ([Dumb_Data.Column] <= [Column])),
    Finalize_Data = Table.SelectColumns(Filter_Dumb_Records_For_Brilliance,{"Row_Nbr", "Dumb_Data.Column", "Dumb_Data.Value"}),
    Rename_Columns = Table.RenameColumns(Finalize_Data,{{"Dumb_Data.Column", "Column"}, {"Dumb_Data.Value", "Value"}}),
    Isolate_Header = Table.SelectRows(Good_Data, each ([Row_Nbr] = 0)),
    Append_Finalized_To_Header = Table.Combine({Isolate_Header, Rename_Columns}),
    Repivot_Data = Table.Pivot(Table.TransformColumnTypes(Append_Finalized_To_Header, {{"Column", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(Append_Finalized_To_Header, {{"Column", type text}}, "en-US")[Column]), "Column", "Value"),
    Ensure_Original_Sort = Table.Sort(Repivot_Data,{{"Row_Nbr", Order.Ascending}}),
    Remove_Row_Nbr = Table.RemoveColumns(Ensure_Original_Sort,{"Row_Nbr"}),
    Restore_Original_Header = Table.PromoteHeaders(Remove_Row_Nbr, [PromoteAllScalars=true])
    in
    Restore_Original_Header

    • 

      Gil Raviv Post authorFebruary 24, 2019Reply

      Micah, Thank you for sharing. I will publish your solution in my next blog post. You are welcome to contact me and publish a guest blog post to explain your code.

15. 

    John BIAN January 4, 2020Reply

    Here’s my solutions fyi:

    Solution 1: Table.Group. The 5th parameter was used.

    let
    Source = Excel.CurrentWorkbook(){[Name=”Backlogs”]}[Content],
    Group = Table.Group( Source,
    “Scenario”,
    {“a”,each Table.FillDown(_,{“Scenario”,”Feature”})},
    0,
    (x,y)=>Byte.From(ynull)),
    Result = Table.Combine(Group[a])
    in
    Result

    Solution 2: List.Generate

    let
    Source = Excel.CurrentWorkbook(){[Name=”Backlogs”]}[Content],

    gen = let lst = List.Buffer(Table.ToRows(Source)),
    num = List.Count(lst)

    in List.Generate( ()=>{{},0},
    each _{1}<=num,
    each let l = List.Buffer(lst{_{1}}?),
    a = List.FirstN(l,each _=null),
    n = List.Count(a),
    b = List.Buffer(List.FirstN(_{0},n)),
    v = List.ReplaceRange(l,0,n,b)
    in {v,_{1}+1},
    each _{0} ),

    rlt = #table(3,List.Skip(gen))
    in
    rlt