Forum Discussion
Excel Power Query whats the best way to combine multiple Tables across multiple workbooks
I am using Excel Power Query to consolidate a number (300+) of Excel files from sharepoint document library
Each of the files will have one or more Tables/Worksheet. For example.
File 1: table1, table2, table3 File 2: table1, table2 File 3: table1, table3
I would like to combine table3 only across all the workbooks and ignore all other tables. How best can I acheive this?
When attempting to combined I receive an error stating key was not found
I found the below post but the m code doesn't seem to work in my file Excel Power Query - What's the best way to combine Excel Files with multiple Worksheets where WorkSheet may not exist in all Workbooks
Thanks in advance
4 Replies
j_oceanHelper V
It 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.
I receive this error:
the source table does not exist add data source
