CODEWITHSUNDEEP
excelpowerbi
skyline01
skyline01

Reputation: 2101

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:

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.

Upvotes: 1

Views: 78541

Answers (4)

OLGJ
OLGJ

Reputation: 432

I had the exact same problem. My data consisted of Excel documents with four sheets. Performing an identical procedure to files that were larger (250k rows +) I ran into the same issue, but not when it was approx half the size. Turns out the solution was to remove the sheets that wasn't used solved the problem.

Upvotes: 0

Dan Aurit
Dan Aurit

Reputation: 11

I had a similar error... for mine, I simply had to rename the sheet in excel... that was what caused my error. 9 Files, 8 of them had the same name for their respective sheet(s), the 9th was the one throwing the error... come to find out, it was as simple as naming the sheet on the 9th file, the same as the others... bada bing, bada bam.

Upvotes: 1

DataUnitedLtd
DataUnitedLtd

Reputation: 31

I had exactly the same error simply because the PowerBI VNET Gateway could not authenticate to the source of the data to refresh dataset hosted in PowerBI premium capacity workspace. Totally unexpected and confusing, but once the correct credentials were set for the Gateway configuration - everything worked fine and the error had gone away.

Upvotes: 0

skyline01
skyline01

Reputation: 2101

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.

Upvotes: 5

Related Questions