Creating Summary Roles from Multiple Role Names

[Anonymous]

Hi there,

I have data where there are various distinctions of the same role category.

For example, Test Analyst: End to End, Test Analyst: Infrastructure, System Admin: DevOps, System Admin: Operati

ons, Training Manager, Operations Manager etc.

What would be the best way to create a column which aggregates these roles under the same category? For example, simply Test Analyst, System Admin and Managerial.

I know I could use multiple if statements or replace the values but this seems cumbersome and not like best practice. 

Please find sample data below.

Name	Dept	Role
Jane Doe	HR	Test Analyst End to End
John Smith	Research	IT Manager
Frank Frost	Strategy	Operations Manager
Sam Sun	Finances	System Admin Operations
Jack Jill	Engineering	Test Analyst Infrastructure
Dennis Menace	Finances	Finance Manager
Albert Einstein	Strategy	System Admin DevOps

Thanks!

[Vijay_A_Verma · Accepted solution]

If roles are large in number, then it is better to maintain a mapping table. Then you can do a lookup on the mapping table.

I am not sure how many roles are there but on the basis of limited data whatever you have provided, following formula would work in a custom column

= if Text.Contains([Role],"Manager") then "Managerial" else Text.BeforeDelimiter([Role]," ",1)

[Anonymous]

Thank you for this! I will try it out. Just for future reference, do you know of any useful references on the process of creating mapping tables?

[Vijay_A_Verma]

Mapping table means this