Extract text from the same column_ US Census Data

[ale259]

Hello, I need help in extracting the US Census Data. I have the text as the example in column Race. I wonder how I can devide them into several columns based on their detail. I noticed there are several level, differenting by the number of space, so I'm looking for ways to seperate them, either using Power Query or SQL. Any idea is appreciated. Thanks so much! 

[Akash_Varuna · Accepted solution]

Hi @ale259 ,Could you please try this

• Add a custom column to count leading spaces:

  Text.Length([RACE]) - Text.Length(Text.TrimStart([RACE]))
  This will count the number of leading spaces in the RACE column.

• Create a New Column for Each Level:

  • Split the RACE column into hierarchical levels based on the number of spaces:
    • Use Conditional Columns or assign levels to Level1 and so on , based on the number of spaces.
  • Alternatively, use Group By to summarize data by hierarchy.
• Apply the Trim transformation to remove any leading or trailing spaces.
  You could also use SQL with CHARINDEX function to do the same 
  If this post helped please do give a kudos and accept this as a solution
  Thanks In Advance

[ale259]

Hi Akash, I managed to count the leading spaces. Can you guide me on how to split the column based on the number of spaces? There are different level of spaces: 0, 4, 8, 12, 16. Thank you

[ale259]

I have managed to do through all the steps. Thank you so much!

[slorin]

hi @ale259 

May be ?

let
Source = Your_Source,
Split = Table.AddColumn(Source, "Split", each Text.Split([RACE]," ")),
Columns = Table.SplitColumn(Split, "Split", each _, 5)
in
Columns

Stéphane