The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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!
Solved! Go to 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)
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)
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
34 | |
19 | |
19 | |
16 | |
13 |