Jun 13 2021 07:37 PM
Jun 13 2021 07:37 PM
I'm trying to merge multiple excel files into one spreadsheet using Power Query, but I receive the following error when doing so:
An error occurred in the ‘Transform File’ query. Expression.Error: The key didn't match any rows in the table.
Jun 14 2021 01:40 AM
I guess you use From Folder connector. For it both files shall have the same structure. If you select 'Client Lists' sheet for sample (first) file, sheet with the same name shall be in another file. If you combine based on structured tables, they shall have the same name in all files.
Jun 21 2021 02:17 PM
The column data types on both table must be the same.
What kind of join did you select in your merge query?
Jun 21 2021 03:08 PM
Jun 21 2021 03:29 PM
Aug 03 2021 10:22 AM
Aug 27 2021 04:36 AM
@PGonzaga You are a genius. Simple and fast solution. Thanks you mate.
Sep 14 2021 06:41 PM
@Ozz_Kozz I built couple of PQs, one for Master and other for comparison. Now when I merged queries for vlookup, I'm getting the same error and that's what I expected as there were no matching records. I want to go to next step and work on those records, but PQ doesn't allow me to Close & Load due to this. Does anyone overcome this or what's the work around?
May 17 2022 01:25 PM
I found this video on Ewetoob, on how to fix this. Very easy.
Basically, the worksheets within each book have different names, PQ is looking for the same name. You can have it look by an index number instead, probably 0 if there's only one sheet in each workbook.
Open the editor. Select "Transform Sample File" in the Queries Pane (left side).
Select "Navigation" in the Applied Steps pane (right side).
Change the formula to
That should fix it!
Jun 03 2022 04:08 PM
The simplest solution to a common problem experienced by most people the first time they try to use Power Query to merge Excel documents.
Just make the name of the worksheet the same in each file containing data. That is all.
How many pages full of incomprehensible and complicated answers that were impossible to implement did I have to get through just to find this simple answer? Lots.
Jun 03 2022 04:15 PM
Jan 04 2023 05:23 AM
Thank you!!! I too was struggling for hours. :)
Mar 04 2023 05:13 AM
I have the same error, However Im simply trying to load and save the power query from a single excel file, having only one worksheet. In fact it is from one of the Tutorials and sample files from Kevin Stratvert. All simple tutorial steps followed. single file, single works sheet "Order Data". No merge step required. However same error. Is there a bug with using the Folder Source connection? Thanks for any reply.
Mar 09 2023 04:08 AM
Do you use From Folder connector? Are there any other files in this folder? On which step do you have an error?
Apr 19 2023 12:55 PM