Power Query or the Query Editor allows you to insert an INDEX COLUMN which is very useful in many ways
Suppose we have following data.
| Country | City |
| Saudi Arabia | Riyadh |
| Saudi Arabia | Jeddah |
| Saudi Arabia | Abha |
| Saudi Arabia | Taif |
| Saudi Arabia | Jubail |
| United States | New York |
| United States | Washington |
| United States | Miami |
| United States | California |
| United States | Chicago |
| England | London |
| England | Manchester |
| England | Liverpool |
| England | Glasgow |
Using Query Editor>>>Add Column…. we can easily an Index Column as shown in picture below
But what if we need an INDEX for each COUNTRY separately
Using DAX, you can insert Index Column for each CATEGORY (i.e. COUNTRY in this example) using the RANKX function
Using a Calculated Column
Index by Country (Column) = RANKX ( FILTER ( ALL ( TableName ), TableName[Country] = EARLIER ( TableName[Country] ) ), TableName[City], , ASC, DENSE )
Using a MEASURE
Index by Country (Measure) = RANKX ( FILTER ( ALL ( TableName ), TableName[Country] = SELECTEDVALUE ( TableName[Country] ) ), CALCULATE ( SELECTEDVALUE ( TableName[City] ) ), , ASC, DENSE )
Copy the data above and try doing it yourself