Forum Discussion
Solved
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 seve...
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
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
