•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
How to dynamically change folder path in Power Query each month?
Our take
If you're an accountant managing month-end reports, you know how tedious it can be to manually update the folder path in Power Query each month. This process can disrupt your workflow and consume valuable time. Fortunately, there’s a way to dynamically change the folder path to reflect the current month automatically. By leveraging Power Query's M code, you can simplify this task, allowing it to detect the current month’s folder without manual intervention.
Hi, I’ve been struggling with this for hours. Copilot and Gemini keep giving me code that doesn’t work.
I’m an accountant, and during month-end close I usually compile several reports and paste them into a folder. Then I run a simple Power Query that reads, transforms, and filters the data into a final table.
It works perfectly.
The issue is that every month I copy and paste the files into a new folder, and I have to manually edit the directory path in Power Query — for example:
C:\Users\wakiarg\THE COMPANY\THE SHAREPOINT - Documents\2026\2026-01\
Then I change it to 2026-02, 2026-03, and so on.
Is there a simple way to modify the M code so Power Query automatically detects the current month folder (e.g., -02, -03, etc.) instead of me having to manually update the path each time?
The IA gave me some ideas using =cell but it's giving me an URL instead of the local directory, and from there on its a mess.
Ideally, I’d like it to dynamically reference where the file is stored, as the folder is always there.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- How can I use Power Query to pull data from a folder with dynamic subfolder names?I have a process where new monthly data lands in a folder structure like MainFolder\2026-03\Data. I want Power Query to automatically pull from the current month's subfolder without me manually updating the path each time. I know I can use a named range with the folder path and reference it in M, but I'm wondering if there's a cleaner approach within Power Query itself. What's the best way to make the folder source dynamic so it always grabs the latest month's data when I refresh? submitted by /u/Busternookiedude [link] [comments]
- Power query unable to connect to network folder but I can access it in file explorerI have an excel file that uses power query to pull data from multiple csv files stored in a folder on our company network drive. It has been working fine for months. Today I opened the file and refreshed and got an unable to connect error. The folder path is correct. I can navigate to it manually in file explorer and open the csv files without any issue. I checked data source settings in power query and the credentials are set to anonymous because it never needed a login before. I tried remapping the network drive with a different letter and updating the path in power query but still get the same error. Nothing has changed on my end that I know of. Could this be an IT thing where they pushed an update that broke something, I saw some older posts about sensitivity labels causing connection issues. Is that still a thing, I use excel from microsoft 365. Has anyone else run into this recently and found a fix, I really dont want to rebuild the whole query if I can avoid it. Any help would be appreciated. submitted by /u/Maiden230 [link] [comments]
- Power query automation with combining two tabsI want to create either a skeleton workbook folder or a template where people can upload their data and it runs all of the conditions on power query that I want. Also, it pulls definitions from a secondary tab and matches them with terms that are from the query and merge them. I basically just want them to be able to paste their data raw and it comes out the way I format it with the steps I’ve already created in query. I have watched every YouTube. Searched. Everything We write a report every month and I am trying to make it a very user-friendly report for them and minimize the extra information They don’t need and also link definitions to be able to understand. Please help. submitted by /u/HeavyBreadfruit3667 [link] [comments]
- Excel Power Query: Renaming Queries Already Associated with Pivot Tables & ChartsHello! I created a massive workbook for a client and parameterized the geography and vintage of my queries in the Power Query Editor so that I can easily make a similar file with different parameters. The problem is I named the queries themselves too specifically (see pic) and now I want to change them but it breaks all of my associated pivot tables and charts. For example, I would like to change ACS_Poverty_County_1YR to ACS_Poverty so that the query names are not misleading when I create an MSA/Zip Code version of the file. This file is way too big to remake so I'm desperate to find a way to fix it without having to remake the visualizations. Any help is much appreciated! https://preview.redd.it/eszqgftt8nsg1.png?width=490&format=png&auto=webp&s=dcaf3b4317b4e955da792bdefbc4ead98ad96f42 submitted by /u/classicjam [link] [comments]
Tagged with
#Excel alternatives for data analysis#generative AI for data analysis#natural language processing for spreadsheets#real-time data collaboration#no-code spreadsheet solutions#rows.com#big data management in spreadsheets#conversational data analysis#Excel compatibility#financial modeling with spreadsheets#intelligent data visualization#real-time collaboration#data visualization tools#enterprise data management#big data performance#Excel alternatives#data analysis tools#data cleaning solutions#Power Query#folder path