How to handle nested data from American Community Survey Census Data?

Hi PWard ,

I assume you are using the download button at the top of the page:

And select the CSV format or Excel that will give you the hierarchy.

In this case you have to had the hierarchy level on Power Query this can be achieved by getting the data from the different rows based on the advance they have so I made the following steps:

• Use first row has header
• For column Label (Grouping) Replace every 4 spaces into a ; (you can also select another delimiter) however it's a specific character so you need to use the following code:

"#(00A0)#(00A0)#(00A0)#(00A0)"

• Best option is to select the first row with values and delete the text

• Now Split column by delimiter

• Then on the replace nothing by null

• Now fill down all the columns
• Filter on any other column the blanks, this will remove the totals and sub totals
• Remove the first row

Final result

Check full code below:

let
  Source = Csv.Document(File.Contents("G:\ACSST1Y2024.S1810-2026-06-10T162334.csv"), [Delimiter = ",", Columns = 7, Encoding = 65001, QuoteStyle = QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Replaced Value1" = Table.ReplaceValue(#"Promoted Headers","#(00A0)#(00A0)#(00A0)#(00A0)",";",Replacer.ReplaceText,{"Label (Grouping)"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value1", "Label (Grouping)", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Label (Grouping).1", "Label (Grouping).2", "Label (Grouping).3", "Label (Grouping).4", "Label (Grouping).5"}),
    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter","",null,Replacer.ReplaceValue,{"Label (Grouping).1","Label (Grouping).2","Label (Grouping).3","Label (Grouping).4","Label (Grouping).5"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Label (Grouping).1","Label (Grouping).2","Label (Grouping).3","Label (Grouping).4","Label (Grouping).5"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([#"Arizona!!Total!!Estimate"] <> "")),
    #"Removed Top Rows" = Table.Skip(#"Filtered Rows",1)
in
    #"Removed Top Rows"