Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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
Solved! Go to 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
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.
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
Hi @Anonymous ,
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
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
Hi @Anonymous
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
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
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.