Power Query - How to refresh queries?

If the new report has the same name and is in the same location, then you can click Refresh All on the Data tab in the ribbon.
If the name of the report and/or the location you'll could use named ranges to make the connection flexible.
So you'll never need to make the connection again, maybe just adjust it.

I am not familiar with Power Query, but I do something similar with MS-Query. I use MS-Query to establish the connection to the database. MS-Query has a query editor similar to MS-Access but except for very simple queries, it is more of a hindrance than a help. In my case, I work with Oracle databases. I write and debug the code there and then copy it into the SQL window for MS-Query.

If MS-Query can display it graphically, then I can pass parameters to it either at run-time though a dialog box or through cells on the spreadsheet. The cells could have dynamic formulas like TODAY()-1 and TODAY()-91 or whatever.

If MS-Query cannot display the SQL graphically, then I have a method using Excel Tables and a short piece of VB code to re-write the query prior to execution. This not only allows me to pass parameters, but I have done things like switch tables, rewrite where clauses and just about anything else as long as I stay in the same data connection (database).

If you are interested, I can walk you through the setup.

Here's what I've done to make your query flexible.

In Report.xlsx I added a sheet Parameters in which I added the FileLocation (B1) and FileName (B2).
Then created a named range "FileLocation" for B1 and "FileName" for B2.
Change the path in B1 to yr needs (don't forget the / at the end).
Change the filename in B2 to yr needs.

Then I added a queryfunction (fnGetNamedRange) for reading named ranges.
And finally I adjusted the query Table1 to use the queryfunction to get the file location and file name.
The source step in the query now looks like this

Formula:

Please Login or Register  to view this content.

You won't need to change the query anymore when the file name or file location changes. Just change the parameters.

Apart from the help you can get on this forum, here's a nice tutorial for beginners

Hi ypurcaro,

Could you change your profile and show what version of Excel you are using? Excel 2003 doesn't have Power Query.

I think what is suggested above, without using a parameter, is to click the gear icon next to Source and change it to your current month file. It should then do the same steps but with the new file.

https://support.office.com/en-us/art...d-6a4921380ca9

I assumed Excel 2003 can use the add-on, but then again that's from a previous life

BTW, the file name and location can be set in the sheet Parameters. Nothing needs to be changed in the Query itself.