Custom Column to sort properly

[jazzista]

Hello Forum: I need some help on creating a custom column so I can sort correctly when displaying the final result in a pivot table: I have a list of accounts that I need to sort by type: Routine, non routine and other. The routine, non routine and other types are within the same column with the accounts as headers. See file attached. I am using excel 2016. Thanks in advance for the help

 

[Ken Puls]

If the column you are testing is Column1:

try    (
          if (
          Text.Contains([Column1], "ROUTINE") 
          or Text.Contains([Column1],"OTHER")
          ) 
          and 
          not Text.Contains([Column1],"TOTAL")
     then
          [Column1]
     else 
          null
     )
otherwise null

 

[jazzista]

Mr. Ken.. Good morning and thank you very much.. It worked. Much appreciated. After that M code , I did fill down on the custom column and now, I have all the columns that I need to pivot this data..... Thanks again