cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
qubit813
Post Partisan
Post Partisan

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

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.

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

View solution in original post

6 REPLIES 6
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

v-joesh-msft
Solution Sage
Solution Sage

Hi @qubit813 ,

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

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.

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

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.

Hi @qubit813 ,

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.

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

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors