Log In
Register
Home
May 2, 2014
Offline
hi All
I have a user who has a summary file called Summary 2018 in a folder called Pay 2018. There are 12 files feeding into this e.g. Jan 2018, Feb 2018 - all in the same folder. Is there a way in PQ to update all these to Pay 2019 and then point them at Jan 2019, Feb 2019 etc. I've tried using Edit Data but it doesn't seem to allow me to change the source> Thanks.
See Answer
Trusted Members
Moderators
November 1, 2018
Offline
If they are the only files in the folder, you could simply process all the files excluding the Summary one using something like = Folder.Files("folder name here") and then filter out the Summary workbook. That way you only need to edit the folder path in the source, either editing the query, or using a single record parameter table.
VIP
Trusted Members
December 7, 2016
Offline
Hi Anne,
You should be able to change the source folder path using the Advanced Editor in Power Query Editor.
You can also change source path from Power Query Editor --> Data source settings --> Change Source...
/Anders
May 2, 2014
Offline
@Anders Sehlstedt and @Velouria - thank you both. So I have got as far as both the steps you have outlined. Just a couple of questions about this. I've tried updating both the file name and folder path in the query (Have loaded from folder) but when I close and load - it's not updated. I think I'm missing something obvious! Thanks.
VIP
Trusted Members
December 7, 2016
Offline
Hello,
I am sorry, I am not so familiar with the M language, but perhaps there can be some useful information in these pages.
https://docs.microsoft.com/en-.....-reference
But I was thinking of another approach, if it is doable in this case I do not know. But instead of having one file for each month, can it not be one file for each year and all months in respective sheet? And of course a more generic folder name. Then there would be no reason to change the path nor the files.
Any way, best of luck finding a solution.
/Anders
May 2, 2014
Offline
Well, the person has 12 files - one for each month - just the way her Payroll is set up and wants to have the same set up for 2019. Think I'm going to go back and just try the edit data feature again...thanks though 🙂
July 16, 2010
Offline
Hi Anne,
I presume you have a new folder for the 2019 source files. In which case, make a copy of the Excel file containing the 2018 query. This will be for your 2019 data. Open the advanced editor in the new file and change the file path and any reference to 2018 with 2019. If there are a lot of references you could copy the query into Word and use Find & Replace, then copy back into the advanced editor.
You may need to refresh the query upon closing the Advanced Editor.
Mynda
Answers Post
1 Guest(s)
