This post may contain affiliate links. Please read my disclosure for more info.

Power Query – Change the Source Data Location

Power Query - Change Source Data Location

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.

Change Source Formula Bar

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

Change Source - Advanced Editor

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.

Change Source - Gear Icon

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

Home - Data Source settings

In the Data Source settings window, select the source we want to change, then click the Change Source... button.

Data source settings window

This will bring up the same window as clicking the gear icon (as we saw earlier).

Change Source - Gear Icon

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.

Using parameters

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.

Series Contents

  1. Introduction
  2. Import Data
  3. Data Refresh
  4. Edit Queries
  5. Close & Load Options
  6. Using Parameters
  7. Basic Transformations
  8. Combine / Append Queries
  9. Import All Files in a Folder
  10. List All the Files in a Folder and File Attributes
  11. Import Data from the Current Workbook
  12. Import Data from the Web
  13. Unpivot Data
  14. Unstacking Data in a Column
  15. Lookup Values using Merge
  16. Change the Source Data Location
  17. Formulas
  18. If Statements for Conditional Logic

Want to Learn VBA & Macros?

If you want to automate Excel and save time, my recommended resouces for learning VBA Macros are:

Leave a Reply

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