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

[Chapin4u]

Hi, 

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

[Gianghoang]

- Click duplicate on source on the right ( Applied step)

- It show a table--> You can see at the Hiddens column--> It's "False"

- Click on "Table" on Data column.

- It's show a msg box (Pic 2) --> Choose continuos 

It's solved

[DenisDee]

Thank you, this was my solution. But I needed to re-create all steps afterwards. In my case it was easy...

[Joseph_Dsilva]

I got this error, when I changed the table name in PowerBI Desktop (Fields category) 

[George_3029]

I had the same error message connecting to a tabular model via visual studio.

When I validated the query, "The query statement is valid."

Going into the "Design" view is when I got the error.

I fixed it from the "Query" menu, selected refresh query, and it worked.

[matheusvieira]

I had the same problem today.

This is how I fixed it:

Make sure to go under advanced editor on Power BI

I conected my excel workbook as tables inside Power BI, so in this case you need to make sure that the file location, file name and most important, table name is correct.

______________

let
Source = SharePoint.Files("FILE LOCATION", [ApiVersion = 15]),
#"FILE NAME xlsx_FILE LOCATION" = Source{[Name="FILE NAME.xlsx",#"Folder Path"="FILE LOCATION"]}[Content],
#"Imported Excel Workbook" = Excel.Workbook(#"FILE NAME xlsx_FILE LOCATION"),
Table = #"Imported Excel Workbook"{[Item="TABLE NAME",Kind="Table"]}[Data]
in
Table

_______________

How do you change a table name in excel?

Go under Formulas Tab > Name Manager

Select the table you want and click edit. This name should be the one you have to insert under Power BI conection above

Make sure to do this to every imported table on you model

[IvanKo]

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, 

[Anonymous]

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]

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

[russellclaude]

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]

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_]

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]

Thanks, resolved my issue.

[heberpower]

For me this error was caused by bad login credentials.

[rossparker]

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

[jmdh]

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

[rossparker]

Changing them to XLSX did not work either  😞

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

[Chris99]

@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]

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. 

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. 

7. Add the user role/ give permission accordingly.

8. Go to Power BI and refresh.

[MansoorSadat]

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

[lemontree]

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!