Error message - loading data from folder

[Syndicate_Admin]

Hi

I am trying to create a new dataset with data from excel documents saved on my local computer. All three excels are in the same folder are all the same format .xlsx and have the same headings, and no discernable errors based on review. Each excel contains between 600 and 1000 records each.

When I try to get data, I use the 'folder" option and navigate to the relevant folder. I choose combine and load. After a short load time, I get the following error message:

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..
'.

If I go to power query, I can see that the data from the first file has loaded, but when I get to the row number where the second file should be there is an error that says:

An error occurred in the ‘Transform File’ query. Expression.Error: The key didn't match any rows in the table.
Details:
Key=
Item=dim_items_Aug
Kind=Sheet
Table=[Table]

I have tried the following basic remediation:

• I have reviewed all the excel files to look for blank rows - none exist
• I have reviewed all of the excel files to find any strange/different entries - all looks fine
• I have manually entered each excel separately and then appended, to see if any error message would come - no error messages and all the data looks fine (But I don't want to use this option as I will have to add each file manually in future months)
• I am using Power Bi desktop latest October release

Please see below screen shots from load process and Power query:

This is the first time I am trying to load from folder, so its entirely possible I'm making a basic error. I've googled and researched but haven't come close to identifying the source of the problem.

Appreciate any assistance 🙂

Julian

[Syndicate_Admin]

Hi @enablejulian ,
We may need to get @ImkeF  on the job...the superwoman of M.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Syndicate_Admin]

Hi @enablejulian ,

please check the Excel files structure.

By the look of the error, one of the files does not contain the tab called "dim_items_Aug".

To combine files without some manual tweaks to the code generated by PBI they need to have an identical structure.

Kind regards,

JB

[Syndicate_Admin]

Hi @jborro ,

The name dim_items_Aug is actually the name of one of the files. The other files are named dim_items_july and dim_items_sept.

I assume that the purpose of importing from folder is that I can import excels that have identical structure, the only difference being the file name (?)

Thanks again

Julian

[Syndicate_Admin · Accepted solution]

Hi @enablejulian ,

From what I can see apart from being a different name, they also have a slightly different structure.

This is the step where you most likely have this error (you should have something similar in the template created by PBI):

Table1_Table = Source{[Item="Sheet1",Kind="Sheet"]}[Data]

This works Ok, so I changed it to:

Source{[Item="Sheet12",Kind="Sheet"]}[Data]

This is the error that I get:

Expression.Error: The key didn't match any rows in the table.
Details:
    Key=
        Item=Sheet12
        Kind=Sheet
    Table=[Table]

Looks familiar? 😁

Somewhere in the Query list, you should have something like this. This is the bits that are created by PBI automatically when you do folder import:

On the Sample File try changing the step that is usually called Navigation  and looks like this:

Roll the number in {} from 0 to 2 and check what is happening with the Transform Sample File table, which is the template for the function that PBI generates to import files. If it brakes, you know will find causes it. 

If you have to have different names for tabs in different files (e.g. for csv it is usually defaulting to the file name), let me know there is a way to tweak the code to adapt for it.

Kind regards,

JB

[Syndicate_Admin]

Hi @jborro 

Thankyou! I think I have resolved.

I followed your instructions and realised that whilst the data is consistent in each of my excel files, the name of the sheet (tab) is different. Aug file has sheet named dim_items_aug and July has sheet named dim_items_july etc etc

I've changed each of the sheets to be the same name (e.g. dim items) and I am now able to load 🙂

Thanks for your assistance - I knew it would be something so simple!

Thanks again

Julian

[Syndicate_Admin]

Hi, sorry for writing here after four years, but I think this can help another person like me. Today, I had the same issue, and I solved it when I realized that my Excel files had exactly the same column structure, but... the sheets did not have the same names. So I edited my files and wrote the same sheet name for both files, opened Power Query again, and now I can combine the files without any problems.

I hope this will be helpful for someone.