Have you ever found yourself in a situation where you’re dealing with a large dataset that lacks row numbers or an index, leaving you desperately searching for a solution to bring some much-needed structure to your data? Fear not! In this blog post, you’ll learn how to add row numbers to your data, bringing organization and coherence to your analysis.

But before we dive into the “how,” let’s take a moment to appreciate why adding row numbers, or indexing your data, is incredibly advantageous. By assigning unique identifiers to each row, you unlock a treasure trove of benefits.

Row numbers offer a systematic approach to identifying and referencing individual rows, effortlessly guiding you to pinpoint specific data points amidst the vast ocean of information. Moreover, these row numbers facilitate seamless sorting and filtering operations, empowering you to efficiently organize and analyze your data with ease. Lastly, embracing indexing enhances data integrity and guarantees precise calculations, especially when navigating intricate relationships between rows.

With that out of the way, let’s explore ways you can effortlessly create row numbers and bring order and clarity to your datasets.

Create Row Number in Power Query

The most straightforward approach to generating row numbers is by utilizing the Index Column option found in the Add Column ribbon of Power Query. This handy feature not only allows you to effortlessly create row numbers but also provides the flexibility of employing zero-based indexing and even offers customizable indexing options tailored to your specific needs.

To create an index column using this method, simply navigate to the Add column menu. From there, click on the dropdown arrow within the Index Column option, where you can effortlessly choose whether to start the indexing from 1 or 0 or even opt for custom indexing, tailored to your specific needs.

If you opt for the Custom option, Power Query will present you with the Add Index Column window where you can fine-tune the row numbering process. Here, you have the freedom to specify the Starting Index value and determine the desired increment using the Increment box. Once you’ve carefully defined your requirements, simply click on the OK button to proceed.

According to the provided specifications, Power Query has generated an index column that initiates at 1 and incrementally increases by a single value. If desired, you have the option to rename and reposition the index column to be the leftmost column.

If you want to skip moving your mouse around, you can create an index column using the M code. Mashup Query Language or M language is Power Query’s data transformation and preparation tool. It’s a very adept tool for data manipulation tasks.

To use M language to create an index column, follow these steps:

1. Add a new step to the list in the APPLIED STEPS tab. You can do this in two ways.

You can click on the Add Step icon beside the formula bar. When you do this, it will add the name of the previous in the formula bar. You can clear this and enter the syntax you want to use to create the index column.

The second way to add a new step is to right-click on the last step in the APPLIED STEPS tab and select Insert Step After from the contextual menu.

= Table.AddIndexColumn(#"Renamed Columns", "Index", 1, 1, Int64.Type)

2. Once you’ve added the step, make sure to clear any existing content from the formula bar. Next, simply paste the provided syntax. This syntax utilizes the Table.AddIndexColumn function, which is responsible for generating the index column.

The function uses five parameters.

• table – the table you want to add to the new column.
• newColumnName – specifies the name of the column that will be added. The newColumnName must be provided as a text value and enclosed within quotation marks or else the function will return an error.
• initialValue – (optional) represents the first value of the index. The value must be provided as a numerical number.
• increment – (optional) the value that determines how much to increase the index value. The increment value must also be a number.
• columnType – specifies the data type for the index column.

After pasting the syntax, click on the checkmark icon to the left of the add step icon to add an index column that starts at 1 and increments by 1.

Add Row Number to Visual with DAX

Ever been in that scenario where you create a measure that sums up column values. And when you add it to a visual where it’s filtered by categories, you struggle to visually identify the highest and lowest values. It can be quite a tedious process, but there’s a way to eliminate this hassle. By creating a DAX measure that incorporates row numbers or an index, you can simplify and streamline the process.

While adding row numbers using Power Query can be helpful, creating dynamic row numbers that can adapt based on specified filters and calculations takes it to the next level. Luckily, achieving this is a breeze as you only need to create a measure using just a couple of DAX functions.

To begin, go to the Home tab in your Power BI window and select New measure.

Total Ordered Quantity = CALCULATE(
    SUM(
        Orders1[Quantity ordered]
    )
)

First, a measure called Total Ordered Quantity is created using a combination of the CALCULATE and SUM DAX functions. This measure calculates the total number of items ordered.

By creating the measure in DAX studio using a slightly different syntax, you can see the value returned by the measure – 25, 268.

Row Number = RANKX(
    ALL(
        Orders1[Customer Segment]
    ),
    [Total Ordered Quantity],,
    DESC
)

To create a dynamic index or row number, the Row Number measure is created using the RANKX DAX function.

RANKX is a function that returns the rank of a value from a list of values in a table. The following are its parameters:

• <table> – any table, virtual or physical, upon the statement is assessed.
• <expression> – any DAX statement that returns a single scalar value. The <expression> is computed for every row in the <table>, producing a comprehensive range of values for ranking.
• [<value>] – (optional) any DAX expression that returns a single value whose rank is to be determined. If <value> is omitted, the function will use the value from the expression at the current row.
• [<order>] – (optional) specifies what order to rank <value>. It’s a parameter that accepts Boolean values of ASC or DESC. When set to ASC, the lowest <value> receives the highest rank, while the highest <value> is assigned the lowest rank. For instance, in a list of numbers (5, 4, 3, 2, 1), 1 would be ranked as 1 and 5 would be ranked as 5. Conversely, when <order> is set to DESC, the ranking order is reversed.
• [<ties>] – (optional) determines how the function handles ties. It can be set using the Skip or Dense keywords. When you use Skip (the default setting), the subsequent rank value is determined by adding the count of tied values to the rank value of the tie itself. For instance, if there are six (6) values tied with a rank of 12, the next value in line will be assigned a rank of 18 (12 + 6). With Dense, the next value after a tie will be the next rank value. That is if six (6) values are tied with a rank of 12, the next rank value will be 13.

Within the RANKX syntax used to create the Row Number measure, the <table> parameter takes the form of ALL(Orders1[Customer Segment]). This particular syntax constructs a single-column table, ensuring that all entries from the Orders1[Customer Segment] column are encompassed in the ranking, regardless of any filters originating from that same column.

The key element that makes this possible is the ALL function. It allows the retrieval of all rows within a table or values within a column, disregarding any applied filters.

By evaluating only this specific syntax in DAX Studio, it retrieves a table that includes all the values from the Orders1[Customer Segment] column.

Still on the RANKX syntax, the <expression> is [Total Ordered Quantity] – the measure that returns the total number of items ordered – and the order is DESC.

With this, the RANKX function will return the rank of the [Total Ordered Quantity] value in each row of the Orders1[Customer Segment] table, in descending order.

By placing the [Row Number] measure in a visual alongside the [Total Ordered Quantity] measure and the Customer Segment column, you will create a dynamic index within the visual. This index will be invaluable for easily identifying the position of values relative to their magnitude.

If you’re curious about why the last value in the Row Number column is also 1, let’s explore the reason behind it. This can be attributed to the <value> parameter within the RANKX function. In this particular case, the <value> parameter was omitted in the syntax, causing the value from <expression> in the current row – which is 25268 – to be ranked. Since there isn’t a corresponding item in the Customer Segment column to slice or filter against, the number 25268 is ranked as an individual value within its own context, resulting in a rank of 1.

Conclusions

The advantages of indexing data are clear. By harnessing the power of Power Query and utilizing the Mashup Query Language, you can effortlessly add row numbers to your datasets, bringing structure and organization to your reports.

Also, you can use DAX to create dynamic row numbers that change based on specific criteria. By mastering these techniques, you can bring structure and organization to your reports and ease the analysis process.