Power BI – Index Function in Power Query

By: David Rohlfs

Introduction

One of the most important aspects of a semantic model is having the Row Specific Column. This is a column that is specific to a row of data and is used to build relationships to other tables or queries. But what if your data doesn’t have a column like this? There are a couple of solutions to this problem. You can either build your own from your existing data set, find a way around having multiple tables, or create one using the index function. An index function is a function that places a value on each row and does not repeat itself.

If you are unfamiliar with Row Specific Columns, I suggest reading EPM Strategy’s blog on Row Specific Columns linked below.

In this blog we will cover when you should use an index, some problems that you may find when using the index function, and a short walkthrough demonstrating how to set up an index using Power Query.

When to use it

An index function is generally not the last step in a process of transforming and cleaning data. What I mean by this is that even though you can create row specific columns with an index, the index function is much more commonly used when you are working with difficult or complex transformations in Power Query. Look at Common Problems below to see what I mean about not using an index function for a Row Specific Column.

The most common use I have for the index function is when I am trying to do complex transformations inside of the Power Query. An index function has a unique ability to refer to other rows in a query. The Power Query preview is not your data, but instead a preview of your data. So, when you do transformations and add steps, you are not assigning a new number to your data, but instead just putting a set of data through a list of transformations. This is not very helpful when you start to add new columns that are based on specific rows. But with an index or Row Specific Column, what you end up doing is creating transformations based on the index number instead of the Power Query preview view.

There are a lot of examples with this when you start looking at how to refer to other rows in Power Query.

There are two different types of index functions that you can use. One will start counting at 0, and the other will start counting at 1. Power Query is a system that is much more apt to work with data that starts from 0, but there isn’t much difference in practice if you understand your data.

You can also do some interesting stuff with an index function if you are able to write out the function in M Code. Unlike many functions in Power Query, you get a specific add column function that you need to use when working with an index function. Here is the Syntax for this M Code:

#”Table Step” = Table.AddIndexColumn(table, newColumnName, optional initialValue as nullable number, optional increment as nullable number, optional columnType as nullable type)

If you are familiar with the Table.AddColumn function in Power Query, then you are probably seeing a couple of similarities and differences with the syntax above. You still must write out your previous step (table), create a name for your column (newColumnName), and can give your column a type (columnType), but the other two portions are specific to an index column. The initial Value section is where you can type in a number for the index to start from. Above, I mention how you can start from 0 or 1, but if you type in this line of code you can start from any nullable number. The other section, increment, is where you can change the increase in value to your previous value. If you use the pre-built function in Power Query then this will always be 1, but you can change this to a decimal value or a larger/smaller value if you want to.

If you would like to have the customizable numbers in an index, but you don’t want to learn M Code, you can use the Custom Option under the Index Function.

Common Problems

The most common problem that you will find is that using an index as a relationship building column is still not ideal in most situations. The most optimal way to have a row specific column is by having it built into the data, but if this really isn’t an option and there is not a way to create your own Row Specific Column with the data you have, an index is still an okay solution in small scenarios. Notice that I said small scenarios; this is because the larger the semantic model is, the harder it will be to get an index to match up with other tables.

Walkthrough

Because this blog is only meant to give you the basics on the Index Function, we are not going to do a detailed walkthrough scenario, but instead show creating an index function the normal way in Power Query and go through a demonstration of creating a custom index column using the M Code.

Classic Index Function

To start you are going to need to go to the Power Query. You can do this by clicking on the transform data button in the top of your hoe ribbon on Microsoft Power BI.

Next you should make sure that your rows are ordered and sorted in the way that you want them. This is important if you are planning on doing DAX, M Code, or creating a relationship from the index.

Then you can go to the add column ribbon and select the index option. For this demonstration we are doing a from 0 index.

Now you should have a new column appear named Index.

M Code Custom Index

For this method I suggest being familiar with the M Code basics. If you aren’t, then there is another EPM Strategy blog linked below that goes over the basics of creating custom M Code in the advanced editor.

To start you are going to go to the advanced editor.

Next, we can type in our code. I am going to name my new column “Index”, have it start from 5, and make the increment 2.5. You can change any of these options to fit your specific needs.

Now, if there are no errors in our code, we can click on Done in the bottom right corner.

Finally, we can check that our code worked. Here is my example column:

Conclusion

Even though you probably won’t work with index’s all the time in Power Query, they are still very important to learn about and utilize. If you already knew what an index was, then hopefully you learned something about how they can be used in Power BI or the modifications that you can make to them to fit your semantic model. If you enjoyed this blog, I recommend reading through some of EPM Strategy’ other blogs linked below.

Links to Related Articles:

Row Specific Columns

Remove Duplicates

Group By

Adding a Custom Column

Group By Index

M Code Basics