How to turn one column into a hierarchy

[Anonymous]

How do I turn the attribute column into a defined hierachy with different columns but ensure that my values don't get re-summarized under their parent variable.

Thank you!!

[danextian · Accepted solution]

Hi @Anonymous ,I have managed to create create separte columns for the Attribute items and their breakdown using the code below but these questions are still left unanswered

• What to do with rows 2-9 in your raw data?
• How should the values be aggregated when the total of the breakdown doesn't match with the parent hierarchy?
  

let
  Source = Excel.Workbook(File.Contents("D:\Downloads\data pop.xlsx"), null, true),
  in_Sheet = Source{[Item = "in", Kind = "Sheet"]}[Data],
  #"Promoted Headers" = Table.PromoteHeaders(in_Sheet, [PromoteAllScalars = true]),
  #"Changed Type" = Table.TransformColumnTypes(
    #"Promoted Headers",
    {{"Attribute", type text}, {"Value", Int64.Type}}
  ),
  #"Removed Top Rows" = Table.Skip(#"Changed Type", 8),
  #"Added Custom" = Table.AddColumn(
    #"Removed Top Rows",
    "Count of Preceding Spaces",
    each
      let
        x         = Text.ToList([Attribute]),
        y         = List.Select(List.Transform(x, each Number.From(_)), each _ <> null),
        toTable   = Table.FromColumns({y}),
        remErrors = Table.RemoveRowsWithErrors(toTable, {"Column1"})
      in
        List.PositionOf(x, Text.From(y{0})),
    Int64.Type
  ),
  #"Added Custom1" = Table.AddColumn(
    #"Added Custom",
    "Attribute2",
    each Text.End([Attribute], Text.Length([Attribute]) - [Count of Preceding Spaces])
  ),
  #"Added Custom2" = Table.AddColumn(
    #"Added Custom1",
    "Attribute3",
    each if [Count of Preceding Spaces] = 2 then [Attribute2] else null,
    type text
  ),
  #"Added Custom3" = Table.AddColumn(
    #"Added Custom2",
    "Attribute4",
    each if [Attribute3] = null then [Attribute2] else null,
    type text
  ),
  #"Filled Down" = Table.FillDown(#"Added Custom3", {"Attribute3"}),
  #"Filtered Rows" = Table.SelectRows(
    #"Filled Down",
    each [Attribute4] <> null and [Attribute4] <> ""
  ),
  #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows", {"Attribute"}),
  #"Renamed Columns" = Table.RenameColumns(
    #"Removed Columns",
    {{"Attribute3", "Attribute"}, {"Attribute4", "Sub-Attribute"}}
  ),
  #"Removed Other Columns" = Table.SelectColumns(
    #"Renamed Columns",
    {"Attribute", "Sub-Attribute", "Value"}
  )
in
  #"Removed Other Columns"

[Anonymous]

This was incredibly helpful!! I was trying to decide what to do with the row 2-9 from the raw data and went back and added it to the table with no sub attribute. It looks great in a matrix table and isn't double counting but I think my next goal is to get the 'Total - Age groups and average age of the population - 100% data' to be the main attribute with all the age breakdowns under it. Below is my updated code but again thank you very much for helping get on the right track!!

let
    Source = Csv.Document(File.Contents("data.source\data pop.csv"),[Delimiter=",", Columns=2, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Attribute", type text}, {"Value", Int64.Type}}),
  #"Added Custom" = Table.AddColumn(
    #"Changed Type",
    "Count of Preceding Spaces",
    each
      let
        x         = Text.ToList([Attribute]),
        y         = List.Select(List.Transform(x, each Number.From(_)), each _ <> null),
        toTable   = Table.FromColumns({y}),
        remErrors = Table.RemoveRowsWithErrors(toTable, {"Column1"})
      in
        List.PositionOf(x, Text.From(y{0})),
    Int64.Type
  ),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom", {{"Count of Preceding Spaces", 0}}),
  #"Added Custom1" = Table.AddColumn(
    #"Replaced Errors",
    "Attribute2",
    each Text.End([Attribute], Text.Length([Attribute]) - [Count of Preceding Spaces])
  ),
    #"Replaced Errors1" = Table.ReplaceErrorValues(#"Added Custom1", {{"Attribute2", "0"}}),
  #"Added Custom2" = Table.AddColumn(#"Replaced Errors1", "Attribute3", each if [Count of Preceding Spaces] = 2 then [Attribute2] else if [Count of Preceding Spaces] = 0 then [Attribute2] else null, type text),
  #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Attribute4", each if [Attribute3] = null then [Attribute2] else if [Count of Preceding Spaces] = 0 then 0 else null, type text),
  #"Filled Down" = Table.FillDown(#"Added Custom3", {"Attribute3"}),
  #"Filtered Rows" = Table.SelectRows(
    #"Filled Down",
    each [Attribute4] <> null and [Attribute4] <> ""
  ),
  #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows", {"Attribute"}),
  #"Renamed Columns" = Table.RenameColumns(
    #"Removed Columns",
    {{"Attribute3", "Attribute"}, {"Attribute4", "Sub-Attribute"}}
  ),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Count of Preceding Spaces", "Attribute2", "Attribute", "Sub-Attribute", "Value"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"Count of Preceding Spaces"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns1","0",null,Replacer.ReplaceValue,{"Sub-Attribute"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Sub-Attribute", type text}}),
    #"Replaced Value1" = Table.ReplaceValue(#"Changed Type1","0",null,Replacer.ReplaceValue,{"Sub-Attribute"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Replaced Value1",{"Attribute", "Attribute2", "Sub-Attribute", "Value"})
in
    #"Reordered Columns1"

[danextian]

That's great!

Please mark my post as solution if it solves your initial requirements.

[danextian]

Hi @Anonymous 

What do you mean by values don't get re-summarized under their parent variable ?

I can imagine that the example of hierarchy you want to achieve is 0 to 14 years and then (0  to 4, 5 to 9, etc) which can be done by using the number of spaces ( if that is a space) before range of years (before 0  to 14). How about the rows before that? Also can you please post a sample data that we can easily manipulate (not an image) and your expected result? You can post a link to an Excel file stored somewhere in the cloud.

[Anonymous]

Hi danextian! Thank you for the reply!! I've added the data to the link below:

Data 

My issue is that I have tried to seperate the variables by the spaces but when I create visuals all of the values get aggregated instead of taking the total as is.

Ideally I would like a hierachy slicer of the variables and that there would be no double counting. Thanks again for the help!