Extract and categorize the column

[joeywong]

Hi members,

I need help on this.

I have a table with a remarks column. The remarks column is unstructured with spacing, text, numbers, and symbols. This column contains many words, but it also includes keywords such as "A," "B," and "C." 

I want to create an additional column and categorize the remarks column.

I have tried adding columns with “columns from an example”, but it does not work out.

May I know how to go about it? Thanks!

[joeywong]

Hi Vijay

Thanks in advance for your help.

Please find below an example for the Remarks and Categorize columns.

A new column, "Categorize," was added based on the Remarks column. Thanks!

[Vijay_A_Verma]

Use this formula in a custom column where you will need to populate {"AA","BB","CC","DD"} appropriately.

= Text.Combine(List.Intersect({Text.Split([Remarks]," "),{"AA","BB","CC","DD"}}),", ")

[joeywong]

Hi Vijay,

Thank you for your attempt to help. However, this solution does not work. There are some null rows in between. 

Do you have any other solutions? Thanks!

[v-cgao-msft · Accepted solution]

Hi @joeywong ,

How about use the conditional column? like this:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[Vijay_A_Verma]

Use a try otherwise block

= try Text.Combine(List.Intersect({Text.Split([Remarks]," "),{"AA","BB","CC","DD"}}),", ") otherwise null

[Vijay_A_Verma]

Can you give us an example of your Remarks column and how to categorize them? It will be easier for us to give the solution...