How to change source data location in Power Query (7 ways)

Maybe you’ve built some queries in a test environment, and now it’s time to start using them for real. The obvious question arises: How to change source data location in Power Query?

Or, maybe you’ve linked to a workbook/CSV file that updates every month, so you need to update the data source. Or, perhaps you’re a consultant who needs to pass a Power Query based workbook to a client, and you want to provide an easy method to configure it to their environment.

These are all scenarios where we need to change the source data in Power Query. This post supplies methods to help you achieve this.

As with most things in Excel, there are multiple ways to do this. While each option provides the same result, each fits your workflow differently. Make sure you read to the end to see all the possibilities.

Without further ado, let’s get started.

Table of Contents

Watch the video

7 METHODS to change source data location in Power Query | Excel Off The Grid

Watch the video on YouTube

Edit the Source step in the applied steps window [#1]

The first step in most queries identifies the source data. Technically, it doesn’t have to be, and it doesn’t have to be called Source either. However, in most cases, Source will be first, so we will work on this assumption.

Click on the gear icon next to the Source step within the Applied Steps box.

The source dialog box opens. Click Browse to navigate to the new file location, then click OK.

Click on gear icon in applied steps window

The query now points to the new source data location.

In this example, an Excel Workbook is the source. Depending on the source type, the dialog box appearance will differ, but the principles will be the same.

Change the M code in the formula bar [#2]

The source file’s location is included in the M code for the step. For example, the screenshot below shows the first step in the query with the M code displayed in the Formula Bar. 

Change data source in the formula bar

If the Formula Bar is not visible, click View > Formula Bar in the Power Query menu.

We can edit the code directly in the Formula Bar. We just update the text string with the new file location or other information as required.

Change the M code in the advanced editor [#3]

This method is a similar solution to Method #2.

We edit the code in the Advanced Editor, click Home > Advanced Editor (or View > Advanced Editor).

Edit the code in the Advanced Editor dialog box, then click Done.

Change source in the advanced editor

Use the Data Source settings [#4]

When multiple queries come from the same source, there is a better way; using the Data Source settings. This method repoints all queries using that source to the new source.

In the Power Query editor, click Home > Data Source settings

Home - Data source settings

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

Change source in the data source settings dialog box

This brings up the same window as clicking the gear icon in the applied steps window. After changing the source path, we click OK to change all queries using that source.

Note: The Data Source settings are available in other locations in Excel and Power Query, but I find this the easiest to remember.

Using worksheet cell parameters [#5]

If you know source data locations will change, a good option is to create parameters. Check out this post to find out more about parameters in Power Query.

A cell on the worksheet can contain a text string with the location of the data source. Repointing the query is as easy as changing the text string in the cell. However, the challenge is finding where that cell is.

Due to the Formula.Firewall error, it is likely the Source will not be the first step (that is not guaranteed though). In the Applied Steps window, look for code that references [Name=”?????”]. This tells us that there is a Named Range or Table on the worksheet called FilePath containing the data source location.

Worksheet cell as parameter

Back in Excel, we find the Named Range or Table in the Name box. Selecting the names takes us to the cell we need to change.

Named Range on a worksheet

Using queries as parameters [#6]

If the code references a query with a text data type, the data source is likely to be the result of another query (see the example screenshot below)

Parameter Query

To update the source, we edit the referenced query.

Parameter Query as separate query

Using Power Query parameters [#7]

Finally, the data source can be referenced inside an official Power Query parameter.

Power Query Parameter

To change the data source for this parameter, click Home > Manage Parameters.

Make changes to reference the new source data inside the Manage Parameters dialog box, then click OK.

Manage Parameters

These parameters are used for special purposes inside Power Query. Find out more about this here.


Power Query is a very flexible tool that provides many options for handling data sources. In this post, we have seen 7 approaches to change data source location. However, even this list is not exhaustive; other methods, such as functions or manual data entry exist. But I believe these are the scenarios we are most likely to face.

Read more posts in this series

  1. Introduction to Power Query
  2. Get data into Power Query – 5 common data sources
  3. DataRefresh Power Query in Excel: 4 ways  &  advanced options
  4. Use the Power Query editor to update queries
  5. Get to know Power Query Close & Load options
  6. Power Query Parameters: 3 methods
  7. Common Power Query transformations (50+ powerful transformations explained)
  8. Power Query Append: Quickly combine many queries into 1
  9. Get data from folder in Power Query: combine files quickly
  10. List files in a folder & subfolders with Power Query
  11. How to get data from the Current Workbook with Power Query
  12. How to unpivot in Excel using Power Query (3 ways)
  13. Power Query: Lookup value in another table with merge
  14. How to change source data location in Power Query (7 ways)
  15. Power Query formulas (how to use them and pitfalls to avoid)
  16. Power Query If statement: nested ifs & multiple conditions
  17. How to use Power Query Group By to summarize data
  18. How to use Power Query Custom Functions
  19. Power Query – Common Errors & How to Fix Them
  20. Power Query – Tips and Tricks

Discover how you can automate your work with our Excel courses and tools.

Excel Academy

Excel Academy
The complete program for saving time by automating Excel.

Excel Automation Secrets

Excel Automation Secrets
Discover the 7-step framework for automating Excel.

Office Scripts Course

Office Scripts: Automate Excel Everywhere
Start using Office Scripts and Power Automate to automate Excel in new ways.

Leave a Comment