CODEWITHSUNDEEP
excelfunctionpowerquery
David N.
David N.

Reputation: 1

How can I use Power Query to automatically change the path required for a query when changes automatically occur?

I am currently working on a project in which I am trying to simplify the analysis of 12 reports, all of which are validated against one other report.

I am working with Power Query in order to format the reports so that they are more useful.

My issue is the sourcing, as the reports change every day and also that each report looks different, hence I need to load them separately and cannot load them in one go.

I have one folder in which the reports are loaded every day; unfortunately the names change and hence the query path changes / is lost. (e.g. RG0040_20180701 changes to RG0040_20180702.)

The first part of the name is always the same, but the date within the filename changes.

I could manually rename every report each day but this is not plausible nor efficient as I want to reduce the time taken across the whole process.

How can I resolve this issue?

My initial thoughts are perhaps some VBA that could tell the query to look for only the name (e.g. RG0040) as this is already a unique identifier to the report might be a possible solution.

Upvotes: 0

Views: 47

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

I would suggest loading from the containing folder rather than the specific file and then filter down to pick the most recent file that matches your naming criterion.

If the reports don't have consistent structure, then automation will be much more difficult.

Upvotes: 1

Related Questions