Forum Discussion
Column index
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
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.
8 Replies
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.
thank you for your fast replay but i want add indesx with DAX code
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.It blocks here --> RETURN LOOKUPVALUE( Soert_Org[Index]
