Power Query - repeat process of adding and dropping connections

Question

Each month I carry out a process where I add the previous months' forecast spreadsheets to a forecast summary spreadsheet's workbook queries, append them all (they have the same columns) and then delete the prior-previous months' data connections from the workbook queries. E.g. add division 1 month 6, division 2 month 6, division 3 month 6 etc. Append then delete division 1 month 5, division 2 month 5 and division 3 month 5.

The reason for deleting the prior-previous months' data connections is because Excel 2010 and Power Query cannot seem to handle more than 3 months worth of spreadsheets, throwing a system out of memory error on many occasions. The reason I need previous months' data is because I compare that to the current months' data to show differences between the two months.

What I want to do is automate the process of adding tables to my workbook queries and deleting tables from my workbook queries. Is this possible?

Answer 1

Assuming the Division and Month can be identified from your file names, and they are all in one folder or subfolders, then I would start from Get Data or New Query / From Folder. I would then Edit the Query to filter the list of files down to just the ones you want to load, then hit the Combine Files button (on the Content column header).

That will generate the whole Function and Parameter setup for you in the Query Editor. You just need to add the Steps to suit your requirements and you are done.

Refresh performance should be as good as your manual method - it will only open the files that get through your filter criteria.

Once you get that all set up, there will not be any manual admin required - just hit Refresh.