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

Question

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.

Answer 1

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.