•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
How can I use Power Query to pull data from a folder with dynamic subfolder names?
Our take
Power Query offers a powerful way to streamline your data retrieval process, especially when dealing with dynamic folder structures. By structuring your folder path correctly, you can automate the process of pulling data from the latest month's subfolder without the need for manual updates. Instead of relying solely on named ranges, consider leveraging Power Query's advanced features, such as functions that dynamically generate the folder path based on the current date.
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?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- How to dynamically change folder path in Power Query each month?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. submitted by /u/wakiarg [link] [comments]
- I want to use Power Query to import data received from a client, where the file name changes each month. What's the easiest way to automate this?I used to use VBA for this, but that's a lot more roundabout, and I have a lot less control over the transformation. I have no issues with transforming the actual data itself. My issue lies in the fact that it's a different file each month. Using wildcard formatting, *filehere*.xls* would always pull the correct file. This file is also stored in the same place relative to my spreadsheet each time, but the location of the spreadsheet and folders itself changes each month. In VBA, I could find the relative position quite easily via ThisWorkbook.Path & "\Data\" However, I don't know how to use PQ to import automatically like this, so that I'd always import the correct data simply by refreshing links. I think I've seen people set up a somewhat hacky way, where PQ first reads a table in the workbook to retrieve values, and then uses those to find the file to query. Is that the only way? submitted by /u/space_reserved [link] [comments]