Create Index column using a Power Query in Power Bi
Using a Power Query, you can add an index column to your data that serves as a row counter to rows in data table, it is really a best feature of power query.
Lets go through the following steps.
Go to Ribbon bar, and click on icon Power Query Editor as shown in below screenshot.
Once you click on Power Query Editor, a Power Query Editor window opens.
You can see a Dataset Global-superstore on the left side, we will add index column to this dataset.
Although, this dataset already have a RowId column that uniquely identify each rows in data but we will add a new index column using a Power Query Editor.
Lets add an index to Data. Click on Add Column tab, then expand Index Column Dropdown.
Index column gives you three option as follows.
From 0 – It adds index starting from 0.
From 1 – It adds index starting from 1.
Custom.. – It gives you ability to set index starting number and increment.
Index Starting with 0
Lets select index from 0, It adds index to rows starting from 0, Index column is placed at the last place as shown in below screenshot.
You can also move Index column to first place, just select index column then go to Transform Tab.
Then expand Move Dropdown, and select To Beginning option.
After selecting To Beginning option, you can see now index column is placed to the first position as shown in below screenshot.
Index Starting with 1
In similar way you can add index From 1, It adds index starting from 1.
Go to add columns tab, Select From 1 from Index Column Dropdown.
You can see, Index column is placed at last, renamed it as IndexFrom1.
Lets move this column also to the first place, just do as we did earlier.
You can see, now it is moved to the first place.
Custom Index
It gives you ability to set index starting number and increment to that number.
Go to Add Column tab, Click on Add Column tab, then expand Index Column Dropdown.
Next, select Custom.. option, a Add index Column window opens.
Here you have an option to set custom index, provide a starting number for index and increment to that number.
We have provided index starting number as 1 and want it to be incremented by 2. That means each next rows will have an index number that is incremented by 2 to the index number of previous row.
Lets click on Ok button.
After clicking on OK button. It adds index column at the last place, renamed it as CustomIndex as shown in below screenshot.
You can see, index number for each rows is incremented by 2.
Lets move this column to the first place, and after doing this do not forget to save your changes other wise you will loose you work.
Go to Home Tab, Click on Close&Apply icon button to save your pending changes and close the power query editor.
Once you save you changes, you can see all indexes which you have created are added to your dataset.
Read Also..
