Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Chapin4u
Microsoft Employee
Microsoft Employee

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

54 REPLIES 54
Irvineloo
Regular Visitor

In case anyone who has the same scenarion that is trying to import data from folder with multiple files in it, try this way by defining a name that is identical across all the file and this is the key despite different file name or worksheet name. 

 

Irvineloo_0-1714125102978.png

 

All the best .. 

Could you go into more detail about this? Where sould I define this name? In every workbook that I am trying to merge or just in the summary file?

Sure .. please refer this sample screen. 

Irvineloo_0-1718093047062.png

Assuming you have many files in the same folder and extract data from this folder, each of the file whether it is csv or xls format, having all the record selected (Ctrl+A) and define the name (MasterSheet in my case) so import process able to match to consolidate all data. Hope this works for you .. 

Hi! Thanks for your reply. My problem is that I need to merge 100+ excel files (with 1 sheet each) into one, but all have different Sheet names therefore Get Data from Folder cannot find the key.

 

Following your advice should I open every file and define the name? I could rename Sheet name in all files and everything should work fine, but I don't want to open 100+ files. Maybe I don't understand well.

 

Perhaps you open few files to manually change to be sure that is issue that ? if you found out that is really one that unable to merge, you may now consider using macros to open these 100+ files to define a name for range of rows and columns. My folder have less than 10 files so i can open to change manually but in your case .. 

Anonymous
Not applicable

A user of mine was having this issue. He was connecting to a database though. I gave him Select permission on the database and he was no longer receivinmg this error after refreshing the query.

Devtr
Helper III
Helper III

It's too late. But this might be useful for someone.

When using an Excel spreadsheet and a table connected to PQ, sometimes that table name gets changed unknowingly. Especially when you copy-paste inside the table. Another scenario is the sheet name gets changed too.

I have found this happens.

Thanks.

DDDDD
Frequent Visitor

Not actually an error, odd Power BI behaviour imo.

I had this message for the first time today, on a scheduled report which has been running for months. It uses combine files, but there's been no change to the file location or structure.

 

For me this error message disappeared whilst I was testing the sample file step, I added steps afterwards to expand the table view and the error on the earlier step vanished. I removed the added step and refreshed the report and it was fine. Bizarre.

Hi, 

 

Are you able to share what steps you added? I believe I have a similar issue. The structure of my data hasn't changed and the tab name remains the same. But everytime a new file is added to the folder (Everyday) the refresh breaks due to this error. It seems to be trying to add a blank row to the dataset in each time

rendorHaevyn
New Member

The issue for me was that across 50+ workbooks, the first sheet was not always named the same.  The soultion was to dynamically pick up the first sheet on each workbook, rather than taking the hard coded variable from the sample combine effort.


> Advanced editor on the Transofrm File 

rendorHaevyn_0-1661496686295.png

> Comment out the "data_sheet" line, and add the "FirstSheet" line.  Bingo.

let
    Source = (Parameter1 as binary) => let
        Source = Excel.Workbook(Parameter1, null, true),
        FirstSheet = Table.SelectRows(Source, each [Kind] = "Sheet"){0}[Data],
        // Data_Sheet = Source{[Item="Data",Kind="Sheet"]}[Data],
        #"Promoted Headers" = Table.PromoteHeaders(FirstSheet, [PromoteAllScalars=true])
    in
        #"Promoted Headers"
in
    Source

 

Pavelfab
New Member

Anonymous
Not applicable

- 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

Picture2.png

Picture3.png

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

Joseph_Dsilva
New Member

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

George_3029
Frequent Visitor

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.

 

 

Anonymous
Not applicable

I had the same problem today.

 

This is how I fixed it:

Make sure to go under advanced editor on Power BI

matheusvieira_0-1642099778787.png

 

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

matheusvieira_1-1642100080298.png

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

matheusvieira_2-1642100205682.png

 

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

 

 

IvanKo
Frequent Visitor

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, 

This one worked for me, thank you!

Anonymous
Not applicable

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

Anonymous
Not applicable

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.