• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Changing Data Source Location in Power Query

You are here: Home / Power Query / Changing Data Source Location in Power Query
changing power query data source settings
December 16, 2020 by Philip Treacy

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

query load file from pc

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

data source setting excel

In Power BI Desktop

data source setting pbi

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

data source settings file

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

change file data source settings

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

query to load folder

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

folder data source settings

then click on Change Source, and enter the Folder Path

change folder data source settings

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

query load file

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.

steps to load table from file

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

file on onedrive

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.

onedrive root url

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.

url for file

%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

full file url

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

source local file

to this

source onedrive

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.

edit credentials

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

creds for onedrive

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

query load folder from pc

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.

table from folder query

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

moth sharepoint site

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.

folder on sharepoint

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

get data sharepoint excel

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

get data sharepoint pbi

Enter the URL for the Sharepoint root

sharepoint root folder

Sign in with your Microsoft or Organization account if required

sign in to sharepoint

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

all sharepoint files

Click on Transform Data

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

sharepoint file list

So filter the Folder path column to only include that folder

filter files

filtered files

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.

modify query to access sharepoint

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

changing power query data source settings

More Power Query Posts

Power Query if Statements incl. Nested ifs, if or, if and

How to write Power Query if statements, including nested if, ‘if or’ and ‘if and’, which are easier to write than their Excel counterparts.
power query variables

Power Query Variables 3 Ways

Power Query Variables enable you to create parameters that can be used repeatedly and they’re easily updated as they’re stored in one place.
delete empty rows and columns using power query

Remove Blank Rows and Columns from Tables in Power Query

Delete blank rows and columns from tables using Power Query. Even rows/columns with spaces, empty strings or non-printing whitespace
extracting data from lists and records in power query

Extracting Data from Nested Lists and Records in Power Query

Learn how to extract data from lists and records in Power Query, including examples where these data structures are nested inside each other.
combine files with different column names in power query

Combine Files With Different Column Names in Power Query

Learn how to load data into Power Query when the column names in your data don't match up. Sampe files to download.
power query keyboard shortcuts

Power Query Keyboard Shortcuts to Save Time

Time saving keyboard shortcuts for Power Query that work in both Excel and Power BI. Download the free Shortcuts eBook
remove text between delimiters power query

Remove Text Between Delimiters – Power Query

Remove all occurrences of text between delimiters. There's no in-built Power Query function to do this, but this code does.
power query advanced editor tips

Tips for Using The Power Query Advanced Editor

Tips for using the Power Query Advanced Editor in Excel and Power BI. Watch the video to see these tips in action
pivot unknown variable number of rows to columns

Pivot an Unknown Number of Rows into Columns

How do you pivot rows to columns when you don't know how many rows you're dealing with? It's not as easy as you may think.
try otherwise power query iferror

IFERROR in Power Query Using TRY OTHERWISE

Using TRY..OTHERWISE in Power Query Replicates Excel's IFERROR So You Can Trap and Manage Errors In Your Queries.


Category: Power Query
Previous Post:stacked bar waffle chartStacked Bar Excel Waffle Charts
Next Post:Table Statistics from Table.Profile in Power Querytable.profile statistics in power query

Reader Interactions

Comments

  1. Timo

    June 21, 2022 at 6:40 pm

    How this would be done if the source data from a single local Excel file is imported into a database in Azure (into similar tables which exist in the Excel) and then we want to change the PowerBI to read data from this database instead of the original Excel?

    Reply
    • Mynda Treacy

      June 21, 2022 at 6:48 pm

      You’d be best to recreate the query connections to the new file location and then copy in the steps from the original query after the source step.

      Reply
  2. John Grace

    July 4, 2021 at 1:44 am

    I have gone into advanced editor for the sample file source and made similar changes and this is now a complete solution for me. Sorry if I’ve wasted anyone’s time.

    Reply
    • Mynda Treacy

      July 4, 2021 at 12:31 pm

      Glad you got it working, Grace.

      Reply
  3. John Grace

    July 4, 2021 at 12:48 am

    Done all of the above for pointing to drive folder to SharePoint folder and it works fine until I change the name of the drive folder because the sample queries are still pointing to the drive folder. What am I missing?

    Reply
  4. Jay

    December 29, 2020 at 3:50 am

    It would seem the Sharepoint / Onedrive URL’s change based on the user. I have some workbooks that I want to migrate the data source to the cloud but the data source links would break when opened by another user. Any ideas?

    Reply
    • Catalin Bombea

      December 30, 2020 at 7:49 pm

      Hi Jay,
      My guess is that depends on the type of the link you create for those folders. Are they shared to a specific person, are they public, with a link that provides access to anyone that has the link?
      If you create a link that is designed to allow one person, you can’t give the link to someone else. Well, you can, but will not work.
      Create a group of users, and create a sharable link for that group only.

      Reply
  5. Jan Blomberg

    December 17, 2020 at 6:21 pm

    Great post! This was exactly what I was looking for. Especially the part on getting the work online to Sharepoint. All the best and happy holidays..

    Reply
    • Philip Treacy

      December 18, 2020 at 10:07 am

      Glad to help Jan 🙂

      Merry Christmas.

      Phil

      Reply

Leave a Reply Cancel reply

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

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.