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.

Claim your free eBook

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.

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.

Claim your free eBook

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:

3 thoughts on “Power Query – Change the Source Data Location

  1. Vince says:

    I have a power query that is saved in a shared teams folder. Multiple people have access and can change the code. But while I don’t get a source error, the other two users do get an error, even though we all have access to the same folder. You can see the source is associated to me versus the more generic teams folder. Is there a way around this? As all 3 of us need to be able to make changes without having to change the source each time.

    • Excel Off The Grid says:

      I’m not sure about your specific scenario, as I’ve not encountered it myself. I’m guessing that for you the file path is a local file, while for the other users the file path is a URL to OneDrive or something similar.

      I think you will need to set-up the query to use the OneDrive version of the file. Then all users are accessing the same file.

      As I said, that’s just my guess.

    • Shertank360 says:

      I had a similar issue when working from a shared folder. For us, our shared drive was on a local services. If each person had their drive mapped differently, it would cause a source error.

      In our situation, the path had do be identically mapped to fix the issue.

Leave a Reply

Your email address will not be published.