Forum Discussion

kevbrown1980's avatar
kevbrown1980
Frequent Visitor
4 hours ago

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 consultant changes, for exampe

Brown

Smith

Rodgers

Brown

Tucker

Smith

So Brown would be Index 1 and there would be 2 of them, Smith index 2 and 2 of them, Rodgers 3 and only 1 instance, Tucker 4 and only 1 instance

 

Thanks

Kev

 

power bi

2 Replies

  • benibix's avatar
    benibix
    Icon for Helper V rankHelper V
    2 hours ago

    Hi kevbrown1980

    uzuntasgokberk's code does the job. If you'd rather stay in the ribbon, there's a second route. Duplicate the query, keep only the Consultant column and use Remove Duplicates. Then Add Column › Index Column › Custom, with starting index 1 and increment 1. You now have one row per consultant with a number. Merge that query back into your main table on Consultant, expand the index column, and remove Consultant before you load the table, so the names never reach the model. Brown, Smith, Rodgers and Tucker come out as 1 to 4. As a bonus you get the name-to-ID table as its own query, in case you want to keep the mapping.

  • uzuntasgokberk's avatar
    uzuntasgokberk
    Icon for Super User rankSuper User
    4 hours ago

    Hi 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.