Forum Discussion
Excel Power Query whats the best way to combine multiple Tables across multiple workbooks
Helper VIt seems like you're building to a false assumption. The error is caused when table3 doesn't exist--why doesn't it exist? Is some random other file getting mixed in? Is someone upstream giving you improperly formatted reports?
If you step the code back in the PQ editor you can find the step just before the "function" is applied which brings in the file contents. You can see file-by-file where the error is, and investigate whats going on.
You should be able to select "skip files with errors" to drop these and clear the fault, but is that right?
Hi, the data in table3 was included in daily reports part way through the year.
Also the files are uploaded by different people so may not always include this particular table
Using skipfiles with errors does not work when trying to load to connection only plus add to the data model as I receive an error
- j_ocean
Jayna wrote:Hi, the data in table3 was included in daily reports part way through the year.
If this was the only problem I'd say try making two connections for the two formats and appending them after that point vs doing all 300 in one go....except:
Jayna wrote:Also the files are uploaded by different people so may not always include this particular table
This is definitely a more fundamental process problem. Those people are either lagging behind in updating their templates or its a different type of report being muddled in regardless of what you call it. Your first option should be to address the upstream issues.
That said I don't follow why the skip files is not working. It should. You can manually filter out the errored files before the expand function, filter the files based on some file name or date criteria, or manually move the files around to different folders.