Forum Discussion

AlessandroBet's avatar
AlessandroBet
Helper V
1 year ago
Solved

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      ...
Need Help
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    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.
rajendraongole1's avatar
rajendraongole1
Super User
1 year ago

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.