Referencing Individual Cell Values From Tables In Power Query

[This blog post is relevant to Power Query in Excel 2010/2013, the Get & Transform section on the Data tab in Excel 2016, and the Get Data screen in Power BI Desktop. I’m going to use the term ‘Power Query’ in this post to refer to all of the previously mentioned functionality]

Sometimes, when you’re working with a table of data in Power Query, you want to be able to get the value from just one cell in that table. In this blog post I’ll show you how you can do this both in the UI and in M code, and talk through all of the more advanced options available in M. Incidentally this is a topic I covered in some detail in the M chapter of my Power Query book, but since that book is now somewhat out-of-date I thought it was worth covering again in a blog post.

Referencing Cell Values In The UI

Imagine your data source is an Excel table that looks like this:


If you import it into Power Query, and you want to get the value in the cell on the second row in ColumnB, then in the Query Editor window you just need to right-click on that cell and select Drill Down:


…and bingo, you have the value 5 returned:


Note that this is the value 5, and not the value 5 in a cell in a table – a Power Query query can return a value of any data type, and in this case it’s going to return a single integer value rather than a value of type table. If you load the output of this query into Excel you’ll still see it formatted as a table, but if you’re using the output of this query as an input for another query (for example, you might want to read a value from Excel and use that value as a filter in a SQL query) it’s much more convenient to have an integer value than a table with one column and one row.

Referencing Cell Values in M

You can see in the screenshot above the M code generated for the Drill Down by the UI, and probably guess how it works. Here’s a cleaned-up version of the query from the previous section for reference:

    Source = Excel.CurrentWorkbook(){[Name="SourceData"]}[Content],
    ChangeDataTypes = Table.TransformColumnTypes(
                                        {{"ColumnA", Int64.Type}, 
                                         {"ColumnB", Int64.Type}, 
                                         {"ColumnC", Int64.Type}}),
    GetMiddleCell = ChangeDataTypes{1}[ColumnB]

There are three steps here:

  • Source reads the data from the Excel table
  • ChangeDataTypes sets the data types for the three columns in the table to be Whole Number
  • GetMiddleCell returns the value from the middle cell of the table returned by the ChangeDataTypes step

M allows you to refer to individual values in tables by a system of co-ordinates using the name of the column and the zero-based row number (tables in Power Query are always assumed to be sorted, therefore it making it possible to ask for a value from the nth row). So the expression

returns the value from the cell on the second row in the column called ColumnB of the table returned by ChangeDataTypes, which is 5. Similarly, the expression

returns the value 3, which is the value in the column ColumnC on the first row.

It’s also worth pointing out that the row and column reference can be in any order, so the expression


…returns the same value as


As you’ll see in a moment, the order that the row and column reference come in could be important.

Referring To Rows Or Columns That Don’t Exist

What happens if you write an expression that refers to a row and/or column that doesn’t exist? You get an error of course! So using our example query, the expressions




…will both return errors because there isn’t a fifth row in the table, and there isn’t a column called ColumnD.

However, instead of an error you can return a null value by using the ? operator after the reference. For example, the expression


returns the value null instead of an error:


You have to be careful though! The expression


still returns an error, not because there isn’t a fifth row but because the reference to the fifth row returns a null value and there is no column called ColumnB in this null value.


The solution here is to reverse the order of the references, like so:


or even better use ? with both references:



Unfortunately using ? won’t stop you getting an error if you use a negative value in a row reference.

The Effect Of Primary Keys

Did you know that a table in Power Query can have a primary key (ie a column or columns whose values uniquely identify each row) defined on it? No? I’m not surprised: it’s not at all obvious from the UI. However there are several scenarios where Power Query will define a primary key on a table, including:

  • When you import data from a table in a relational database like SQL Server, and that table has a primary key on it
  • When you use the Remove Duplicates button to remove all duplicate values from a column or columns, which behind the scenes uses the Table.Distinct() M function
  • When you use the Table.AddKey() M function on a table

The presence of a primary key affects how the Drill Down functionality works, and gives you another way of referencing individual cells.

Consider the following Excel table, basically the same as the table you’ve seen already but with a new column that uniquely identifies each row:


If you load the table into Power Query and then right-click on the column MyKeyColumn and select Remove Duplicates, you will have set this key as a primary key:


(By the way, you can use the Table.Keys() function to see what keys are defined on a table in Power Query, and there’s an example query showing how to use this in the sample workbook for this post).

Having done this, if you use the Drill Down functionality to get the value from the second row in ColumnB once again, this time you’ll get a query something like this:


    Source = Excel.CurrentWorkbook(){[Name="SourceData3"]}[Content],
    ChangedDataTypes = Table.TransformColumnTypes(
                                      {{"MyKeyColumn", type text}, 
                                      {"ColumnA", Int64.Type}, 
                                      {"ColumnB", Int64.Type}, 
                                      {"ColumnC", Int64.Type}}),
    RemovedDuplicates = Table.Distinct(
    SecondRow = RemovedDuplicates{[MyKeyColumn="SecondRow"]}[ColumnB]

The last step is the important one to look at. The row in the reference is no longer by the row number but by the value from the primary key column instead:



You can still use the previous row number-based notation, but when a table has a primary key column defined on it you can also use a value from the primary key column to identify a row.

A Last Warning About Performance

Being able to reference individual values like this is incredibly useful for certain types of query and calculation. However, bear in mind that there are often many different ways of solving the same problem and not all of them will perform as well as each other. One obvious use of the techniques I’ve shown in this post would be to write a previous period growth calculation, where you need to refer to a value in a previous row in a table – but my experience is that writing calculation using row and column references prevents query folding and leads to poor performance, and an alternative approach (maybe like the ones shown here and here involving joins) often performs much better. There aren’t any general rules I can give you though, you just need to make sure you test thoroughly.

You can download the sample workbook for this post here.

23 responses

  1. Pingback: Dew Drop – September 16, 2015 (#2091) | Morning Dew

  2. Pingback: Query SAP Business ByDesign Web Services via Power Query – Ivan Bond's blog

  3. Very helpful, Chris. I was struggling today to work out how to return a single cell Table as text, to the point that I gave up and asked the question on SO, at which point I learned that the trick was to just return the single cell in the Table. Which led me here, after I had enough terminology to get actual helpful results from a Google search. So much to learn…

  4. Pingback: Combining Data From Multiple Worksheets In The Same Excel Workbook Using Power BI « Chris Webb's BI Blog

  5. let
    Source = Web.Page(Web.Contents(“″)),
    Data0 = Source{0}[Data],
    #”Changed Type” = Table.TransformColumnTypes(Data0,{{“Ažurirano”, type datetime}, {“Plinski dan”,
    Int64.Type}, {“Neravnoteža [kWh]”, Int64.Type}, {“Tip”, type text}})
    #”Changed Type”

    How to get yesterday’s date instead of 03.07.2018?

    Thank you for your answers.

  6. Hi Chris, thanks for all your contributions. How about getting relative set of rows. Can you advise? Eg first 3 rows

  7. Pingback: Fourteenth Blog Birthday « Chris Webb's BI Blog

  8. Hi Chris,Thanks for sharing this post! I have a simple table with two rows (2 different servers) and two columns (server name, status). I am filtering in Power query for the row where the status =1 and then using the drill down functionality to get a single text value for the name of the server. The output of this query then becomes my source for all other SQL Server based connections. This works great until you publish to the web and try to establish a data gateway. I get the following error: “You can’t schedule refresh for this dataset because the following data sources currently don’t support refresh:” the error message continues to list all of the datasets with the source as the text value of the drill down. I believe the issue is due to the data type of the query with the drill down which appears to be a text data type. Do you have any suggestions on how establish a data gateway and use the single text value as is similarly described in your post?

      • The privacy levels are set to “None” in the pbix file for this data source. Is this a privacy setting in the service that you could be referring to?

      • You can turn off data privacy checks in Power BI Desktop, which may explain why it works in Power BI Desktop but not in the service after you’ve published (where you can’t turn off checks). However the answer may be to set the privacy settings on the data sources used to “Public” in the Power BI gateway. You can do this in the Advanced section of the data source definition in the “Manage gateways” screen in the service.

  9. I set the datasource to public in the advanced settings as well as the pbix file. This didn’t work. I’ve tried setting both to none as well but still getting the same error:
    You can’t schedule refresh for this dataset because the following data sources currently don’t support refresh:
    Data source for DB_cyl_master
    Discover Data Sources
    Query contains unknown or unsupported data sources

  10. Pingback: Removing HTML Tags From Text In Power Query/Power BI « Chris Webb's BI Blog

  11. Pingback: Power Query/M Optimisation: Getting The Maximum Value From A Column, Part 2 « Chris Webb's BI Blog

  12. Pingback: Chris Webb's BI Blog: Understanding The “The key didn’t match any rows in the table” Error In Power Query In Power BI Or Excel Chris Webb's BI Blog

  13. Hi Chris,
    I have been usning #”Dates”[3M CR]{0} to reference value in Row {0} and Column “3M CR” from a separate query named #”Dates” (or Dates really…)
    I was wondering if you can take it a step further and reference an earlier step in the Query #”Dates? Reason is that in the final result in #”Dates I have removed the Column I’d like to reference. So reference would look something like #”Dates”[Table/Step Name][3M CR]{0} with the proper syntax for [Table/Step Name]. Is this even possible?

  14. Pingback: Chris Webb's BI Blog: Returning Annotations From OData Sources In Power BI/Power Query Chris Webb's BI Blog

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: