I have tens of Excel files with similar table (means: same name, same headers, different content). I'd like to merge all data from these tables to one big table for future processing.

I did it many times before using these steps (I have non-English version, so strings could be slightly different):

  1. Power Query > From file (in Load external data Section) > From folder
  2. In dialog window with list of Excel files in folder Combine and load
  3. In Window Combine files select the wanted table name.

Now I usually get one big merged table. But for one set I get [Expression.Error]: The key did not match any rows in the table

In Query editor window there is a few files loaded OK and then it ends on whole row filled with Error.

enter image description here

This could be easily walked around by using checkbox "Skip files with errors" in step 3, but I'd like to know what file(s) are causing this error(s).

How to find it (them)?

  • I think the immediate way is to hope that powerQuery loads the files in order, so you can go through each file and compare with what you see in powerQuery. If it is just a few rows in any particular file, then you'll have to do a vlookup to see which is the offending row.
    – gns100
    Jul 12, 2021 at 18:10
  • long term, you may consider redoing your powerQuery to combine files one step at a time, then you can go through the steps to see when the errors pop up. that will pinpoint your file, then you probably have to do some kind of vloolup (or index/match) to find the particular rows...
    – gns100
    Jul 12, 2021 at 18:11
  • @gns100 Thanks for suggestions. what do you think by redoing PowerQuery to combine step by step? Is threre some automated way how to do it? Or it means to add (manually) each file to the query separately ?
    – Lluser
    Jul 13, 2021 at 7:06
  • @gns100 Yes, files are loaded in order as they are arranged in previous applied steps in Power Query Editor, so it's easy way how to determine defective file.
    – Lluser
    Jul 13, 2021 at 7:15
  • @gns100 please create an answer from your comments. I would like to mark it as solution :)
    – Lluser
    Jul 14, 2021 at 4:48


You must log in to answer this question.

Browse other questions tagged .