Power Query error 'The key didn't match any rows in the table.' when combining

%3CLINGO-SUB%20id%3D%22lingo-sub-2443417%22%20slang%3D%22en-US%22%3EPower%20Query%20error%20'The%20key%20didn't%20match%20any%20rows%20in%20the%20table.'%20when%20combining%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2443417%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone%2C%3CBR%20%2F%3EI'm%20trying%20to%20merge%20multiple%20excel%20files%20into%20one%20spreadsheet%20using%20Power%20Query%2C%20but%20I%20receive%20the%20following%20error%20when%20doing%20so%3A%3CBR%20%2F%3E%3CBR%20%2F%3EAn%20error%20occurred%20in%20the%20%E2%80%98Transform%20File%E2%80%99%20query.%20Expression.Error%3A%20The%20key%20didn't%20match%20any%20rows%20in%20the%20table.%3CBR%20%2F%3EDetails%3A%3CBR%20%2F%3EKey%3D%3CBR%20%2F%3EItem%3DClient%20Lists%3CBR%20%2F%3EKind%3DSheet%3CBR%20%2F%3ETable%3D%3C%2FP%3E%3CDIV%20class%3D%22bbTable%22%3E%3CBR%20%2F%3E%3CBR%20%2F%3ENow%20a%20bit%20of%20information%20on%20my%20data%20sheets.%20The%20workbooks%20I'm%20trying%20to%20combine%20are%20tabled%20and%20also%20contain%202%20tabs%20each.%20I'm%20trying%20to%20specifically%20combine%201%20of%20the%20tabs%20from%20each%20sheet.%20In%20the%20Power%20Query%20navigator%2C%20it%20shows%20the%20options%20of%20'Table'%20and%20the%20named%20tab%20I%20want%20to%20focus%20on.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20tried%20to%20separate%20the%201%20specific%20tab%20into%20one%20book%20and%20ensured%20all%20of%20my%20books%20have%20the%20exact%20same%20headers.%20When%20I%20try%20to%20combine%20I%20still%20receive%20the%20same%20error.%3CBR%20%2F%3E%3CBR%20%2F%3EDo%20I%20have%20to%20un-table%20my%20sheets%3F%20Or%20do%20I%20have%20to%20create%20a%20new%20book%20for%20the%20specific%20sheet%20I'm%20looking%20to%20combine%3F%20Please%20help!%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2443417%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2467408%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20error%20'The%20key%20didn't%20match%20any%20rows%20in%20the%20table.'%20when%20combining%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2467408%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F754353%22%20target%3D%22_blank%22%3E%40Ozz_Kozz%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20column%20data%20types%20on%20both%20table%20must%20be%20the%20same.%3C%2FP%3E%3CP%3EWhat%20kind%20of%20join%20did%20you%20select%20in%20your%20merge%20query%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Echeers%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2467566%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20error%20'The%20key%20didn't%20match%20any%20rows%20in%20the%20table.'%20when%20combining%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2467566%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1047584%22%20target%3D%22_blank%22%3E%40Yea_So%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20easy%20to%20reproduce%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20620px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F290384iD0FF1626BD56A18B%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2467589%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20error%20'The%20key%20didn't%20match%20any%20rows%20in%20the%20table.'%20when%20combining%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2467589%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%2C%3CBR%20%2F%3E%3CBR%20%2F%3ESo%20which%20is%20it%20that's%20creating%20the%20error%20message%20since%20you%20already%20re-created%20the%20error%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2443869%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20error%20'The%20key%20didn't%20match%20any%20rows%20in%20the%20table.'%20when%20combining%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2443869%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F754353%22%20target%3D%22_blank%22%3E%40Ozz_Kozz%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20guess%20you%20use%20From%20Folder%20connector.%20For%20it%20both%20files%20shall%20have%20the%20same%20structure.%20If%20you%20select%20'%3CSPAN%3EClient%20Lists'%20sheet%20for%20sample%20(first)%20file%2C%20sheet%20with%20the%20same%20name%20shall%20be%20in%20another%20file.%20If%20you%20combine%20based%20on%20structured%20tables%2C%20they%20shall%26nbsp%3Bhave%20the%20same%20name%20in%20all%20files.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2861003%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20error%20'The%20key%20didn't%20match%20any%20rows%20in%20the%20table.'%20when%20combining%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2861003%22%20slang%3D%22en-US%22%3EIt%20worked%20for%20me%2C%20thanks!%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello everyone,
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.
Details:
Key=
Item=Client Lists
Kind=Sheet
Table=



Now a bit of information on my data sheets. The workbooks I'm trying to combine are tabled and also contain 2 tabs each. I'm trying to specifically combine 1 of the tabs from each sheet. In the Power Query navigator, it shows the options of 'Table' and the named tab I want to focus on.

I tried to separate the 1 specific tab into one book and ensured all of my books have the exact same headers. When I try to combine I still receive the same error.

Do I have to un-table my sheets? Or do I have to create a new book for the specific sheet I'm looking to combine? Please help!
11 Replies

@Ozz_Kozz 

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.

@Ozz_Kozz 

 

The column data types on both table must be the same.

What kind of join did you select in your merge query?

 

cheers

@Sergei Baklan,

So which is it that's creating the error message since you already re-created the error

@Yea_So 

That's in my very first answer above

@Sergei Baklan
Yes and I my response was to supplement your response and make sure that the data type details and my join type question is not overlooked by the user which is not mentioned in your response.

"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."
I had a similar problem and for me the solution was simple. I just made the names of the worksheets in each workbook identical. I had 10 excel files, eg. USA.xlsx, China.xlsx, UK.xlsx, etc, each with one worksheet. I simply renamed the worksheet in each file as "SomeName" and that solved my problem.

@PGonzaga You are a genius. Simple and fast solution. Thanks you mate.

@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?