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:
- Source file – the data loaded into Power Query
- Start file for the example
- Completed file for the example
Download the file: 0021 Power Query – Change source based on cell value.zip
Watch the video:
Contents
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:
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.
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.
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.
Double click the name of the query to open the Query Editor.
]
In the Power Query editor, click View -> Advanced Editor.
Reference named ranges in M code
We will add new steps directly after the let statement.
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
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),
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.
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:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
- 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.
- 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:
Great page!
Thanks Ed 🙂
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),
Thanks JM – Great add.
Thank’s for the article, very usefull.
OMG I have been looking for this for ages… THANK YOU
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?
It’s possible. But would need to be coded specifically to your circumstances. It will depend on exactly which cells and in what order and how you decide what a change is.
If you don’t know how to do it, I would recommend a service like Excel Rescue (https://excelrescue.net/works-with/exceloffthegrid)
how do i specify the Sheet?
Hi Patrick – The sheet is defined in the Named Range (i.e., it refers to a specific range on a specific sheet).
Can you elaborate on this/show an example?
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
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
Thank you for your reply and the tips.
In the meantime I found out in other posts (eg https://www.excelguru.ca/blog/2019/07/17/power-query-intellisense-and-highlighting-in-excel/) that intellisense is (at the moment) only for Excel 365, not for 2016 nor for 2019 🙁
I know the VBA Sub Worksheet_Change(ByVal Target As Range). Just thought that there would be a built in functionality as in MS Query / Parameter from Cell value
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),
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.
Hi Andy – I’m not sure why you need to use curley brackets in the VBA code. Once the link is set-up it should be a simple refresh process.
Check out this post, it shows how to auto-refresh the Power Query source when a cell value changes.
https://exceloffthegrid.com/auto-refresh-power-query/
Thank you – great post! Appreciate your very thoroughly step-by-step, works great and helped me a lot.
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.
Thank you! And how about a source file is a folder or a txt/csv file?
The principles are exactly the same, no matter what the source is.
Same principles and methods apply
Excellent!! Thank you
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?
I found my error. Thank You!
Good news 😊
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”
Thanks Rupert, that is really useful stuff. 🙂
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).
Yay!! Great News. I’m glad it helped 😀
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
Dropbox as a source, I’ve not tried it. That’s useful to know 😀
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?
Sorry, I’ve never used it on a Mac. You might want to try the Microsoft Answers community.
This is a life saver. Thank you Mark! <3
You’re welcome 😁
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.