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

One of the most common errors you’ll see when working with Power Query in Power BI or Excel is this:

Expression.Error: The key didn’t match any rows in the table

Source : Chris Webb’s BI Blog
Read more…

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)?

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.

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 didnt match any rows in the table?

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

How do I remove query errors?