•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
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?
Our take
Are you tired of manually importing client data each month, especially when the file name and location change? Power Query offers a streamlined solution that simplifies this process, allowing you to automate data imports with ease. By leveraging wildcard formatting and a dynamic file path, you can ensure that your spreadsheet always pulls the correct data. In this guide, we’ll explore how to set up Power Query to automatically retrieve your data, making the refresh process seamless and efficient.
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?
[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]
- PowerQuery and add manual dataHi everyone, I have a Power Query in Excel that outputs a table with [title] [date]. I need to manually add the Sprint number in an extra column [sprint] to specific combinations, aka "I will work on this this month." The problem is every time the query refreshes, any manually entered data gets lost or misaligned. - New rows come with the needed values. - Row order changes. Because this table is used by many people, I want them only to add the sprint number, nothing else, no copying data or anything. I would like to know more about your experiences when data needs to be written infrequently but many times. I am open to know more for powerbi options direct dashboard too. submitted by /u/No_Solid2349 [link] [comments]
- Summarize simple use of Power Query for impatient experienced user with simple need?I'm a longtime Excel user, comfortable in using it for all kinds of text manipulation and basic number crunching, beginning to get a handle on tables and new 365 formulas. I've learned most of what I know from self study and enjoy it, but at the moment I'm a bit overwhelmed with trying to get started with Power Query. It seems like just the tool for what I need to do, but it's also designed for a lot more, and I'm having trouble getting my bearings since even the basic introductions assume that my needs are much more complex than they are and talk about a lot of options that are irrelevant to me. This is all I need to do: I have a workbook with a table where I keep track of work on inquiries sent to an online platform from which I can export submissions into Excel. The export contains a few columns I don't need, and lacks a few others that I add in the workbook, so what I've been doing is opening the export file, deleting unneeded columns, adding blank rows to the bottom of the table, copying the data from the export file, and pasting it in beginning with column D. Seems like just the kind of thing to automate with Power Query. But I don't want to keep the export file anywhere once I've appended its contents to the workbook. The data remains in the online platform if I need it, and storing any subsets would only waste space and cause confusion. I want to export the latest submissions, transform them with Power Query (I've already figured out how to do that), append them to my workbook, disconnect the two files, and delete the export. Sounds simple enough. But I can't seem to find out how to do this very simple thing without having to sit through long explanations of things I don't need to do and am too busy to wrap my head around right now. Can anybody point me to some simple step-by-step directions that just tell me how to do only what I need to do without bothering with all the other possible scenarios? Or is Power Query overkill for my needs? Is there another alternative? Thanks. submitted by /u/fastauntie [link] [comments]
- Power query and manual table next to itHi, I want to pull data verbatim from a spreadsheet my team uses and use data from it for my own purposes. The main goal for using power query is that the data updates on my spreadsheet. Mainly, if any new entries are added at the bottom. I also have some manual fields that I need to add that correspond with the power query data. I've added another table beside the power query data, and filtering it causes the data on both sides to adjust correctly. I'm mainly concerned that, if the entries are rearranged or sorted on the original sheet, that my tables will not align after a refresh. Also, if a refresh would break my table alignments at any point. Is my fear founded? Is there a way to combine the two features that I need into a single table? submitted by /u/Perspective-Guilty [link] [comments]
Tagged with
#Excel alternatives for data analysis#generative AI for data analysis#real-time data collaboration#big data management in spreadsheets#conversational data analysis#intelligent data visualization#digital transformation in spreadsheet software#no-code spreadsheet solutions#modern spreadsheet innovations#machine learning in spreadsheet applications#enterprise-level spreadsheet solutions#collaborative spreadsheet tools#AI-driven spreadsheet solutions#cloud-based spreadsheet applications#automation in spreadsheet workflows#rows.com#natural language processing for spreadsheets#Excel compatibility#financial modeling with spreadsheets#Power Query