Home ยป Functions

How to Add and Use Index Column in Power Query

The ‘Power Query’, popularly known a ‘Get & Transform’, is an excellent tool pre-installed by Microsoft in its higher versions of excel (Excel 2016 and later). This feature allows excel users to import data from sources to an excel workbook. To name a few, you can get data from CSV, Notepad, JSON, website URL, and many other sources to excel. This feature also enables users to transform this imported data using various power query transformation tools. One of the many transformations that you can do using a power query is adding an index column in an excel table using the power query.

In this blog, we would learn how to add and use the index column feature in excel using power query.

What is Index Column?

Many times, when you get data from some webpage into excel or from other external sources, it does not have the row counters at the beginning of the table. The row numbers allow you to determine how many rows of data does your table contain.

As shown in the image below, the row numbers are missing.

Missing Index Column Excel Table

You can easily insert or add serial numbers (row counters) to excel data using the power query excel feature.

Steps To Add Serial Number (Index Column) using Power Query

To work with the excel power query feature, the first and foremost thing is to import the table data into the power query editor window.

The Power Query Editor window is a separate window that contains various tools to transform the excel table data.

Let us see how to import the excel table into this new transformation window (The Power Query Editor window).

1# Import Excel Table into Power Query Editor Window

  • Select the range of cells or table to import.
  • Then, go to the ‘Data’ tab and navigate to – ‘Get & Transform Data’ group > Get Data > From Other Sources > From Table/Range, as shown below:
Get Data From Table Range Navigation
  • If the ‘Create Table’ window pops out on the screen, then, simply accept it by clicking on OK.

As a result, the selected table would open in the new transformation window, as shown below:

Sample Table in Power Query Editor Window

Do you have this question – My excel does not contain Power Query? If yes, then check excel power query compatibility here.

2# Using Index Column To Add Serial Number

Once the excel table is inside the power query editor window, we can use the ‘Index Column’ feature of Power Query to add serial numbers to the rows.

The Index Column transformation tool is available in the ‘Add Column’ tab (General group) as shown in the image below:

Navigation to Index Column Power Query
  • Click on the ‘Index Column’ option and –
    • Excel inserts a new column having the default name as ‘Index’ at the end of the table.
    • The serial number starts from 0 to n (default option).
Index Column Start with 0 Power Query Editor

However, if you want to start the serial number from 1 to n (instead of 0 to n) in the power query editor window, click on the small downward-facing arrow beside the ‘Index Column’ button and choose the option ‘From 1’.

Index Column in Power Query From 1 to n

As a result, power query would start serial number starting from 1.

Index Column Start with 1 Power Query Editor

Let us now move the index column to the beginning or starting of the table.

To do so, you can either select, drag and drop the column or navigate to Transform > Move > To Beginning, as shown below.

Navigation to Move Column to Beginning

As a result, the index column would move to the beginning of the table.

Adding Index Column in Excel Power Query

Let us finally export the transformed table from the power query window to the excel worksheet back. Use the path – ‘Home’ tab > ‘Close & Load’ option to achieve this.

Power Query Editor - Close & Load Icon

Purpose of Power Query Custom Index Column

In the above sections, we have learned how to insert serial numbers starting from 0 or 1 in an excel table using a power query.

In addition to this, the power query editor window also provides the option to create your own custom index number range with a custom interval.

For example, let us create an index number starting from 5 with interval of 2 numbers between each.

To achieve this, navigate to ‘Add Column’ > ‘Index Column’ > ‘Custom’ option as shown below:

Custom Index Column Option Power Query

As a result, the ‘Add Index Column’ dialog box would appear on your screen, as shown below:

Add Index Column Dialog Box

This dialog box has two input boxes:

  1. Starting Index – Enter the starting number for the serial number. In our case, the start number is ‘5’.
  2. Increment – Specify the interval between two serial numbers. In our illustration, it is ‘2’.
Starting Index and Increment Input Box

As soon as you click on the OK, you would notice that the power query adds a new index column starting from 5 and incremented by 2 (i.e. 7, 9, 11, and so on).

Result of Custom Index Column Power Query

With this we have reached to the end of this blog on ‘How to add serial number column using excel power query’. ๐Ÿ™‚

Leave a Comment