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.
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.
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
@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.
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-us/powerquery-m/power-query-m-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
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 🙂
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
