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
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:
On Sheet1 there’s some data you want to load into Power BI or Excel using Power Query:
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:
…click OK and your query is ready:
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:
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”?
The result is that the first step of the query now returns a table that looks like this:
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:
There are two ways to fix the error:
- Go to the Excel workbook and change the name of the sheet called “Hello” back to “Sheet1” or
- 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:
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.
@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
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
Hi Chris,
I want to thank you because you saved me a lot of trouble and time.
Just a beginner in power query and already run into the error: ““The key didn’t match any rows in the table”.
In my case I got the error because my data source was first in the excel format “.xlsx” (office 365), but later I got the same data in the format “.xls” (Excel 97-2007).
First, I had to change the file name in the source step to “.xls” of course.
But then, I ran into the next error “The key didn’t match any rows in the table”. So, I checked the sheet name and all the column names but nothing was wrong with that (the names remain the same).
Then I decided to follow your advice and adjusted the navigation step into
dump_Sheet = Source{[Name=”dump”]}[Data], and wallah.. it worked.
Beware of case sensitivity too. Change from sheet name of “Hello” to “hello” causes the error.
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”?
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)?
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
I have the same issue. In my case the source step is refference to other query. I changed this source to hit third query. In PBI desktop works fine – the model is regpfreshed without any error,but in the service I get this error.
I too face the same issue what is the solution please?
Chris, why doesn’t PowerBI/Analysis services display the actual key (=table name) that isn’t found?
The error message is unhelpful because (apart from needing useful articles like this even to interpret) you still have to deduce which table is the miscreant – and there might be dozens in the data source. It seems such a simple thing to reveal the value of the key.
It does though, in the error message…?
I see now, yes it does for Excel, that’s good.
That is specific for Excel, though. For an ODBC connection the exception in its entirety (that I’ve been able to extract) is this, with no indication of the key:
“Failed to save modifications to the server. Error returned: ‘OLE DB or ODBC error: [Expression.Error] The key didn’t match any rows in the table..”
I’ve wondered why it ends in two dots – perhaps there was meant to be further info between them.
This error can also be experienced by using the Nz function in an Access query that the Excel Power Query is referencing.
Excel has always had trouble utilising embedded functions used within Access SQL queries, even when Access has no problem showing the results.
Unfortunately this is another example where it won’t give you a good indication of the offending reason without a lot of trial and error!
This is similar to my case.
I’m experiencing this error when refreshing Power BI report. My external data source is Access query. The query is using linked excel sheet. When running this query from the Access level it always works, but when I do this on Power BI it’s completely random. First try usually resulting in “The key didn’t match any rows in the table” error but after another 2 – 3 tries it eventually manages to complete the refresh.
Any clues why this is happening?
That is odd it’s intermittent.
Can you try renaming the query, then changing the query to a make table query, run it so that it produces a table with the same name as the original query and then see what happens when you refresh excel again? ie it’s pulling from a static table composed of the same data as it would have pulled in from the query.
That then establishes whether its a problem with the sql or something in the data such as an untranslated character such as a carriage return which is stored as ascii but invisible to the naked eye.
If it works with the data as a table, (and you don’t fancy running it from a static table all the time?) delete the table and rename the query back to what it was before. Then check thru it to see if there are any access functions you are using in it (or in any nested sub-queries you might be using) these could be things like nz, iif, choose, first, last, or, switch. I know that nz always causes me problems, maybe one of those is what’s causing you problems?
Hi Kris,
It’s odd that it’s intermittent.
Maybe try renaming your query, then running it as a make table to output the data as a table with the same name as the original query and then refresh the excel again.
This will have the effect of excel pulling from what it thinks is the same source, but this time it’s from a static table. This will establish whether the issue is with the SQL or something in the data such as a hidden character like a carriage return which are stored as ASCII characters but invisible to the naked eye.
If the table does refresh ok in Excel then it must be an issue with something in the SQL of the query or a subquery if you are using them. Rename the query back to what it was before and then check thru the query (and any nested sub-queries) for a function such as Nz, Iif, Switch etc…. one of these might be the offender.
https://support.microsoft.com/en-us/office/access-functions-by-category-b8b136c3-2716-4d39-94a2-658ce330ed83
Good luck!
Really enjoyed your article as its highly informative
many thanks!!
Thanks, your post helped me a lot!
I have a different situation. I want to extract data from various property reports. I have used a formula to filter based on the heading of the table in pdf i want to extract- = Table.AddColumn(Source, “Custom”, each List.Contains(List.Combine(Table.ToRows([Data])), “PCA Consultant (Firm)”)
The problem i am facing is in the next step the table number is contant, however in the pdf files the data is dynamic so table number keep changing for every report. It will be very hepful if you can help me fix this error.
Manually if i go and just delete the last septs and and re run its working but the sheet needs to be used by those whose understanding on excel is not great.
Hi, I followed what you mention here (Regarding that in different sheet names, I have changed their names and download again), but error appeared again(((
Puse el mismo nombre a todas las hojas de los diferentes libros y funcionó. Gracias.
I had to tick a checkbox called ‘Skip files’ that have errors; and then I no longer had the message about the key error. So spreadsheet name was not obviously the problem!
My error still persists, I’m using a source as an import in Google big query, I even separated each table in a pbix file to update, but the error happens in all files, I don’t know what to do.
Took me a while but the above solutions really helped and prevented me from starting my dashboard over from scratch! Thank you for the help! Much appreciated. For me I had 2 table names from SAP that changed. Once I showed the technical names in PowerBI I was able to go into the Advanced Editor Code and update 2 table names and solved.
Thank you. I have a similar task where i am trying to connect to multiple Tables with the same structure and header name into one query. How an I do that? Any suggestion?
Thank you in advance.
Thank you very much, This was really helpful.
Hi Chris,
I have the same issue but I think for different reason. My data source is in Access Microsoft. I import 2 tables. It was working fine for more than a year but it’s giving this error for one of the tables. The other table is working fine. I wonder what possible reason is causing this error?
Did you change a table name?
In an earlier comment I mentioned that this issue could be caused by…
“something in the data such as an untranslated character such as a carriage return which is stored as ascii but invisible to the naked eye.”
Is there any way you can remove the contents of the table, with the exception of a sample set of data eg the first row of data and try importing again to establish if it is tye data that is causing the issue?
Chris, thank you for this post. I have a very unique scenario and I’m not sure if the problem is solvable in Power BI but there are several automations and an RPA already tied to the current setup of my files, so I really can’t make the change upstream. Essentially, each Excel file has two sheets. The first sheet (the only one I want to keep) is named the same as the file, so for each file, the sheet name is different. Any thoughts? Thank you!