Power Query Problems

How to Create a Dynamic Power Query Source for Changing Files

WPS Editor Oct 8, 2026 868 views

Question details

The user needs to configure Power Query to successfully refresh when the source file's name and folder path change daily.

How to Create a Dynamic Power Query Source for Changing Files and Folders

Product: Microsoft Excel
Device & OS: not provided
Scenario: Refreshing a Power Query connected to a daily generated report where the file name or folder path contains dynamic data like dates.
Observed behavior: The Power Query refresh fails because it looks for a static file path that no longer matches the current daily file.

Before you start

Ensure you have the exact file path and naming convention of your dynamic files, and verify that the data structure within the changing files remains consistent every time.

Solution 1Recommended

Use an Excel Parameter Table for Dynamic Source Paths

Create a named range or parameter table in your Excel workbook to feed the dynamically generated file path directly into Power Query.

By generating the file path dynamically using Excel formulas (like pulling today's date), you can pass that exact string into Power Query. This ensures the query always searches for the correct daily file without manual intervention.

Create a Path Cell

In your Excel workbook, type your folder path and file name into a cell. Use Excel formulas (like TEXT(TODAY(),"yyyy-mm-dd")) to dynamically update the file name.

Name the Cell

Select the cell, go to the Name Box (next to the formula bar), type 'FilePath', and press Enter to create a named range.

Load to Power Query

Select the 'FilePath' cell, go to the Data tab, and click 'From Table/Range' to load this parameter into Power Query.

Drill Down to Value

In the Power Query Editor, right-click the text value in the parameter query and select 'Drill Down' to convert it into a pure text string.

Update Source Step

Open your main data query, go to the 'Source' step in the Applied Steps pane, and replace the hardcoded file path string with the word FilePath (the exact name of your parameter query).

Use an Excel Parameter Table for Dynamic Source Paths

Privacy Levels: If you encounter a Formula.Firewall error after this setup, go to File > Options and settings > Query Options, and under 'Privacy', select 'Ignore the Privacy Levels' for your current workbook.

Solution 2

Use a Fixed Folder and Consistent File Name

Avoid dynamic path complexities by overwriting a standard file in a fixed directory each day before refreshing the query.

Free Microsoft Office alternative

Looking for a Lightweight Alternative for Data Processing?

If you frequently struggle with complex Excel configurations or Power Query errors, consider WPS Office. It provides a lightweight, highly compatible, and user-friendly spreadsheet environment for your data analysis needs without the heavy resource usage of Microsoft Office.

1. Download and Install: Get WPS Office from the official website and run the lightweight installer.
2. Open Your Data: Launch WPS Spreadsheet and open your existing .xlsx data files directly.
3. Analyze Data Easily: Use the familiar Ribbon interface to apply formulas, pivot tables, and data consolidation tools without steep learning curves.

Seamlessly open, edit, and save Microsoft Excel (.xlsx, .xls) files without formatting loss.Intuitive data processing and analysis tools with a familiar user interface.Completely free to download with a lightweight installer that saves PC resources.Built-in advanced formulas and dynamic array functions to automate your daily reports effortlessly.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Power Query refresh fail every day when the file name changes?

Power Query records the exact file path and name when you first connect to the data. If the daily file name changes (e.g., includes today's date), the query cannot find the source file at the static path it originally saved, resulting in a 'DataFormat.Error' or 'File Not Found' error.

Can I use an Excel cell value as a Power Query source without using VBA?

Yes, you can create a Parameter by loading a single cell into Power Query using 'From Table/Range', right-clicking to drill down to its text value, and then replacing the static file path string in your main query's Advanced Editor with this parameter's name.

How do I dynamically get the latest file from a folder in Power Query?

Instead of connecting to a specific file, connect to the entire folder (Get Data > From File > From Folder). In the Power Query Editor, sort the 'Date Modified' or 'Date Created' column in descending order, keep only the top row (Keep Rows > Keep Top Rows > 1), and expand the binary content to always load the newest file automatically.

Excel & Data Problems