Forum Discussion
Power Query error 'The key didn't match any rows in the table.' when combining
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. ...
I found this video on Ewetoob, on how to fix this. Very easy.
https://www.youtube.com/watch?v=JZSdAvGAq_E
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
= Source{0}[Data]That should fix it!
I've been struggling for three hours trying to combine 56 Excel spreadsheets with over 26,000 records and this is the first answer that fixed the problem first-try. Thank you x1.000!
