If a cell contains a string of text then return that specific text to new column

[Anonymous]

I want to edit my data in query, adding a column to return either of the below job grades

• Assistant Manager
• Associate
• Associate Director
• Analyst
• Consulting Director
• Director
• Executive
• Manager
• N/A
• Partner
• Senior Manager

If the job title column contains any cell with any of the specific grade text. See below an example of the job title column. For example, a formula so that if the cell next to "regions growth executive" would use the list above to match and identify the job gradings in the list above and return the correct job grade grouping - in this case "executive"

Job title	Job grade
Regions Growth Executive
Commercial Audit Senior Manager
Tax Associate Director
Ethics Senior Manager
Restructuring Partner
Commercial Audit Partner
Forensic & Investigation Services Partner
FS Tax Associate Director
Restructuring Partner

Thanks!

[jennratten]

Hello - here is one way you can achieve this result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY89C8JADIb/SujsUBzcRas4CNK61Q7HGa4Bm4MkV/vzPdRB8WN+3s+2LWoMFFlhK/FqPVQT+mQ0YtHN2mIVhwHFk7vAMp3JoEGmKLB37ALKXXN0EyxVY1YZwpoEvcUHqqwnr99MNapJ8paEOMDBifGTfFS+wk0UZCUPp1SW8wXseMxBFJzlD7lHRvKo75YG/iz8saO7AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Job Title" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Job Title", type text}}),
    SplitText = Table.AddColumn(#"Changed Type", "SplitText", each Text.Split([Job Title], " ")),
    JobGrade = Table.AddColumn(SplitText, "Job Grade", each Text.Combine ( List.Intersect( { [SplitText], #"Job Grades"[Job Grade] } ), " " ), type text ),
    #"Removed Columns" = Table.RemoveColumns(JobGrade,{"SplitText"})
in
    #"Removed Columns"

Job Grades Table

Job Titles Table with the result.

[Vijay_A_Verma · Accepted solution]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY89C8JADIb/SujsUBzcRas4CNK61Q7HGa4Bm4MkV/vzPdRB8WN+3s+2LWoMFFlhK/FqPVQT+mQ0YtHN2mIVhwHFk7vAMp3JoEGmKLB37ALKXXN0EyxVY1YZwpoEvcUHqqwnr99MNapJ8paEOMDBifGTfFS+wk0UZCUPp1SW8wXseMxBFJzlD7lHRvKo75YG/iz8saO7AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Job title" = _t]),
    #"Inserted Text After Delimiter" = Table.AddColumn(Source, "Last2Words", each Text.AfterDelimiter([Job title], " ", {1, RelativePosition.FromEnd}), type text),
    #"Inserted Text After Delimiter1" = Table.AddColumn(#"Inserted Text After Delimiter", "Last1Word", each Text.AfterDelimiter([Job title], " ", {0, RelativePosition.FromEnd}), type text),
    ListOfJobGrades = {"Assistant Manager","Associate","Associate Director","Analyst","Consulting Director","Director","Executive","Manager","N/A","Partner","Senior Manager"},
    #"Added Custom" = Table.AddColumn(#"Inserted Text After Delimiter1", "Job grade", each if List.Contains(ListOfJobGrades,[Last2Words]) then [Last2Words] else if List.Contains(ListOfJobGrades,[Last1Word]) then [Last1Word] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Last2Words", "Last1Word"})
in
    #"Removed Columns"