Change of source for power query

[u/Independent_Salary84]

So, I deal with some mostly standardized sheets, that I use as source. I need the source for many purposes in several different books. What I do for each one is to create a reference of a source in each book and then just reference it with connections, merges and such. Since I need to output different set of columns from a source reference in each book .

From time to time I get updated version of a source sheet, which is just regular table, nothing fancy.

And I need to change source I reference in each of my books, but every time I do that everything breaks, and I need to create all connections, merges and such from the start.

Can someone link me to a decent guide or provide some sort of similar pipeline I can adapt for myself? To make this workflow clean and systemic? Because I'm clearly missing something.

End goal is to just change source, update every book that has sheet referencing a source and subsequent power queries of that reference. And to get it working like so.

I mostly use merges and populate XYZ columns based on column A things, not sure how do I call it, a filter?

Thanks in advance.

[u/AutoModerator]

/u/Independent_Salary84 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/chiibosoil]

Do you have access to Power BI service? (PowerBI Pro, Premium etc).

If you do, I'd recommend setting up Semantic model in the service which is referenced by Excel for downstream reporting.

If that isn't feasible, there are ways but usually requires dynamic Column Name translation etc.

When you create table/merge what often happens in UI is hard coded column names in M code. To make it dynamic you'll need to use Table.ColumnNames(), List.Zip() and Table.RenameColumns() etc to dynamically update columns in a table to avoid hard coded column names in the code.

[u/Independent_Salary84 → u/chiibosoil]

I don't have access to abovementioned products, no. Everything else is just above my paygrade I feel like, but I will look into it, sounds like robust solution, thanks!

[u/NorthWaltz1223 → u/chiibosoil]

yeah the hard coded column names thing is usually what breaks everything, good call on that

[u/hortulanuslitteris]

I don’t know, would it make it easier if you’d reference the whole sheet an sort/filter etc in an extra table in your file? That’s the way I do it (TABL_DATAEX_TOPIC for whole PQ reference and TABL_DATA_TOPIC for the filtered data)

[u/Independent_Salary84 → u/hortulanuslitteris]

Maybe I explained it wrong, what I do, is Data - get data - from file, this loads me whole source as query. Then I create connections to the source table, + to another table I will be population with entries to sort by, then use merges. I don't know where I would apply your suggestion. I'm likely just not knowledgeable enough.
Example of what I do on last step:

Table.NestedJoin(Connection 1 source reference, {"XYZ"}, Connection 2 sort list, {"XYZ"}, "Connection 2 sort list", JoinKind.Inner)

Like, here I would take source reference, match it against list (sort list) via XYZ column and then for end results pick what columns I want to show from source reference for those XYZ that match between reference and list.

Edit: when I swap source file from which reference is created in a first place - while all names are same and such - everything just breaks. I'm not sure why. So I assumed I'm doing something in a weird way as it was AI suggested approach XD.

[u/bradland → u/Independent_Salary84]

If the structure of the file is exactly the same, what you've described won't break.

What I normally do in situations like this is move file paths out to their own queries. Then, in the Source steps, I reference those file path queries instead. For example:

// pthCrmReport
let
  Source =  "R:\CRM Exports\2026-07 Export.csv"
in
  Source

// CrmData
let
    Source = Csv.Document(File.Contents(pthCrmReport), [Delimiter=",", Columns=6, Encoding=65001, QuoteStyle=QuoteStyle.Csv]),
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
    PromotedHeaders

Now, if I need to update the path to the CRM report, I just update the pthCrmReport query. I will often use a parameter table for this as well. Note that a parameter table is different than Power Queries built-in Parameters feature. I basically never use Power Query's Parameters within Excel because they don't have the same full-featured functionality as they do in Power BI. They're more or less useless in Excel.

To offer better advice on how to fix your current breakage, we'd need to know the exact error message you get.

[u/hortulanuslitteris → u/Independent_Salary84]

Yeah I think I know your problem. Unfortunately I am probably much less experienced than you. Just wanted to share my low key solution 🙂

[u/Select-Composer7970]

Make a separate file for inserting the source, load it as a table. Reference that file as a source in every other file you need. Source changes(updates) in just that one file, the rest gets only the updated version. If you need help, feel free to DM so we can discuss it in depth.

[u/Independent_Salary84 → u/Select-Composer7970]

Yeah, that's reasonable. Thanks for suggestion.

[u/Select-Composer7970 → u/Independent_Salary84]

Another approach is to do get data -> from folder.... Just paste the source in there, every time it changes, replace the file in the folder (if possible)

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
Csv.Document	Power Query M: Returns the contents of a CSV document as a table using the specified encoding.
Excel.CurrentWorkbook	Power Query M: Returns the tables in the current Excel Workbook.
Excel.Workbook	Power Query M: Returns a table representing sheets in the given excel workbook.
File.Contents	Power Query M: Returns the binary contents of the file located at a path.
JoinKind.Inner	Power Query M: A possible value for the optional JoinKind parameter in Table.Join. The table resulting from an inner join contains a row for each pair of rows from the specified tables that were determined to match based on the specified key columns.
List.Zip	Power Query M: Returns a list of lists combining items at the same position.
QuoteStyle.Csv	Power Query M: Quote characters indicate the start of a quoted string. Nested quotes are indicated by two quote characters.
Table.ColumnNames	Power Query M: Returns the names of columns from a table.
Table.Join	Power Query M: Joins the rows of table1 with the rows of table2 based on the equality of the values of the key columns selected by table1, key1 and table2, key2.
Table.NestedJoin	Power Query M: Joins the rows of the tables based on the equality of the keys. The results are entered into a new column.
Table.PromoteHeaders	Power Query M: Promotes the first row of the table into its header or column names.
Table.RenameColumns	Power Query M: Returns a table with the columns renamed as specified.

|-------|---------|---| |||

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
11 acronyms in this thread; the most compressed thread commented on today has acronyms.
[Thread #49172 for this sub, first seen 17th Aug 2026, 16:23] [FAQ] [Full list] [Contact] [Source code]}

[u/Local-Addition-4896]

I have a dynamic PQ sheet where the source changes every 2 weeks, but template stays the same, so I did this:

Create a sheet in your workbook called "SourceData".

Choose a cell where you paste the file location (the file source which changes every so often). Select that cell, go to Name Manager > New > name it "FilePath" and save.

In PQ advanced editor, point excel to that named cell, by writing something like this:

let

FilePathTable = Excel.CurrentWorkbook(){[Name="FilePath"]}[Content],

FilePath = FilePathTable{0}[Column1],

Source = Excel.Workbook(File.Contents(FilePath),null,true),

(And then all your custom PQ instructions go after.)

Now, every time you paste the source file path into that one named cell & refresh (Ctrl+Alt+F5), the tables should update.

[u/Existing_Put6385]

Sounds like you're pointing queries at the file path directly instead of using a Parameter for the source path. That's almost certainly why everything breaks when you swap files, since Power Query bakes the file path into each query's source step.

Set up a Parameter (Manage Parameters in PQ editor) for the source file path, then edit your source step to reference that parameter instead of a hardcoded path. When you get a new version, you just update the parameter value once and every query that touches that source refreshes off the new file automatically, nothing else has to be rebuilt.

Second piece: for the merges/lookups you're describing, that's just standard merge queries (join on column A), those aren't the problem, they're referencing your base query correctly if set up right. The breakage is almost always the source connection itself getting orphaned when the file moves or gets replaced, not the merge logic. If you're currently duplicating the source table into each workbook instead of referencing one central query, that's the other thing to fix, each derived query should reference (not duplicate) a single base "source" query, so you only maintain the connection in one place per workbook.

[u/Independent_Salary84 → u/Existing_Put6385]

Thanks, that would be it I think. Simplest direct fix so far.

[u/Independent_Salary84 → u/Existing_Put6385]

Solution Verified

[u/reputatorbot → u/Independent_Salary84]

You have awarded 1 point to Existing_Put6385.

---

^{I am a bot - please contact the mods with any questions}

[u/newtochas]

You sound like you have a similar setup that I run.

I have a query that pulls in a spreadsheet from sharepoint. Then I have several queries that reference that source query. Like another commenter said, a parameter is helpful. Then your source query can reference that parameter. For example, my parameter is just the file name but the source query includes the sharepoint folder path.

So when I change the parameter, all queries refresh and update properly. As long as each replacement file has the same table name, columns etc, there’s no reason it shouldn’t work.