Expression error the key didn\t match any rows in the table

Attempting to pull data into Excel from a UDT.

Raw data will pull, but,

when I try to edit Query, receive "Expression.Error: The key didn't match any rows in the table." in Power Query.

See screen shot : 

Any Thoughts?

I have used "syzcon" running the Data Dictionary utility ("DD") to set table keys.

I need to pull specific data in a specific order into Excel.

• When I add a Power Query from a Premium SQL Database it inserts the language of:

  = Sql.Databases("MACHINE_NAME\SQLEXPRESS") = MAS_CNX{[Schema="dbo",Item="CI_UDT_CUSTOMERS"]}[Data]

  for a pre-existing CI UDT for company code "CNX".

• Many thanks.  I could not replicate your setting, but it lead me to check my SQL server credentials which opened up functionality.

bearcub

Well-known Member

JoinedMay 18, 2005Messages699 Office Version

1. 365
2. 2013
3. 2010
4. 2007

Platform

1. Windows

• #1

I was trying to load a couple of files into PowerQuery today and I keep on getting this error message (Expression Error: The key didn't match any rows in the Table).

Do you know what the possible causes are and how I can fix it.

I even tried downloading it without making any adjustments and got the same error message.

Thank you for your help in advance,

Michael

I have tens of Excel files with similar table (means: same name, same headers, different content). I'd like to merge all data from these tables to one big table for future processing.

I did it many times before using these steps (I have non-English version, so strings could be slightly different):

1. Power Query > From file (in Load external data Section) > From folder
2. In dialog window with list of Excel files in folder Combine and load
3. In Window Combine files select the wanted table name.

Now I usually get one big merged table. But for one set I get [Expression.Error]: The key did not match any rows in the table

In Query editor window there is a few files loaded OK and then it ends on whole row filled with Error.

This could be easily walked around by using checkbox "Skip files with errors" in step 3, but I'd like to know what file(s) are causing this error(s).

How to find it (them)?

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.

How do you fix the key didn't match any rows in the table?

How do you resolve Expression error in Power Query?

What is Expression error in Excel?

How do you fix Expression error we couldn't find an Excel table named?