Forum Discussion
Create Distinct Index
Hi, Within my data I have a column called Consultant. I would like to anonymise that consultant so need to create a new index column starting at 1 but need it to only change number when the consulta...
uzuntasgokberk
Super User
Super UserHi Kev, use Power Query to list the consultants in first-seen order, then use each consultant’s position as their ID. In Advanced Editor, add these steps after your current last step, replace #"Previous Step" with its actual name, and point in to WithIndex.
Consultants = List.Buffer(List.Distinct(#"Previous Step"[Consultant])), WithIndex = Table.AddColumn( #"Previous Step", "ConsultantIndex", each List.PositionOf(Consultants, [Consultant]) + 1, Int64.Type ) in WithIndex
Your example returns 1, 2, 3, 1, 4, 2. If IDs must stay fixed across refreshes, store the name-to-ID mapping separately, since a change in row order can renumber them. If report users should only see the IDs, remove Consultant before loading the table into the model.