cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate I
Advocate I

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

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.

Highlighted
Resolver I
Resolver I

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

@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

Highlighted
Anonymous
Not applicable

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

Those steps really fix it, thanks

Highlighted
New Member

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

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

Highlighted
Advocate III
Advocate III

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

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

Highlighted
New Member

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

Changing them to XLSX did not work either  😞

 

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

Highlighted
Frequent Visitor

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

For me this error was caused by bad login credentials.

Highlighted
Helper II
Helper II

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

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

Highlighted
Frequent Visitor

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

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.

Highlighted
Frequent Visitor

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

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.

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors