Search code examples
exceldatabasepowerquerypowerpivot

Move data stored in excel file and queries to external source (local) to be available to other excel files

I recently modernized all my excel files, and started using the magic of PowerQueries and PowerPivot. Background: I have 2 files: - First one is a "master" with all sales and production logs, and everything works inside that excel file with Power queries to tables stored in that same file. - Second one is mostly a different set of data about continuous improvement data, but i'd like to start linking them with the master file by having charts that compare efficiency to production, etc.

As it is now, I am using links by entering a direct reference to the cells/ranges in the master file (i.e: [Master.xlsm]!$A1:B2) However, every new version of the Master file, I have to update the links and it's not scalable if I have more documents in the future.

Options: - Is it possible to store all the queries or data from the Master files in a separate file in the same folder and "call" for it when needed either in my Sales/Production master file or the Manufacturing file? That could be a database or connection file that has the queries to the data stored in the master file. - If not, what is the best way to connect my Manufacturing file to my Master file without entering specifically the filename?

My fear is that as soon as the Master file name will change (date, version), I will have to navigate inside the queries and fix all the links again. Additionally, I wanna make this futureproof early one as I plan to gather large amounts of data and start more measurements.

Thanks for your help!

Solution

  • Once you have a data model built, you can create a connection to it from other Excel files. If you are looking for a visible way to control the source path of the connected file, you can add a named range to the Excel file that is connecting to the data model, and in the named range, enter the file path. In Power Query, add a new query that returns your named range (the file path), and swap out the static file path in your queries with the new named range query.

    Here is a sample M code that gets the contents of a named range. This query is named "folderPath_filesToBeAudited".

    let
    Source = Excel.CurrentWorkbook(){[Name="folderPath_filesToBeAudited"]}[Content]{0}[Column1]
in
    Source

    Here is an example of M code showing how to use the new query to reference the file path.

    Folder.Files(folderPath_filesToBeAudited)

    Here is a step-by-step article. https://accessanalytic.com.au/powerquery_namedcells_parameters/