I have been working with SharePoint a lot these days. A LOT!!!
One thing that frustrates me all the time is that there is no easy way to connect to a single SharePoint file from Power Query. Until now, that is.
The other day I learned a handy little trick from Jo (my beautiful and insanely talented wife). So here we go.
Problem: Power Query doesn’t recognize SharePoint file paths
I have a file in SharePoint (as shown below) and want to link it to Power Query (either in Excel or in Power BI).
When I use the Share > Copy Link approach I can’t get the file to open thru Power Query. You can see the Unable to Connect error message when I try this option below:
Old Solution – Long and tedious
So the normal fix for this has been,
- Open a SharePoint Folder connection
- In Power Query filter down to the single file I need
- Apply any transformations
- Finally load
New Solution – Short & Sweet
So this is the trick Jo told me.
- Click on the ? (three dots menu) next to your file in SharePoint
- Go to Details
- This opens a panel on the right-hand side of the screen
- Scroll down and locate the “path” and copy the path
- Now use this path in Power Query (from web option)
- BOOM! You get the data straight away.
Here is a quick demo of the solution:
Learn more about Power Query…
We can use Power Query to connect and get all sorts of data into either Excel or Power BI. If you are new to Power Query or need a proper introduction, check either of below resources.
10 Responses to “How to connect to a SINGLE file on SharePoint from Power Query (Fix Unable to Connect Error)”
Exactly what was needed and what I could not find! Thank you mate, great as all other things you are sharing!!!
Chandoo!!! You are the best!!!!!! Thank you for another great tip!!!
Thank you - this is one of the most ridiculous things I've ever seen, given that it's not docmented by Microsoft.
This doesn't work! It only connects power query to your local files on your PC. What if I share the excel file with a co-worker? Then the path won't work, as it is looking to their local pc for the files. The entire point of using sharepoint is so that if I share the file, the associated data files should be accessible to anyone who downloads the excel file.
I works for me. Can you check if the path is correct. You must save the file on SharePoint first before copying the URL.
It works!! Thanks!
Hey, the solution works for files. But it doesn't work for sharepoint folders. I always get this error "we couldn't authenticate with the credentials provided power query"
Is there any solution?
Do you get the error when you try to connect to sharepoint folder normally? That error could mean you are outside the network when you are trying to refresh or your login has expired.
Perfect!
Your wife is a genius (but I guess you already know this! 🙂 )