It can often be useful to create an index column in power bi. But power Query does not allow you to create a grouped index for a specific product category. Let’s predict this simple example:
As you can see we have two columns, one that contains the group (column 1) and one that contains values (column 2). First of all on power query we have to add the index column starting from 1 and close the window.
Now that we have the index column, the next step is to create a calculated column that uses the following measure:
SequenceColumn1 =
VAR CurrentValue = Sheet2[Index]
RETURN COUNTROWS (
FILTER (
CALCULATETABLE (
Sheet2;
ALLEXCEPT ( Sheet2; Sheet2[COLUMN 1] )
);
Sheet2[Index] <= CurrentValue
)
)
Where
- Current Value: is the column used to order our value. In our case is the index but we can order the column using a specific value
- Allexcept: have to contain the column used to do the group by of countrows
- Sheet2[Index] <= CurrentValue: is the same value used to create order
The result is :
As you can see now the index is grouped by a specific attribute and is repeated for each attribute combination.
