how to resolve Power BI error - the key didn't match any rows in the table

[Anonymous]

I am trying to load (combine) multiple Excel files into Power BI (October 2019 version). Every file has only 1 sheet. Each sheet has 1 range, and each range has the same schema across all files. (The sheet names are different, though.) A sample sheet name is '200704'.

Here are my steps:

1. Get Data \ Folder \ Connect
2. specify the Folder path
3. Combine & Load
4. select one of the files as my sample file; click on the file name as my Parameter1; click OK

After I click OK, the cursor spins for a bit, and then it stops. Nothing happens. So, I go to Edit Queries \ Edit Queries. There is a warning symbol on my data query that reads:

An error occurred in the 'Transform File' query. Expression.Error: The key didn't match any rows in the table.

Details: Key = Item=200704 Kind=Sheet Table=[Table]

How do I resolve this error?

If it helps, Power BI generate 5 queries for me, and the structure is:

• Transform File from data [2]
• Helper Queries [3]
• Parameter1 (Sample File)
• Sample File
• Transform File
• Transform Sample File
• Other Queries [1]
• data

Interestingly, if it helps to diagnose the issue, if I set sample file = First file or if I set sample file to my first file manually, the following error is thrown in the dialog, but it doesn't show what query is in error when I try to view / edit the query.

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..'.

And, to be sure, when I attempt to load this file (or any file in the folder, for that matter) individually (via Excel connection), it loads successfully. So, something must be wrong with the M code in my Folder connection.

[Krypton]

I received the same error. In the process of updating my data set in PBI - I noticed the sheet was not labeled correctly. I made an adjustment and re-labeled the sheet and it resolved the issue. 

[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

[FloridaMan55051]

I resolved this by inspecting the sheet names. The users dropped a new file with the wrong sheet name. There's probably a dynamic way to ignore the sheet name in power query, but we haven't employed that yet.

[v-joesh-msft]

Hi @Anonymous ,

The following methods may be helpful:

1. Ensure the columns in the files have the exact same name, order, data format, and numbers as the sample file, please note that the power query is case sensitive.

2. Sometimes error occurs when the file Power Query is trying to get data from is open. All files must be closed for Power Query to access them.

Here are two posts similar to yours, you can look at it:

https://community.powerbi.com/t5/Desktop/Expression-Error-The-key-didn-t-match-any-rows-in-the-table/td-p/186522

https://forum.enterprisedna.co/t/error-the-key-didnt-match-any-rows-in-the-table/289/5

Best Regards,Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[carlosam]

at point#2, I have source from folderfiles, this folder are sync with sharepoint, all my colleagues use sync and have folder sync, due to this they almost always have their excel file open, working or not, but it is open, sometimes I have an error when refreshing the queries, one of the possible causes may be which are the open files? Thanks

[Anonymous · Accepted solution]

None of those suggestions / links helped.  However, I figured out the cause of my problem and the solution.  The issue is that the row in my template query was being referenced incorrectly (i.e., the primary key between the template query and the regular query is wrong, and it has hard-coding of sheet names).  To fix that, I had to remove all other columns in the template query table except the Data column, as described here.  (It's odd that no MS documentation on combining multiple Excel files discusses this very important step.)

For comparison, here is my former (incorrect) M code:

Transform Sample File:

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

test:

let
    Source = Folder.Files("C:\some folder path"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"ID", type text}, {"Name", type text}})
in
    #"Changed Type"

And here is my new (correct) code:

Transform Sample File:

let
    Source = Excel.Workbook(Parameter1, null, true),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Name", "Item", "Kind", "Hidden"}),
    Data = #"Removed Columns"{0}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Data, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", type text}, {"Name", type text}})
in
    #"Changed Type"

test:

let
    Source = Folder.Files("C:\some folder path"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File")))
in
    #"Expanded Table Column1"

Notice the 'Removed Columns' step in the new template query.  This is the "secret sauce" to the key problem.  Also notice that I kept all default steps after my 'Data' step (i.e., 'Promoted Headers' and 'Changed Type') in my template query.  This is because all of my sheets have the same schema.  If this weren't true, then I would need to move those steps to the regular query.

[v-joesh-msft]

Hi @Anonymous ,

It's pleasant that your problem has been solved, could you please mark the reply as Answered? that way, other community members will easily find the solution when they get the same issue.

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

This solution is required when you have different sheet names across the workbooks.  Otherwise, a simple 'Combine & Load' is sufficient.