Excel Power Query - Change Folder Location (using MS Office Professional Plus 2016 - 64 bit version)

[Mark Goodridge1]

Hello,

I hope someone can help me with this query:

Each month I run a set of Excel Power Queries to pull data from 50 files into a consolidation model.
Each of the Power Queries pulls through different datasets from the 50 Excel files, e.g. 1 would be for financial data, another would be for HR data, etc.
These are all held in a monthly folder, so P1 would be April, P2 would be May, etc.
Each of the 50 files has the exact same layout in terms of tabs, formula, etc.
Each of the file names has a different description, e.g. File 1 may be called Executive Team 21-22 P2 v1.0.xlsx, File 2 may be called Finance 21-22 P2 v1.0.xlsx, etc.

Yet, each month I find myself having to recreate the Power Query, change the location of the folder, etc. I am using the Data\New Query\From File\From Folder menu options.

At the moment, I transfer 1 file to a holding area within the network, from which I create the Power Query, then once that has been tested and works, I transfer the remaining files and re-run the Power Query.

Obviously, this takes quite a bit of time post-month-end, when we do our monthly financial reporting.

I'm relatively new to Power Queries, but they do save a lot of time, but now I am trying to take it to the next level in order to save even more time, (as per my Manager's request!).

Thank you in advance.

Mark

 

[alansidman]

check this video out-->

and this one-->

 

[Mark Goodridge1]

Hi

Many thanks for your response. I will have a look at the video, hopefully, it will resolve my issues