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?
I know this is an old post, but i am facing the same error now, i am doing a union between two tables in DAX for two tables, that are extracted from two separate on premise Databases. As you had described everything is working correctly in power bi desktop, however once the report is published online, i am getting the error although i am able to access the two tables seperately.
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!
Hello Chris Webb,
Suddenly, I am facing one error in semantic model-
Expression.Error: The key didn’t match any rows in the table.. Key = [workspaceId = “XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX”]. Table = #table({“workspaceId”, “workspaceType”, “Data”, “workspaceName”, “Tags”, “ItemKind”, “ItemName”, “IsLeaf”}, {}). ;The key didn’t match any rows in the table.. The exception was raised by the IDbCommand interface. Table: Date.
Are you connecting to a dataflow here?
i had the same issue but none of the above worked. The issue was the file extension change from xlsx to xlsb. Just open the file and save as solved the error
heres a further iteration of this problem occurring in the wild where we also got this error message happening. We have excel files saved on sharepoint site. This random style of error happened in 2 different Power BI desktop files connected to these excel tables saved on sharepoint. The BI file was setup to import these calendar related tables , into the bi file to work as its calendar table.
For some weird reason, Power BI whenever it opens these 2 files now , it doesnt read the files as excel tables anymore but now treats them as excel ranges. When this happened the queries just stopped working because the query specifically says the datasource is table not a range. No matter how many ways I tried, the power bi desktop file would not recognize these saved tables as tables anymore and only way to get the query re-working was to let it do what it wanted and start viewing the excel tables as ranges, which meant rewriting the query to promote the header row of the table etc etc.
After googling for hours on the issue, other than this helpful reference, I could find nothing. Its weird crap like this with microsoft that really drives me nuts at times. I cant get the error to repeat but in other files. If I just create a normal excel file with a table and import it to power bi desktop , Power BI just reverted to its normal and correct behavior of recognizing said table as a table and the query was created accordingly. There must be something weird that has happened in these 2 excel files that somewhere when power bi desktop reads in tables it for some odd reason wants to see named ranges where there are actual tables. Other than manually rewriting all the queries ( i cant go back to a prior version of the workbook as its a huge workbook with a lot transactional history and I am not reentering all the data from weeks again) this was the only way to get rid of the bug and the dreaded error message.
Hi Chris
As a seasoned excel guru , I have an unusual variant of this problem that I am wondering if you have ever seen this in the wild ? I am totally stumped what to try and do next ? My email is paulf@stoneaxepastoral.com.au, if you have any clues what might be triggering my error , would be appreciated. I think I need to lodge a help request with microsoft itself.
We work in a sharepoint environment where excel files are all saved to sharepoint locations. I have set up a number of excel models using power query where I am pulling data from other excel spreadsheets also saved in this sharepoint location. Note I always change the data sources so that they point at the web version of the spreadsheet not the local one drive copy.
The error message, “The key didn’t match any rows in the table” keeps occurring when someone else other then me was in the file, made some small changes to data ( but didnt rename or delete anything). They hop out, I come along, hop in and refresh the model and all of a sudden certain tables used as data sources that reside in these other spreadsheets, stop being seen by power query as excel tables but all of a sudden power query can only read them as “ranges”. Hence the query just breaks down.
If I try and recreate the query , and reimport the table, excel automatically defaults to seeing the table as a range and then imports it like its a range, header row comes in as a range and then you need to promote headers etc ( power query does that automatically if you step into the transform window which I always do ). Excel literally no longer sees a table sitting there but only a sheet with the data on it. Very weird.
The way we have always fixed the problem is to restore the file back in time, I then go back in and file works ok for few days, until someone else goes back in and does some work, like adding new data but not changing names of tables or anything , leaves and I go back in and same issue is re-triggered with the query breaking down as same error message re-occuring and again all of a sudden excel cant read the external tables as tables.
I dont know if the problem is sharepoint, power query or one drive but the behaviour is quite bizarre. Spent bit of time googling to see if anyone else has encountered this behaviour but other then discussion here of this error and some discussion on Matt Allington’s site of sharepoint being a unreliable place to put data sources, I have not gotten a lot of clues as to what is causing my grief ?
Any thoughts or ideas you might have would be very appreciated.
I had this issue and it was particularly weird because none of the queries appeared to be broken (i.e. no yellow error triangles or messages). The error message only appeared when trying to load the query. It happened in a “Get data from Folder” scenario.
In the end I found that someone had inadvertently added a folder into the queried folder which contained data that was in a different format.
Just wanted to add this here in case it helps someone!