How to connect to a SINGLE file on SharePoint from Power Query (Fix Unable to Connect Error)

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.

Sharepoint to Power Query - Howto?

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

Sharepoint files
My Excel file in the SharePoint site

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:

Unable to connect error in Power Query when linking to Sharepoint files

Old Solution – Long and tedious

So the normal fix for this has been,

  1. Open a SharePoint Folder connection
  2. In Power Query filter down to the single file I need
  3. Apply any transformations
  4. Finally load

New Solution – Short & Sweet

So this is the trick Jo told me.

fix to unable to connect error - sharepoint to power query
Fix for Unable to Connect Error – SharePoint to Power Query
  1. Click on the ? (three dots menu) next to your file in SharePoint
  2. Go to Details
  3. This opens a panel on the right-hand side of the screen
  4. Scroll down and locate the “path” and copy the path
  5. Now use this path in Power Query (from web option)
  6. BOOM! You get the data straight away.

Here is a quick demo of the solution:

demo of the sharepoint connect (with fix)

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 Tutorial – What is it, How to use, Full examples, Tips & Tricks

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 – Learn Excel, Power BI & Charting Online.

Original source:

Close Menu