Forum Discussion
Extract text from the same column_ US Census Data
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!
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.
- Split the RACE column into hierarchical levels based on the number of spaces:
- 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
4 Replies
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.
- Split the RACE column into hierarchical levels based on the number of spaces:
- 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
