fbpx
Search
Close this search box.

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

Share

Facebook
Twitter
LinkedIn

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

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

  1. Viktor says:

    Exactly what was needed and what I could not find! Thank you mate, great as all other things you are sharing!!!

  2. Paula says:

    Chandoo!!! You are the best!!!!!! Thank you for another great tip!!!

  3. Alex DC says:

    Thank you - this is one of the most ridiculous things I've ever seen, given that it's not docmented by Microsoft.

  4. Brandon says:

    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.

  5. Rohit says:

    It works!! Thanks!

  6. Irfan says:

    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?

    • Chandoo says:

      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.

Leave a Reply