Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
JK_PowerBINew
Helper II
Helper II

Creating Summary Roles from Multiple Role Names

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.

 

NameDeptRole
Jane DoeHRTest Analyst End to End
John SmithResearchIT Manager
Frank FrostStrategyOperations Manager
Sam SunFinancesSystem Admin Operations
Jack JillEngineeringTest Analyst Infrastructure
Dennis MenaceFinancesFinance Manager
Albert EinsteinStrategySystem Admin DevOps

 

Thanks!

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

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)

1.png

View solution in original post

3 REPLIES 3
Vijay_A_Verma
Super User
Super User

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)

1.png

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?

Mapping table means this

1.png

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors