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