The real problem isn't transforming data. It's that your source file changes name and your folder structure moves every month. VBA handled it with `ThisWorkbook.Path & "\Data\"`, a relative path that adapts automatically. Power Query, by default, wants absolute paths. When those paths break, your refresh fails. That is not a skill issue. It is a design gap.
The hack you mentioned, having Power Query read a table in the workbook to retrieve file paths, works, but calling it "hacky" is generous. It is workable. You store the relative folder path in a named cell, Power Query reads that cell as a parameter, then builds the file path dynamically. It gets the job done. But it adds a manual step: someone has to update that cell when the folder moves. That is exactly the kind of fragile, human-dependent process that automation is supposed to eliminate.
What you really want is a parameter that resolves itself. A function that says "I am in this workbook, so look for the data folder relative to me." Power Query can do that, via `Parameter.CurrentWorkbook()`, `Excel.CurrentWorkbook()`, or by referencing a named range, but it requires you to structure the query to accept that parameter from the start. It is not as intuitive as VBA's `ThisWorkbook.Path`, and that is a legitimate frustration. The tool claims to be modern, yet forces you to think like a workaround engineer for a basic relative path.
Here is the practical takeaway: if you control the folder structure, embed the relative path logic directly into Power Query using `Excel.CurrentWorkbook()` and a helper table. It is not elegant, but it is reliable. If you do not control the folder structure, if the parent folder moves each month, then you have two honest options. One: keep a small VBA wrapper that updates the parameter cell before refresh. Two: switch to a tool that treats relative paths as a first-class feature. AI-native spreadsheets, for example, can infer file location from context, not hardcoded strings. They do not need you to build a scaffolding of helper cells just to find the file next door. That is the direction worth exploring.