Power Query

Hi Claire,

Thank you for the forum question.

This happens because Power Query hard codes the full path (including the filename) in the query’s Source step. When you rename or move the source workbook, that path no longer matches, so refresh fails.
Below are reliable ways to make your query resilient to renames and moves. Pick the one that best fits your setup.
________________________________________
Option A — Make the filename & folder dynamic with Parameters (simple & robust)
Goal: Store the folder and filename in parameters (or named cells) so you can change names without editing M every time.
1) Create two named cells in your Master workbook
In a sheet (e.g., “Config”):
• A1: SourceFolder → put the folder path, e.g. C:\Data\Orders\
• A2: SourceFile → put the filename, e.g. WeeklyOrders.xlsx
Give each cell a Name (Formulas › Name Manager):
• Name the first cell SourceFolder
• Name the second cell SourceFile
(Tip: make sure SourceFolder ends with a trailing backslash \ on Windows or / on Mac.)
2) Bring those named cells into Power Query
• Data › Get Data › From Other Sources › From Table/Range (select each named cell one at a time)
→ This creates two small queries (tables with a single value). Rename the queries exactly SourceFolder and SourceFile and Convert to List or Keep Only the Value so each returns a single text value.
3) Point your existing query to those parameters
Edit your existing query (or create a new one):
let
// Read the values from the small queries (each returns a single text)
FolderPath = SourceFolder{0},
FileName = SourceFile{0},

// Build a dynamic path
FullPath = FolderPath & FileName,

// Load the workbook
Source = Excel.Workbook(File.Contents(FullPath), null, true),

// Continue your existing steps (example sheet/table selection)
OrdersTbl = Source{[Item="Orders", Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(OrdersTbl,{
{"Order Date", type date}, {"Qty", Int64.Type}, {"Amount", type number}})
in
#"Changed Type"
Now if you rename the source workbook, just update the SourceFile cell in the Master. No need to edit the query.
________________________________________
Option B — Use the Folder connector and pick by name/date (best for recurring files)
If your source file lives in a folder and might change names or versions (e.g., WeeklyOrders_2026-03-19.xlsx), let Power Query find it.
1. Data › Get Data › From File › From Folder
Point to the folder that holds the orders file(s).
2. In the query:
o Use Table.SelectRows to filter to the correct file by a pattern (e.g., files that contain “WeeklyOrders”).
o Optionally sort by Date modified and keep the latest file.
o Expand the Binary (Content) to a table via Excel.Workbook.
Example M:
let
// Parameterize the folder if you want (reuse Option A's SourceFolder)
FolderPath = SourceFolder{0},

Source = Folder.Files(FolderPath),

// Keep only files whose name contains 'WeeklyOrders' and are .xlsx
Filtered = Table.SelectRows(Source, each Text.Contains([Name], "WeeklyOrders") and Text.EndsWith([Extension], ".xlsx")),

// If multiple, pick the most recent by Date modified
Sorted = Table.Sort(Filtered, {{"Date modified", Order.Descending}}),
Latest = Table.FirstN(Sorted, 1),

// Access the binary and open the workbook
GetBinary = Table.AddColumn(Latest, "Workbook", each Excel.Workbook([Content], null, true)),
ExpandedWB = Table.ExpandTableColumn(GetBinary, "Workbook", {"Data", "Item", "Kind"}, {"Data", "Item", "Kind"}),

// Pick the specific table/sheet you need
OrdersTbl = Table.SelectRows(ExpandedWB, each [Kind] = "Table" and [Item] = "Orders"){0}[Data]
in
OrdersTbl
This way you never hard code the filename. Renaming is fine as long as it still matches your pattern.
________________________________________
Option C — Use a relative path to the Master workbook (portable workbooks)
If both files live in the same folder (or a subfolder), you can compute the master’s folder at runtime and build a relative path. This makes the solution portable across machines or share drives.
1. In a cell in the Master workbook, create a formula that returns the workbook path:
=LEFT(CELL("filename"),FIND("[",CELL("filename"))-1)
Name that cell ThisWorkbookPath.
2. Pull ThisWorkbookPath into Power Query as in Option A (a single-value query).
3. Build the path relative to the master:
let
BasePath = ThisWorkbookPath{0}, // e.g., C:\Team\Reporting</span>
Relative = "Data\WeeklyOrders.xlsx", // adjust to your structure
FullPath = BasePath & Relative,
Source = Excel.Workbook(File.Contents(FullPath), null, true),
OrdersTbl = Source{[Item="Orders", Kind="Table"]}[Data]
in
OrdersTbl
Renaming the source file? Just change Relative (or keep it in a named cell/parameter like in Option A).
________________________________________
Option D — If your file is on OneDrive/SharePoint/Teams, use the correct connector
Instead of local file paths, use:
• Data › Get Data › From SharePoint Folder (for a site library), or
• From OneDrive/SharePoint links using the organization connector.
Build a query that finds the file by Site URL + File name (filter), which won’t break when you rename the file within the library—especially if you use the Folder approach (Option B) together with SharePoint. Avoid using raw links with Web Contents unless necessary.
________________________________________
How to retrofit your existing broken query
1. Data › Queries & Connections › Right click your query › Edit.
2. In Power Query, select the first step (Source). You’ll see something like:
Source = Excel.Workbook(File.Contents("C:\Data\Orders\WeeklyOrders.xlsx"), null, true)
3. Replace it using any of the patterns above, e.g. parameters:
Source = Excel.Workbook(File.Contents(SourceFolder{0} & SourceFile{0}), null, true)
4. Close & Load.
________________________________________
Common pitfalls & tips
• Privacy Levels: If combining files from multiple locations, set appropriate privacy levels (File › Options › Trust Center › Privacy) to avoid “Formula.Firewall” errors.
• Trailing slash: Ensure the folder value ends with \ (Windows) or / (Mac) before concatenation.
• Table/Sheet names: If you’re selecting by [Item="Orders", Kind="Table"], that Item must match the exact table name in the source.
• Caching: After changing parameters, do a full Refresh. If you added a named cell, ensure it’s visible to Power Query (not volatile, not returning an error).
• Version control: If your naming pattern changes (e.g., from WeeklyOrders_YYYYMMDD.xlsx to Orders_Week_##.xlsx), update the filter pattern once instead of editing paths everywhere.
________________________________________
Which option should you choose?
• You want quick control over a single file name: Option A (parameters).
• You receive a new weekly file with a date in the name: Option B (Folder + “latest” logic).
• You want maximum portability (e.g., zipped package, shared directory): Option C (relative path).
• Your files live in SharePoint/OneDrive/Teams: Option D (proper connector + pattern filter).


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector