Please note javascript is required for full website functionality.
MVP

Blog

Power Query: Cell Referencing

7 February 2018

Welcome to our Power Query blog.  This week, I take a look at how to reference a cell in an Excel workbook from Power Query. 

I will begin by creating a query from the item data below:

On the ‘Data’ tab, in the ‘Get and Transform’ section, I choose to create a new query ‘From Table’.

This all looks fine, but now I want to add the ‘Grand Total’ from another sheet in my Workbook.

My first step is to name my cell using the Name Box (highlighted above, currently displaying the cell K5).  I will call it ‘Grand_Total’.

I go back to my query, and I choose to create a ‘Custom Column’ from the ‘Add Column’ tab:

I have entered the following formula:

Excel.CurrentWorkbook(){[Name="Grand_Total"]}[Content]{0}[Column1]

When I click ‘OK’, my new column is added.

This method works for character and numeric cells (including dates).

Want to read more about Power Query? A complete list of all our Power Query blogs can be found here.  Come back next time for more ways to use Power Query!

Newsletter