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):
Power Query
>From file
(inLoad external data
Section) >From folder
- In dialog window with list of Excel files in folder
Combine and load
- 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
.
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)?