Forum Discussion

joeywong's avatar
joeywong
Helper I
3 years ago
Solved

Extract and categorize the column

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!

Need Help

6 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    3 years ago

    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...

  • joeywong's avatar
    joeywong
    Helper I
    3 years ago

    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's avatar
      Vijay_A_Verma
      Most Valuable Professional
      3 years ago

      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's avatar
        joeywong
        Helper I
        3 years ago

        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!