Add Index Column In Power BI Using Power Query Editor

Introduction

 
Power BI has a feature of adding ‘Index Column’ using power query editor. Through power query editor I have added ‘Custom column’, ‘Conditional column’ and ‘Column from Example’ in my previous articles. To see these articles, click on the below links.
Here in this article you will see the new feature; i.e., ‘Index Column’ in ‘Add Column’ tab of Power query Editor. Let’s start.
 
Step 1
 
First you need to open the Power query editor to click on ‘Transform Data’. Add a new source into it. I am adding ‘Excel sheet’ having ‘Employee data’.
 
Add Index Column in Power BI using Power Query Editor
 
Add Index Column in Power BI using Power Query Editor
 
Add Index Column in Power BI using Power Query Editor
 
Step 2
 
Go to ‘Add Column’ tab. Here you can see many options to add columns. I have covered a few options in my previous article that I mentioned above. Here I am using ‘Index Column’ feature.
 
Add Index Column in Power BI using Power Query Editor
 
Here you get three options:
 
From 0
 
Once you click on ‘From 0’ your value (or index) of the column will start at  zero.
 
From 1
 
If you use ‘From 1’ option your data will  started at one.
 
Custom
 
This is the last option, here you can mention the number from where you want to start and provide the ‘increment by’ number.
 
Let's start with ‘From 0’ option. So, click on it.
 
Add Index Column in Power BI using Power Query Editor
 
Once you click on it you will see that a new column will be added into the right side of the table and data will start at 0. I have renamed this column as ‘Index from 0’ by double clicking on it or right click on it and clicking on ‘Rename’.
 
Add Index Column in Power BI using Power Query Editor
 
Similarly, you can start indexing from 1 by choosing ‘From 1’ option.
 
Step 3
 
I want to add one more column for serial numbers; i.e. ‘S. N.’ at the start of the table. Here I am using ‘Custom’ option. So, click on ‘Index column’ drop down menu and click on ‘Custom’. A new window ‘Add Index Column’ will open. Provide the ‘Starting Index’ and ‘Increment’, I am giving 1 as starting index and 1 as increment and renaming this column as ‘S. N.’
 
Add Index Column in Power BI using Power Query Editor
 
By default, this additional column shows at the right side of the table but I want this at the beginning as a first column. To do so, right click on this column and you will see ‘Move’ option. Click on ‘Move’ and further click on ‘To Beginning’. Now this column will move at the beginning and become a first column.
 
Add Index Column in Power BI using Power Query Editor
 
Duplicate Column
 
In power query editor we have a ‘Duplicate Column’ feature. Use this feature if you want to make a copy of a column. Select the column and then click on ‘Duplicate Column’. A copy of that column will be created. I am selecting ‘ename’ column.
 
Add Index Column in Power BI using Power Query Editor
 
Add Index Column in Power BI using Power Query Editor
 
Save these changes by clicking on ‘Close & Apply’ inside the ‘File’ tab.
 

Summary

 
These are the simple steps to add ‘Index Column’ into the table. Hope you understood the article. Thanks for reading.


Similar Articles