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.
Power Query Article
Power Query Videos
The post How to connect to a SINGLE file on SharePoint from Power Query (Fix Unable to Connect Error) appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.
source https://chandoo.org/wp/connect-sharepoint-files-from-powerquery/
0 Comments