Understanding The “The key didn’t match any rows in the table” Error In Power Query In Power BI Or Excel

One of the most common errors you’ll see when working with Power Query in Power BI or Excel is this:

Expression.Error: The key didn’t match any rows in the table

KeyErrorScreenshot

It can occur with almost any data source and in a wide variety of different circumstances, and for new users of Power Query it can be very confusing. In this post I’ll explain what the error message means and when you’re likely to encounter it using a simple example.

TL;DR You’re probably getting this error because your Power Query query is trying to connect to a table or worksheet or something in your data source that has been deleted or been renamed.

Say you have an Excel workbook with three worksheets in called Sheet1, Sheet2 and Sheet3:

WorksheetsBefore

On Sheet1 there’s some data you want to load into Power BI or Excel using Power Query:

Data

It’s very easy to do this: after you click on the Excel data source and select your Excel workbook, you see the Navigator window with the contents of the workbook and select Sheet1:

Nav

…click OK and your query is ready:

PQQuery

What actually happens in this query though? If you click on each of the four steps in the Applied Steps pane on the right-hand side of the screen, you’ll see what each step does. In particular, notice that the first step in the query (called Source) returns a table with one row for each worksheet, table and named range in the workbook:

SourceStep

This is something that happens with many types of data source: after you have connected you have to choose what data inside the data source you want to connect to in the first step, and the list of things you can connect to is returned in a table. This table lists the same things displayed in the Navigator dialog shown above. In the same way, if you connect to a SQL Server database this first step returns a table with all the tables and views in the database; if you connect to an OData feed the first step shows all the resources available through the feed.

The other thing to notice in the table in the screenshot is the Data column, which contains nested table values. If you were to click on one of these nested tables you would navigate to the data in the worksheet named on that row and that is in fact what the second step in the query does.

Here’s the M code for the whole query:


let
Source =
Excel.Workbook(
File.Contents(
"C:\MyExcelFile.xlsx"),
null,
true),
Sheet1_Sheet =
Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" =
Table.PromoteHeaders(
Sheet1_Sheet,
[PromoteAllScalars=true]
),
#"Changed Type" =
Table.TransformColumnTypes(
#"Promoted Headers",
{{"Month", type text},
{"Sales", Int64.Type}}
)
in
#"Changed Type"

If you want to understand how the code in the second step of this query gets the contents of the cell containing the nested table, I have a blog post here that goes into a lot more detail. Basically the second step (called Sheet1_Sheet in the code, but shown as Navigation in the Applied Steps pane) returns the nested table in the Data column from the row in the table where the Item column contains the value “Sheet1” and the Kind column contains the value “Sheet”. It’s the contents of the Item and Kind columns that are used to identify the row in the table that contains the data you want to see – in database terminology these columns are the key columns on the table.

What happens if you change the name of the worksheet that contains the data you want, from “Sheet1” to “Hello”?

WorksheetsAfter

The result is that the first step of the query now returns a table that looks like this:

AfterNav

Since there isn’t a row where the Item column contains “Sheet1” any more, the second step can no longer find the row it’s looking for – the key value it’s looking for no longer exists in the table – which is why you see the error message you do:

KeyErrorScreenshot

There are two ways to fix the error:

  1. Go to the Excel workbook and change the name of the sheet called “Hello” back to “Sheet1” or
  2. In either the Advanced Editor or the Formula Bar edit the M code and replace the reference to “Sheet1” with “Hello” in the second step of the query:
    FormulaBar

In summary, when you’re working with data sources in Power Query you need to be aware that if something in your data source is renamed or deleted then your query will break. You can of course write some clever M code to deal with situations like this but my recommendation is to try to fix the problem in the data source and not in your query.

7 responses

  1. @Chris

    When connecting to Excel Workbooks its best to modify the Navigation step
    as =Source([Name=”Hello”}][Data] – rather than having a double filter for Item = “Hello” and Kind = “Sheet”

    That way if you save the file as a xlsb file the query will continue to work

    Also if you migrate the Excel workbook to an Access Database – then also the 2nd step will remain the same and only the source steps needs a minor modification

  2. Hey Chris, I’ve seen this when I’ve used the wrong user to connect, and the user doesn’t have permissions on the views (or tables) required

  3. Pingback: Power BI: The Key Didn’t Match Any Rows in the Table – Curated SQL

  4. Beware of case sensitivity too. Change from sheet name of “Hello” to “hello” causes the error.

  5. Thanks Chris, this is really helpful.

    When the datasource is a folder, what is the M code to ignore all files missing “Sheet1” and continue extracting the data from files with “Sheet1”?

  6. Hey Chris, thanks for this, but what is wrong when in PBI desktop everything works fine with no data errors but the online dataset has the same error (the key didn’t match any rows)?

  7. I’ll just post the details to get the exact idea. I’m at a loss on how to fix this. It used to work fine but since a few weeks it’s giving me grief en this way I’m not allowed to refresh the dataset in Power BI Online.

    Er is iets fout gegaan
    Er is een fout opgetreden tijdens het verwerken van de gegevens in de gegevensset.
    Probeer het later opnieuw of neem contact met de ondersteuning. Geef de volgende gegevens op wanneer u contact opneemt met de ondersteuning.
    Fout in de gegevensbron: {“error”:{“code”:”DM_GWPipeline_Gateway_MashupDataAccessError”,”pbi.error”:{“code”:”DM_GWPipeline_Gateway_MashupDataAccessError”,”parameters”:{},”details”:[{“code”:”DM_ErrorDetailNameCode_UnderlyingErrorCode”,”detail”:{“type”:1,”value”:”-2147467259″}},{“code”:”DM_ErrorDetailNameCode_UnderlyingErrorMessage”,”detail”:{“type”:1,”value”:”The key didn’t match any rows in the table.”}},{“code”:”DM_ErrorDetailNameCode_UnderlyingHResult”,”detail”:{“type”:1,”value”:”-2147467259″}},{“code”:”Microsoft.Data.Mashup.ValueError.Key”,”detail”:{“type”:1,”value”:”[Schema = \”\”, Item = \”QryFinancieringPerJaar\”]”}},{“code”:”Microsoft.Data.Mashup.ValueError.Reason”,”detail”:{“type”:1,”value”:”Expression.Error”}},{“code”:”Microsoft.Data.Mashup.ValueError.Table”,”detail”:{“type”:1,”value”:”#table({\”Name\”, \”Data\”, \”Schema\”, \”Item\”, \”Kind\”}, {})”}}],”exceptionCulprit”:1}}} Table: QryFinancieringPerJaar.
    URI cluster: WABI-WEST-EUROPE-B-PRIMARY-redirect.analysis.windows.net
    Activiteits-id: 5b5fdbe2-4751-41b6-b408-c93eac351f44
    Aanvraag-id: cdadfd23-7c2a-f69f-1481-5782fa299b99
    Tijd: 2020-06-04 10:25:08Z

Leave a Reply

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

%d bloggers like this: