Changing Data Source Location in Power Query

Here's the scenario - you've created a query in Power Query that loads data from a source, either a file or a folder. You then move that data source, so how do you change the query to load data from the new location?

Loading a Single Excel Workbook

If you are loading data from a file with a query like this

and then move the file to another folder or change the filename, you just need to change the path/filename in the Source step.

This is really easy to do. Click on the Data Source Settings

In Excel

In Power BI Desktop

Click on the data source then click on the Change Source button

Either browse to the new folder/file or enter it directly, then click OK.

Loading Files from a Folder

If you are loading multiple files from a folder on your PC the process is very similar.

You'll have a query like this

Again you just need to change the Source step by clicking on the Data Source Settings icon on the Ribbon in Excel or Power BI Desktop

Click on the folder data source

then click on Change Source, and enter the Folder Path

In both of these cases you can also modify the query directly in the Advanced Editor by changing the file and/or folder path.

Moving Files Into the Cloud

Another common scenario is where you've created some queries with files on your PC and you then move the files either to OneDrive for Business or Sharepoint Online.

The changes to be made here are still fairly straight forward but because the files are being moved to the cloud, we also have to change the connector being used in the queries.

Moving a File to OneDrive for Business

We've already seen that the query to load this Excel file from my PC is

The Source step loads the data from the file and the steps after that do the transformations.

Looking at the query in the editor you can see that after the Source step, I end up with this 5 column table.

The transformations that begin with the Expanded Data step will be the same whether I'm loading the file from my PC or OneDrive. What I need to do is change the source loading step so that after loading the file from OneDrive, I end up with this same 5 column table. The transformations can then do their work as they have the same data (the 5 column table) to work on.

I've moved the file to OneDrive into a folder called Sales

I need to know how to access this file and I do this by looking in my browser's address bar. In the image below I've highlighted in red the important part that I need. This is the first part of the URL I need to use in Power Query.

Clicking into the Sales folder to see my file, the URL changes. At the end of the URL I can now see %2FDocuments%2FSales indicating that this is the folder I am currently browsing.

%2F is HTML code for a forward slash so the end of the URL can also be read as /Documents/Sales

I have everything I need now to access the file in Power Query. The full URL to the file is

Back in Power Query, select the query that loads the file from the PC and then open the Advanced Editor.

All I need to do is change this line

to this

Click on Done to save the change and then refresh the query.

If you're prompted to enter credentials to let Power Query know how to connect to OneDrive.

Click on Edit Credentials, choose Organizational Account then click Sign In and enter your username and password if required, then click on Connect.

The process is similar to change location for a text/CSV file.

Loading Files from a Folder

I'm loading several Excel workbooks from my PC with this query

After the Source step I have this table - it's 8 columns wide but I've chopped out the middle to make it fit the screen.

All transformation steps act on this table so after moving the files elsewhere, I need to create a new query that gives me the same table, so my transformation steps do not need to be altered.

I have just moved these files to one of our company Sharepoint sites. Our Sharepoint looks like this

I've moved the files into the MOTH Team Site, into a folder called World Domination. Perhaps the folder name gives away my secret scheme.

To load these files I just need the root URL for our Sharepoint which is https://365moth.sharepoint.com/

In Power Query (in Excel), click Get Data -> From File -> From Sharepoint Folder

In Power BI you have to click Get Data -> More then scroll through the list or search for Sharepoint Folder

Enter the URL for the Sharepoint root

Sign in with your Microsoft or Organization account if required

You'll then be presented with a list of all the files on your Sharepoint

Click on Transform Data

Now inside the PQ editor, I only want the files in my World Domination folder

So filter the Folder path column to only include that folder

This gives me a table with the same structure as I had when loading these files from my PC.

Loading the files from Sharepoint and creating this table was done in 2 steps, loading the source and filtering the folder path.

What I need to do now is take these steps from this query and insert them into the query that does all my transformations.

After opening the query that loads the files from my PC, I replace the Source step with the 2 steps from the query I just created.

I need to make one other change which is to modify the #"Removed Other Columns" step. Originally it was referencing the Source step, but because I had to filter out some rows when loading from Sharepoint, I've now got a #"Filtered Rows" step after the Source step.

I just need to change the reference from Source to #"Filtered Rows" in the 3rd step. I've commented out, but left the original step in, so you can see the change that was made.

Nothing else needs changing so the query can be saved and my files should now load from Sharepoint.