Relative reference in calculated column

Absolute and Relative References in Power Query: R1C1 Excel-style approach

PowerQuery is a great instrument that can do much more than just take data from source and pull it in a table or Power Pivot. We can clear and transform data in multiple ways, but there are some transformations, usual in Excel, which are not so convenient to make in Power Query.

For example, what if I need a relative reference to a specific “cell” (a value from exact row in exact column) in a PowerQuery table? Or reference to a value that is in specified column and 4 rows above from referencing row? In Excel this is obvious, I need just point on it with mouse, ensure that I removed “$” (absolute) signs from row part of reference, that’s all. But in PowerQuery I can’t do this so easy.

But anyway, a solution, still obscure, is reachable.

First of all, let’s found how we can access a particular value from a Power Query table.

The easiest way to understand item addressing in Power Query, in my mind, is analyzing of steps code.

Absolute row references

Suppose we have a simple table of two columns, “Date” and “Amount”. It has 5 rows, and in the first column it filled with, suddenly, dates, in second – some values:

Absolute and Relative References in Power Query

Source table

We would like to get a value from cell B4, exactly 120.
In Excel it’s easy – we just write a reference like =B4, or =R4C2 in R1C1 style.

Can we use this reference style in PowerQuery? Yes.

Load this table in Power Query, right-click on desired value and “drill to details”. Okay, we got 120.

Now take a look to code:

In third row of code we see desired reference. Note that we refer to a previous step (as usual), then we got {2} and then – column name [Amount].

Important: rows and lists in Power Query have so called zero-based index: first position in list always has index of 0, same for the row numbers in a table. So when we ask Power Query for value from third row, we have to use 2 as row position.

This formula could be decrypted as follows: give me a value from [Amount] field in 2nd record from Source table. In other words, formula looks for specified record number inside table, and then return a specified field’s value from this record.

Formula that we get from editor actually uses two consecutive approaches: Name{Argument} expression and Record[Field] expression.

Actually, this Name{Argument} syntax has several implementations:

  • if Name is a list and Argument is a number, expression returns the item of Name list at specified position;
  • if Name is a table and Argument is a number, expression returns a row at specified position from Name table;
  • if Name is a table and Argument is a record, expression returns the unique row from table Name that matches the field values of record Argument for field names that match corresponding table-column names.

Last option listed above adds a lot to imagination of what we can do with it, but now we looking straight to our question: absolute and relative references to a single value.

A Record[Field] expression has relatively same syntax: it looks up a field in a record by field name. If the specified field does not exist in the record, an error is raised.

Look at our formula again: first, we get a row (record) from Source table at position 2:

Then we get a field [Amount] from this record (row of table is a record with column names as field names):

Looks easy, yes?

But wait, if we ask PowerQuery to give us a single column from table, we’ll got exactly the list:

So, if we want to get a specific value from that list (see (1) above in Name{Argument} description), we just add a position number in braces next to the list name:

We can shorten this two steps in one:

and get desired value of 120.

So actually we have two possible ways to reach single value from a Power Query table.

Let’s compare two item addressing versions:

  • Look to a specific record in a table and get a specific field from this record: MyValue = Source{2}[Amount]
  • Get a list from table column and get a value from specific position in this list: MyValue = Source[Amount]{2}

It looks so similar and can give us confidence that there is no difference between column name and position order. Actually those are different approaches, and we need to remember it for future purposes.

But anyway, both this approaches are giving us a key to any type of row references in Power Query tables.

Relative row references

In sample table we have data that looks like account remnants. Suppose we need to get a calculated column which will show us a dynamic change of account values. For this purpose, we have to get a value from row above, and compare it with the value in current row.

But in previous examples we can see that position reference and column reference is hard-coded into formula. So we have to find a way to transform row numbers to relative-style reference.

And here Excel comes to help us (again). What we’ll do in Excel to make this kind of calculation? In simple way, we just put a formula in C3:  =B3-B2

and fill column with it. But let’s write this formula in R1C1 style:  =RC[-1]-R[-1]C[-1]

Which means “take value from this row and one-column-to-left and subtract a value from one-row-up and one-column-to-left”. In most cases in PowerQuery we will operate with values from known columns, and now let’s imagine that we can omit the part relating to the column and use only R1-style reference: =RR[-1]

Ok, that it is. We need to get row number for current row and calculate a number of row above, so we then could use formula in one of this kinds:

or

So, is there a way to get a row number in PowerQuery? Yes. It is special Index column.

Index column generates a list of integer values, starting from N and with step S, for each row in a table. Power Query UI allows us select one of two most popular indexes: “From 0” (default) and “From 1” (both have incremental step 1), or design our own list with custom Start and Step parameters

Ok, let’s add the “Index” column (from 0) after our Source step in Power Query:

Now we have all we need: we know a column name and can get a row number from “Index” column. Let’s rename previous step to “AddInd” (so we could write it fast) and add a custom calculated column with entering this formula in dialog window:

This gives us full formula for this step looks like this:

What we just do is refer to a value from column “Index” in each row as an argument to Name{Argument} expression.

Actually, in this example I used list-position style of reference (1) against table-record style (2). Look at result in PowerQuery preview window:

Relative reference in calculated column

Here we can see how a relative row reference works in Power Query

We got a desired list of Amount increments, but also got an error for the first row. Of course, there is no rows with position of (0-1)=-1. If we probably could use {position} bigger than last row index, then we should use “?” (a question symbol) after position reference. It called “optional item selection”.

Let’s see how it works. Go one step back and add another custom column with this formula:

And what we got is:

Optional Item Selection in Power Query

“Optional Item Selection” returns null if we exceed row counts

i.e. reference to non-existing position in list was replaced with null value. But, as I told before, it works only with positions equal or bigger than count of items in list (or of records in table). For negative position number formula generates an error.

Ok, lets remove this step and return to previously added custom column, which has an “Error” value in first row.

What we can do with this error? We can add another step with UI: replace errors. Just select our Custom column, then Transform-Replace errors…, and put 0 in dialog window. Or just add this formula in the editor:

Ok, we’ve got what we want:

Replacing errors with values in Power Query

Now we rapleced errors with specific “hard-coded” values

If we would like to replace errors not with exact values but with some other calculations or functions, it is better to use “try-otherwise” construction (it works like IFERROR function in Excel). We just need to edit previous step (when we added first custom column). Click on “gear” right to the step name and replace the formula with this:

or make necessary corrections directly in formula bar. There could be any expression after “otherwise” operator, for example, you can use custom function, enter a text o return null value.

Errors handling with "try-otherwise" in Power Query 'M' Language

Now we replaced errors using very customisable “try-otherwise” expression

Hey, that’s all. Congratulations, now we know how to make an absolute and relative row references in Power Query tables.

This trick has a lot of uses, mainly in area of raw data transformation and cleaning.

But BE CAREFUL: using relative references on big data tables could cause significant resources consuming and may increase query refresh time.

You can download sample workbook to see how it works and try all tricks from this post

What about relative column references? It is kind of tricky and rare, but we can do it also. Next time.

If you want to read more on advanced Power Query transformations and data modelling, I highly recommend the next books:

Share this
Transfer values to other columns in Power Query

Comments

  1. Great article. can you provide the sample file as well? Thanks.

  2. Hello Maxim,
    Can you provide a reference to further discussion on relative column references. I see Imke’s post above. I can imagine this is how one might construct a function that processes column-wise.

    • Maxim Zelensky
      May 30, 2017 - 08:45

      Hello Dan!
      I think there is still no such special post about relative column reference in my blog.
      There are few ways to refer to a column: by its name and by its position in the table.
      1) Table.Column(tablename, columnname) result is a list of all values from the column, starting from 0. So, Table.Column(tablename, columnname){3} will return the 4th element from the named column.

      2) Table.ColumnNames(tablename) gives us a list of column names from the table, starting at 0. So we can get the name of the 4th column as Table.ColumnNames(tablename){3} and pass this name as the argument to the function from 1).

      So these two ways describe absolute references (A1 and R1C1 style respectively). For relative column reference they need more improvement.

      3) if we know a position of current column (name it N, remember that numbers started from 0), and want to relate to the column two positions to the right, then we just need to modify function from 2): Table.ColumnNames(tablename){N+2}
      and then pass the result again to the 1).

      4) If we don’t sure about the position of the current column, then we can obtain it from List.PositionOf(Table.ColumnNames(tablename), currentcolumnname).

      So, relative column reference works via step 4, then 3, then 2 or 1, where in the step 1 or 2 we can get relative row via Index column.

      Maxim

  3. Thanks for your post !

  4. Hi Maxim,
    I have just solved my previous question.

    If a add a “try otherwise 0” statement to my custom column then I am able to use the custom column as a join in merge queries.

    It seems that the last row of the custom column ( {[Index] + 1} ) may have remained unresolved until I added the “try” statement.

    Thanks

    James

    • Maxim Zelensky
      March 16, 2017 - 18:32

      Hi James, it is really cool that you solved your problem!
      Good Luck!

      Cheers, Maxim

  5. Hi Maxim,
    Great post – thank you. I am using this technique to add a column in a table which conditionally returns a value or null. I am attempting to use the returned value as a column join in a merge table function. As soon as I expand any columns from the merged table I get the following error message:

    “Expression.Error: There weren’t enough elements in the enumeration to complete the operation.
    Details:
    List”

    Please can you help?

    Thank you in advance.

    James

  6. How to do it in Power M ?
    It returns Expression.Error: We cannot convert the value to type List.

    • Maxim Zelensky
      November 1, 2016 - 17:22

      Hi ZOUBAIER. I dont understand your conditions and what is not working here. If it possibe, send a description of your problem to info (at) excel-inside.pro, I’ll take a look

  7. Great tip Maxim

    Do you know any custom function to pull worksheet name?

    Thanks!

    • Hi rolo,
      I do not sure what do you mean. Can you describe it a little more?
      Usually, if you are working with Power Query in Excel or in Power BI Desktop, and take data from other Excel file, there is a step named Navigation in steps panel, where you can see a table with all sheets and tables names in this file. You can take worksheet name from this table.

  8. Thanks for sharing this article, I Tried RELATIVE ROW REFERENCES on my data set, It works well,but the performance is too slow, About 5 or 6 rows per second.It takes a long time to load a 60000 rows dataset

    • Maxim Zelensky
      July 29, 2016 - 18:14

      Hi ryan-gao,
      Yes, depending on your data and transformation you used there could be a big leak in performance.
      But, there could be other ways to make necessary calculations – depending on your needs. Check my other posts about unusual transformations, may be you’ll find your solution
      Cheers,
      Max

  9. Thank you so much for this concise explanation on how to return a value from a table or list!

  10. Marcus Croucher
    June 22, 2016 - 06:46

    Question – once the index is created, we could take an approach of creating a column of relative reference numbers and then use a merge to the same table to bring through the amounts. Could this be a faster way of achieving this?

    • Maxim Zelensky
      July 12, 2016 - 19:16

      Hello Marcus! Sorry for the late answer, somehow I missed your comment.
      Actually we can do it the way you talking about: add another Index column started from -1, merge table to itself and apply these calculations. And it could be really faster, I think (did not tested, but seems to).
      In my mind, if you can avoid this “cell reference” approach, you ought to avoid. But sometimes it is necessary, and if you know how to – you have a tool.

      Thanks for contributing your minds!

  11. Hi Maxim,
    Just had an example where also column headers needed to be referenced dynamically by their position, as the names would change. Hope you don’t mind me posting it here, as this is meanwhile my reference link when it comes to cell referencing in Power Query/M 🙂

    ColumnName = List.Range(Table.ColumnNames(YourTable, 0,1))

    This returns the column name of the first column (zero-based-index!)

    • Maxim Zelensky
      April 25, 2016 - 21:35

      Hi Imke!
      Thank you a lot for this comment. Unfortunately, I’m too busy now to update this post or write a new one about “relative column names coming soon”, so your comment gives a good advice for people seeking this info. Thanks again!

Leave a Reply

Your email address will not be published / Required fields are marked *