Change the Power Query source based on a cell value

by

If you’re like me, you build Power Query solutions in a test environment first; then,  when ready, it’s released into the wild.  This means the paths of all source files must be updated.  Or maybe you’re a consultant who creates solutions for others; when you distribute the workbook to your client, you have to provide them with complicated instructions on how to update the data sources for their environment.  Wouldn’t it be better if the source could be linked to a cell value that we, or a client, could easily update? Yes, it would! So, in this post, we’re going to look at how to do that; how to change the Power Query source based on a cell value.

I have written about changing Power Query sources before (Power Query: Change the Source Data Location).  In that post, I show the manual method and a method using parameters.  What I’m about to show you is an even easier method.

I must say “Thank You” to Celia Alves for sharing this technique on a recent online meetup.

Table of Contents

Download the example file: Join the free Insiders Program and gain access to the example file used for this post.

File name: 0021 Power Query – Change source based on cell value.zip

Get access

The download file includes three files:

  1. Source file – the data loaded into Power Query
  2. Start file for the example
  3. Completed file for the example

Watch the video:

Watch the video on YouTube

The scenario

I’m not going to provide details on how to load the source data into Power Query; I’m assuming you already know how to do that.

If you’re using the example files, the data loaded from the source file looks like this:

Start Scenario

Cell C2 contains the file name, and cell C3 contains the folder path.  These are not currently linked into anything; it is presently just text in a cell.  But by the end of this post, these cells will be connected to Power Query.

These are file locations on my PC, so will not work for you.  Change the file name and folder path to the location of your source file.

NOTE: A common error is to forget the backslash at the end of the folder path.

Create named ranges

Now, create two named ranges:

Select cell C2 (the file name) and create a named range called FileName.  To do this type the name of the named range into the name box and press Enter.

Create Named range for File Name

Next, we repeat the step above for the file path.  Select cell C3 (the file path) and create a named range called FilePath.

We will be combining these two named ranges in Power Query’s M code.  So, make sure they form a valid file path.

Use named range within M Code

Now we just need to use the named ranges in our query.

Open the Queries and Connections pane by clicking Data -> Queries & Connections.

Data Ribbon - Queries & Connections

Double click the name of the query to open the Query Editor.

Double-click the Queries & Connections Pane]

In the Power Query editor, click View -> Advanced Editor.

View Ribbon - Advanced Editor

Reference named ranges in M code

We will add new steps directly after the let statement.

Advanced Editor with FilePaths added
FilePath = Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1],
FileName = Excel.CurrentWorkbook(){[Name="FileName"]}[Content]{0}[Column1],

These lines of M code are effectively the same,  but one is for the file path and one for the file name.  The code breakdown for the first line is as follows:

  • FilePath = : The name of the step in Power Query
  • Excel.CurrentWorkbook() : Power Query function which uses data from the current workbook
  • {[Name=”FilePath”]} : The name of the named range
  • [Content]{0}[Column1] : Uses the first row and the first column from the table of the named range

REMEMBER: Make sure each line of code in the Advanced Editor is finished with a comma.

In this example, we have two named ranges, but we can create as many as we need.

Use named ranges as source

Finally, we need to insert the FilePath and FileName steps into the Source step.

Replace this:

Source = Excel.Workbook(File.Contents("C:\Users\marks\OneDrive\Documents\SourceFile.xlsx"), null, true),

with this:

Source = Excel.Workbook(File.Contents(FilePath & FileName), null, true),
Using new variables in M Code

Click Done to close the Query Editor.  Then Close and Load the data back into Excel.

Assuming we’ve done everything correctly, the data in the query is now linked to the cells.  We can change the values in cells C2 and C3, then click refresh and Ta-dah!  The data will refresh to the new source file.  We’ve managed to change the Power Query source based on a cell value.

Make the file path dynamic (not for OneDrive)

If the source files are contained in the same folder as the workbook containing the query, it is possible to use a dynamic file path.  Try entering the following into cell C3.

=LEFT(CELL("filename",$A$1),FIND("[",CELL("filename",$A$1),1)-1)

The formula will automatically update to show the correct file path.

Notes:

  • The workbook containing the query must be saved
  • Workbooks saved to OneDrive will display the https URL, rather than the local file path, making this method difficult to use without further development

Conclusion

That’s it, we’ve learned how to change the Power Query source based on a cell value.  All it takes is a few named ranges, and a few short lines of M code.

Now any user can easily change the cell to re-point the query to the correct source data location.  They don’t even need to open Power Query, or understand how it works.

What next?

Discover how you can automate your work with our Excel courses and tools.

The Excel Academy

Excel Academy

Make working late a thing of the past.

The Excel Academy is Excel training for professionals who want to save time.

Find out more

40 thoughts on “Change the Power Query source based on a cell value”

  2. Thank you !
    I could make it work for a file on SharePoint, by replacing “File.Contents” with “Web.Contents” in the Source, as below

    Source = Excel.Workbook(Web.Contents(FilePath & FileName), null, true),

    Reply

  5. Thank you for the article; very useful. I have seen where a “Refresh all” can update the query automatically with a macro running on change of a cell value, but looking to only do this once data input is completed in more than one cell to then the macro to update – and not update on when each cell changes.
    Is this possible?

    Reply

  7. super introduction to power query! this has helped me a lot
    2 supplementary questions:
    – in the video at 3’50” the advanced editor appears with text highlighting and hints, similar to notepad++. any tips on how you implement this within power query? (I defined within my NP++ the language M as UDL).
    – in MS Query you can define when the cell value for the query parameter changes that the query is refreshed. any tips how to realize this with PowerQuery (VBA in Excel Workbook)?
    thanks a lot

    Reply

    • Hi Martin,

      The formatting of the editor is dependent on which version of Excel you’ve got. I can’t recall which build version this feature was released on.

      In terms of automatically refreshing a PowerQuery, you will need to use the worksheet change event in VBA to trigger the refresh. I don’t have any content which specifically addresses this, the closest content I have is this video: https://www.youtube.com/watch?v=rDkWIDU7_Fo

      Reply

  8. Hi,

    Thank you for the tip. Made changes to get all files from a folder, working for me.

    FILEPATH = Excel.CurrentWorkbook(){[Name=”FILEPATH”]}[Content]{0}[Column1],

    Source = Folder.Files(FILEPATH),

    Reply

  9. Hi – can you describe how one might update this to run as a programmed macro tied to a button click? The vba seems to be having challenges with the curly bracket? Thanks very much.

    Reply

  11. Dear Mark,
    Thanks the brilliant and clear article. It helps a lot.
    Preciously I had tried the following idea:
    1) create a query (FileLocation) to drill down a table in the excel file as
    C:\PQ\sample.xlsx
    2) modify M code to
    Source = Excel.Workbook(File.Contents(FileLocation), null, true)

    But as you might have known, this trick did not work. Do you know what went wrong? And is it possible to fix it directly in M code?
    Thanks again for sharing your valuable knowledge.

    Reply

  14. You mentioned as many ranges can be used as needed. I tried pulling in multiple files from the same file path using a different file name and named range, but am having trouble. How would i apply this to bring in multiple files?

    Reply

  16. Brillaint. Been searching for so long to find this. Coupled this with another query which invokes the user name to replace the user in the path (we use Dropbox). So the FieldPath is automatically updated with the right user name upon ‘Refresh All’ so any user (provided they use the same drive letter for their Dropbox) can refresh. Found this which can be pasted directly into a new query. Might be useful:

    () =>
    let
    Source = Folder.Contents(“C:\Users\”),
    #”Expanded Attributes” = Table.ExpandRecordColumn(Source, “Attributes”, {“Hidden”, “Directory”, “ChangeTime”}, {“Hidden”, “Directory”, “ChangeTime”}),
    #”Filtered Directories not hidden” = Table.SelectRows(#”Expanded Attributes”, each ([Directory] = true) and ([Hidden] = false)),
    #”Removed Errors” = Table.RemoveRowsWithErrors(#”Filtered Directories not hidden”, {“ChangeTime”}),
    #”Filtered Rows” = Table.SelectRows(#”Removed Errors”, each ([Name] “Public”)),
    #”Username” = #”Filtered Rows”{0}[Name]
    in
    #”Username”

    Reply

  18. Thanks for this post. This did the trick for me with some modifications. I am sourcing a file on Dropbox, and the filepath is dynamic based on the user.

    1. Remove the space between FilePath & File Name in below line:
    Source = Excel.Workbook(File.Contents(FilePath & FileName), null, true),

    2. Populated FilePath with expression to make dynamic:
    =@fn_directory&”\”
    This

    Reply

  19. I am using Excel for Mac and when I get to the step where I press “Close and Load,” I receive the following message: “An on-premises data gateway is required to connect.”

    Any ideas on how I can overcome this?

    Reply

  21. I am getting an error
    DataFormat.Error: The supplied file path must be a valid absolute path.

    Can you help? I have a url string for example
    I:\Folder1\Folder2\Folder3\ExcelFile.xlsx

    In this example, Folder2 changes via formula
    All the references and url is correct, I just keep getting that error.

    Reply

  22. I’ve made the FileName and FilePath dynamic so I don’t have to copy and paste each time I add the connection to a new file!

    FileName
    =TEXTAFTER(SUBSTITUTE(TEXTBEFORE(CELL(“filename”,A1),”]”),”[“,””),”\”,-1)

    FilePath
    =TEXTBEFORE(SUBSTITUTE(TEXTBEFORE(CELL(“filename”,A1),”]”),”[“,””),”\”,-1)&”\”

    There’s probably a better way to capture the final slash in the path, but this works for now.

    RE: I’m creating a PQTOC that I can use in any file

    Reply

Leave a Comment