cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Chapin4u
Microsoft
Microsoft

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

2017-04-07_15-19-41.png

 

Hi, 

I need help to understand this error message (above image) and how to fix it, thanks 

1 ACCEPTED SOLUTION

 A typical case would be when you connect to an Excel file and then your Query attemps to access a Sheet that does not exist in the Spreadsheet...

View solution in original post

30 REPLIES 30
IvanKo
New Member

I started having this error after populating the file path with daily workbooks where the name of the sheet wasn't the same as my query / Transform Sample File 

(My power query reads from many workbooks wich are the same except for the name of the sheet which is named after each workbook)

The query was attemping to find in all the workbooks the same sheet name as in my data transformation example.

 

I tried to sorted it out by removing the Navigation stept in the query but did not work.

 

How did it work? >In the Transform Sample File, I opened the Advanced Editor and changed the name of the Sheet to zero

 

It was>

 

Source = Excel.Workbook(Parameter1,null,true)

#"03_21_4_20_Sheet"=Source {[Item="03__21_4_20",Kind="Sheet"]}[Data],

 

I changed it to >

 

Source = Excel.Workbook(Parameter1,null,true)

#"03_21_4_20_Sheet"=Source {0}[Data],

 

Regards, 

johnelmasry
Frequent Visitor

If you are combining Excel Tables: All tables' names should be the same

If you are combining Excel Sheets: All sheets' names should be the same

 

As simple as that

 

Thanks.

 

John

denisdementyev
Frequent Visitor

In my case this error occured when one of the sheets was called differently

russellclaude
Frequent Visitor

I had this error today, and the solution was..... close the database - and all Access databases- that linked to my source file(s). SHAZZAMM! Fixed.

jingyulong
Frequent Visitor

1. First locate the error and find the problematic step,For me the error occurs in "Invoke Custom Function" step, and error is :"An error occurred in the ‘Transform File’ query. Expression.Error: The key didn't match any rows in the table."

 

2. Find the "Transform File" Function 

= (Parameter1) => let
        Source = Excel.Workbook(Parameter1, null, true),
        Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
        #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true])
    in
        #"Promoted Headers"

This code is created according to the excel of the example, prompting other workbooks in our folder, should also be named “sheet1”

 

3.So, just rename all workbooks under the folder sheet's name to "sheet1", this problem can be solved.

d_a_n_c_
Helper II
Helper II

Hi,

one more thing that may resolve your problem. Close view in SQL you are trying to pull data from and give Power Bi a try.

 

dan

Anonymous
Not applicable

Thanks, resolved my issue.

heberpower
Frequent Visitor

For me this error was caused by bad login credentials.

rossparker
New Member

I cannot get past this error.  It is when trying to update a Data Source for File Folder of CSV files.

First thought : i believe that Folder access works only for Excel files (xlsx not CSV extension)

If you send a link / Pbix i can have a look

Changing them to XLSX did not work either  😞

 

I just redid the query from the folder to take care of it.

Chris99
Resolver I
Resolver I

@altomost - Thank you very much, that was exactly what I did and removing the Navigation fixed it.

 

It is a shame you can't import views from SQL Server, instead you have to import a table and then edit the query to act like a view. That was when I hit this error.

 

Chris

yeszone
Advocate I
Advocate I

If you are using Tables/Views from SQL Server, check whether the power bi credentials have the permission to read from the table/view you have linked.

1. Go to "Options and settings" -> "Data source settings" -> "Edit Permissions" 

2. Capture.PNG

3. Cross check in the database whether this user role has the permission to access the table/view.

4. Go to SQL server

5. Right click on the table/view. Click "Poperties" ->Go to "Permissions".

6. Capture2.PNG

7. Add the user role/ give permission accordingly.

8. Go to Power BI and refresh.

MansoorSadat
Regular Visitor

I had a report connected to a folder and every month used to add new excel files in that folder to update the report. This happened when a new TAB in my new excel workbook had a different name than the old files connected to the report. So I just renamed the new tab in the new excel workbook to match the old tab in the old file excel files and it worked.

 

Hope it helps

Mansoor

Faced a similar problem where the sheet name changed and had to go and rename it but I'd like to have a solution that this is done automatically and not manually as my files are daily and i am not the one producing the source file which means that if not automated every day i need to open the file, rename the sheet, save and then update the query / report.

 

Any ideas on how to automate the process of a repeating incorrect sheet name to match the sheet name of the old series of source files ie...let's assume that all the daily files from Jan 1, 2018 till 30 May 2018 have a sheet named "Country (XYZ)" and from 31 May and onwards this same sheet is named "Country XYZ".

 

Thx in advance for your help!

@lemontree 

Just shooting from the hip, what I would do is to create a VBA script or similar, that opens each file and renames the sheets according to standard, you can use regular expressions for that e.g.

Dim Regex As New RegExp

Dim NewName As String, OldName As String

Regex.Pattern = "[\(\)]"
NewName = Regex.RE(OldName, "")

 

will remove all parantheses from name

hangtenboy
Regular Visitor

As an addition to the solution JMDH has already submitted, I have found the need, at times, to go into the Advanced Editor and change the name for the sheet to whatever it is in the file. So you changed the Sheet name to reflect what it used to be in the original but you still get the error? Check here:2018-05-24_9-08-45.jpg

mjc099
Regular Visitor

 ensure all tabs in worksheet are named EXACTLY as in previous updates. 

 

I received the same message when updated a datasource after three months of successfully updating same.  Expression Error: The key didn't match any rows in the table.

 

My data sourse is a monthly updated xlsx file.  My issue was resolved by ensuring all tabs in worksheet were named EXACTLY as in previous updates.   One of the tabs in the data had an underscore added.  the underscore did not exisit in previous updates. updated the spreadsheet tab resolved the keky didnt match message.

Anonymous
Not applicable

Thanks @mjc099 . I had the same problem when adding a new file to a folder data source. I changed the sheet/tab name in the new file to match sheet/tab name in the original spreadsheet file used to create data model, and the error cleared.

 

It would be great to have this error fixed in the future and purhaps use indexes to call for specific sheets in the future. As in my case, some automated repors saved in one folder will not have the same sheet names. For example, the reports saved in my folder will have sheet names tied to the date range they were generated for, hence the names will be different in each report.

Hi

In order to avoid the error ralated to the sheet name chnaging unexpectedly, i use the { recordnumber} notation instead , to access the Binary of the Excel File and later the Sheet. Here i access the first file ie {0} of my Sharepoint folder , then get its content and then take its first {0} sheet

 #"_FileContent" = try #"Filtered Rows"{0}[Content] otherwise error "Pas possible d'accéder aux données du fichier: " & _FileName,
#"Imported Excel" = Excel.Workbook(#"_FileContent"),
Feuil1_Sheet = #"Imported Excel"{0}[Data]

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.