Earlier this week I was delivering some Power Query training and someone asked me this question…
“I have an Excel that is pulling data from another Excel file that is stored on a network drive. But we’re moving from network drives to SharePoint. Can I easily connect to the file on SharePoint or do I need to recreate the connection from scratch?”
In this video I’ll show you a quick and easy fix that lets you reconnect your Power Query to SharePoint without losing any of your transformations.
To Retrieve the Path of the File on SharePoint
- Navigate to the SharePoint Folder
- Click the 3 dots to the right of the filename
- Select Details
- Scroll down the Details Panel on the right
- Click the icon next to Path to copy the file’s path
Edit the Query
- In Excel click Data > Get Data > Launch Power Query Editor
- Click the Source step on the right hand side of the screen
- Above the table of data is a Formula Bar
- Replace File.Contents with Web.Contents
- Replace the path to the file with the URL to the file (copied earlier)