An error occurred in the ‘transform file’ query. expression.error: the key didnt match any rows in the table.

Hello everyone, I'm trying to merge multiple excel files into one spreadsheet using Power Query, but I receive the following error when doing so: An error occurred in the ‘Transform File’ query. Expression.Error: The key didn't match any rows in the table. Details: Key= Item=Client Lists Kind=Sheet

Table=

Now a bit of information on my data sheets. The workbooks I'm trying to combine are tabled and also contain 2 tabs each. I'm trying to specifically combine 1 of the tabs from each sheet. In the Power Query navigator, it shows the options of 'Table' and the named tab I want to focus on. I tried to separate the 1 specific tab into one book and ensured all of my books have the exact same headers. When I try to combine I still receive the same error. Do I have to un-table my sheets? Or do I have to create a new book for the specific sheet I'm looking to combine? Please help!

Thanks

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

For comparison, here is my former anycodings_powerbi (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:

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

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:

test:

And here is my new (correct) code:

Transform Sample File:

test:

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.

I have noticed this problem when saving a workbook at work, then using a synched version of the same workbook at home and vice-versa (different computers). In my case my workbook contained several queries from sharepoint sources. All of them indicated the same error upon opening the workbook at a different location, even though the sharepoint url pointing to my data sources should have worked just fine.

I tried the Tony's solution offered here and that worked fine. But I felt having to open and refresh each of my queries one-by-one everyday was bound to become irritatingly cumbersome, especially since some of my workbooks pull 10 or more sources.

I found an alternate solution that worked for me.

1. Go to the DATA tab in Excel and click on the Connections icon.

2. Find the connected query(ies) that are having the error

3. Click on the Properties button to the right.

4. Under the first section called "Refresh control", check the box labled "Refresh data when opening the file"

You may have to experiment with how this affects your individual performance but for me it solved the error issue.

Page 2

I have a data set that has refreshed successfully in the past. It was built 3 days ago and refreshed fine for two days. Now I cannot refresh it either on the desktop version or from services. The error reads Expression.Error: The key didn't match any rows int he table. Other tables return the error message Load was cancelled by an error in loading a previous table. I tried to delete the problematic table from the data set with plans to re add it later. When I deleted the problem table, a second table threw the same error. I removed it too and a third then erred. It is a domino effect. All tables are failing one at a time. The tables, however, are fine. I can use them in other documents and in PowerPivot. any ideas?

A coworker updated her version and the gateway and is getting the same error. This was built in a group and resides in the group folder.

Page 3

Hi All,

I'm connecting to a data source on SQL server and faced the same "key didn't match" issue. tried all suggestions from community and almost anything from net, but none has worked for me.

After 7 hours of research and trial and error, I discovered something related to my credential on SQL server that was causing this issue. I'm not sure why the "db_owner" was unticked!(might be due to the patching updates on the server) I checked the box then refreshed the report and now it is working as normal.

In SQL management Studio, navigate to the Databases\[database name]\Security\Users\ and double click on the userID to open the "Database User" window, then select the "membership" from the left side of the window.

I am the dabase owner so the db_owner is ticked, if you are not an owner, then make sure that at least the "db_datareader" is checked.

Hoping it helps you to save heaps of troubleshooting time.

Regards,

Page 4

Just to add to the growing responses on this error.

I have resolved this by using Basic Authentication on the Gateway, rather than Windows. This despite desktop using Windows.

I'm not fully certain how making this change has worked, given some of my Impala datasources were able to refresh on the Server with Windows, but not others.

We re-entered the credentials to the Gateway on Windows authentication and it threw a connection error, upon changing it to Basic it connected and I can now refresh from all Databases the account has access to.

Hope this helps others.