Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook


Change the Power Query source based on a cell value

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.

Download the example file

I recommend you download the example file for this post.  Then you’ll be able to work along with examples and see the solution in action, plus the file will be useful for future reference.

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

Download Icon
Download the file: 0021 Power Query – Change source based on cell value.zip

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.


Headshot Round

About the author

Hey, I’m Mark, and I run Excel Off The Grid.

My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.

In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).


Do you need help adapting this post to your needs?

I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.

But, if you're still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
  2. Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise.  List all the things you've tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.

What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

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

  1. JM says:

    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),

  2. Kym Crettenden says:

    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?

  3. Martin R says:

    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

  4. ANKIT says:

    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),

  5. Andy Z says:

    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.

  6. Jesper says:

    Thank you – great post! Appreciate your very thoroughly step-by-step, works great and helped me a lot.

  7. Isaiah says:

    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.

  8. Venus says:

    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?

  9. Rupert says:

    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”

  10. Colin Harrison says:

    Excellent resource! Exactly what I needed. Thanks! (Modified the edit slightly to get it to grab a different sheet from the same source file based on a cell value, but the logic is the same).

  11. Justin - Pretty Good Konsult AB says:

    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

  12. Joseph says:

    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?

  13. Brandon says:

    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.

Leave a Reply

Your email address will not be published. Required fields are marked *