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. Continue Reading