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 files which support this post, as you’ll be able to work along with examples.  This is the best way to learn.  You’ll be able to see the solutions in action, plus the file will be useful for future reference.  The support files are available for FREE to newsletter subscribers.

Click below to subscribe and gain access to the subscriber area.  You will also receive:

  • My favorite tips and tricks direct to your inbox
  • Exclusive content (which is only available to subscribers)
  • FREE tools and downloads

Download Icon

If you’re already a subscriber, click here to log-in to the subscriber downloads area.

The filename for this post is 0020 Interpolate with Excel.xlsx.  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

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.

Download the 100 Excel Macros ebook

100 Excel Macros Book

  • Contains 100 Excel VBA macros
  • Learn VBA by following along with the example codes
  • Apply to your macros, automate Excel, save time.

Download the ebook today!

Click the button below to subscribe, you’ll gain access to the subscriber area in which you can download the ebook.

Download Icon (on mid-green background)

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.

Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet 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:

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

Leave a Reply

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