Column index

[AlessandroBet]

Hi,

i need to insert a column index matching the value of other column

Ex:

Column to match --> Index

A                                   1

B                                   2

C                                   3

C                                   3

B                                   2

D                                  4

A                                  1

A                                  1

Many thanks

[AlessandroBet]

thank you for your fast replay but i want add indesx with DAX code

[rajendraongole1 · Accepted solution]

Hi @AlessandroBet - you can create a calculated column as like below:

Index1 =

VAR LookupTable = ADDCOLUMNS( DISTINCT('soert_org'[Column to match]), "@Index", RANKX( DISTINCT('soert_org'[Column to match]), 'soert_org'[Column to match], , ASC, DENSE ) )

RETURN LOOKUPVALUE( Soert_Org[Index], Soert_Org[Column to match], 'soert_org'[Column to match] )

Hope this works.

[AlessandroBet]

It blocks here --> RETURN LOOKUPVALUE( Soert_Org[Index]

[rajendraongole1]

you have to use a calculated column . 

sharing the pbix FYR.

[AlessandroBet]

how can i attach the pbx file?

[rajendraongole1]

you can upload it in a google drive and share the link to download.

But i have attached pbix file with solution. can you please check and confirm

[MFelix]

Hi @AlessandroBet ,

Using Power Query you can use any of the solutions provided in the video below:

https://www.youtube.com/watch?v=_PBX3RPXxHw

[rajendraongole1]

Hi @AlessandroBet  - You can create an Index Column based on Unique Values using Power Query in Power BI.

Steps in Power Query
Open Power Query Editor (Transform Data).
Remove Duplicates on the column you want to match:
Select the column (e.g., Column to match).
Click Remove Duplicates (under Home tab).
Add an Index Column:
Go to Add Column → Click Index Column (Choose Start from 1).
Re-merge with Original Table:
Go to Home → Click Merge Queries.
Merge the original table with the deduplicated table using Column to match.
Expand the merged table to bring in the Index column.

output is at soert_org

Please find the attached pbix.