Forum Discussion
How to handle nested data from American Community Survey Census Data?
I am learning Power BI for work and need to be able to process and report on data from the American Community Survey/other census data. The challenge I am running into is that it is organized in a hierarchical manner that's difficult to filter or visualize. I have tried downloading/importing it as a .csv and excel workbook, so there may be a better way to do it. How do you transform data like this to be more usable?
Link to the pictured dataset: https://data.census.gov/table/ACSST1Y2024.S1810?t=Disability&g=040XX00US04&y=2024
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"
2 Replies
- MFelix
Super User
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"
