Forum Discussion
Replace all Distinct Values in a column
I have a column "Employee" that consists of many different employees repeated multiple times with the cooresponding projects they work on.
ProjectID | Employee
123 John
456 John
789 John
321 Chris
654 Chris
987 Chris
I want to replace the names with an anonymous identifier. John will be "Employee 1", Chris will be "Employee 2".
I found this solution, but I want to know how to replace the values without having to enter specific values. There are many employees to account for, and in future, a new employee may get added and I don't want to have to alter the code.
https://community.powerbi.com/t5/Power-Query/Replacing-multiple-value-at-once/m-p/1365221#M43024
Is there a way to replace a list of values with a corresponding list of values without naming all the values in the code?
You could : use a duplicate of the table, remove all columns except Name, Remove Duplicates,
Add an Index column (from 1). Add a custom column "Employee" & Index number
You can then Merge that Query with the original on the name column to bring the EmployeeX text back
3 Replies
You could : use a duplicate of the table, remove all columns except Name, Remove Duplicates,
Add an Index column (from 1). Add a custom column "Employee" & Index number
You can then Merge that Query with the original on the name column to bring the EmployeeX text back
It can be also done in the same table:
1) group by Employee, use operation "All rows"
2) add index column
3) expand column with grouped rows
4) delete column with Employees' names
