You’ve built some queries in a test environment, and now it’s time to start using them for real. How can you change the queries to point to the live source, rather than the test? In this post, we will answer this very question.
As like most things in Excel, there are multiple ways to achieve this:
- Change the location in the query M code
- Use the Data source settings
- Using parameters
Without further ado, let’s get started.
Change the source location in the query
The first applied step in most queries is to identify the source data. Technically, it doesn’t have to be, and it doesn’t have to be called Source either. However, in most cases, it will be. Being first makes the M code easy to find; as it’s always at the start 😉
The screenshot below shows the first step in the query with the M code displayed in the Formula Bar. If the Formula Bar is not visible you can click View -> Formula Bar in the Power Query menu.
We can edit the code directly in the Formula Bar, we just need to provide the new file location as a text string.
We also have the option of editing the code in the Advanced Editor, click Home -> Advanced Editor or View -> Advanced Editor (see the screenshot below).
Thirdly, clicking on the gear icon next to the Source step within the Applied Steps pane will open the source dialog. In this example, an Excel Workbook is used as the source, so click Browse to navigate to the new file location, then click OK.
Depending on the source type, the dialog box will change, but the principles are the same.
We’ve seen the technique of changing the Source already in this Power Query series when we imported from the web.
Use the Data Source settings
Where there are multiple queries all coming from the same source, there is a better way to repoint to the source data; the Data Source settings. In fact, this may be a better way in general.
In the Power Query editor click Home -> Data Source settings
In the Data Source settings window, select the source we want to change, then click the Change Source... button.
This will bring up the same window as clicking the gear icon (as we saw earlier).
However, there is a crucial difference; rather than changing the source for one single query, it changes the source for all the queries using that as the source.
The Data Source settings are available in other locations in Excel and Power Query, but I find this the easiest to remember.
If you know the source data location will be changing, then a good option is to create a Parameter. A cell on the worksheet can contain a text string with the location of the data source. This text string could be the folder path, file name, table name or a SharePoint web address, etc. To repoint the query it is as easy as changing the text string.
We’ve seen many times that Power Query hardcodes text values into the M code, so using parameters is a good flexible approach. As we have already covered Power Query parameters in another post, I won’t repeat the process here, go read the post.
Power Query Series Contents
- Import Data
- Data Refresh
- Edit Queries
- Close & Load Options
- Using Parameters
- Basic Transformations
- Combine / Append Queries
- Import All Files in a Folder
- List All the Files in a Folder and File Attributes
- Import Data from the Current Workbook
- Import Data from the Web
- Unpivot Data
- Unstacking Data in a Column
- Lookup Values using Merge
- Change the Source Data Location
- If Statements for Conditional Logic
- Grouping and Summarizing Data
- Custom Functions
- Common Errors & How to Fix Them
- Tips and Tricks
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.
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:
- 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.
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: