Update September 2020: This post gets so many hits every day I decided to make a video. You should watch it. It is short.
I try to avoid using Excel as a data source when I can. Although Excel has many benefits, its limitations make it less than ideal for enterprise BI solutions. Despite this, it feels like I end up needing to analyze data in Excel at least once a month. And every time I’m connecting from Power Query to an Excel workbook located in SharePoint Online or OneDrive for Business, I find myself exclaiming “it shouldn’t be this hard!”
What’s the problem? It’s not working with the data – Power Query makes this as painless as you’d expect. The problem is referencing the XSLX file. What path do I use?
In Excel and in SharePoint, there is a “copy link” option, which will copy a link to the workbook so you can share it with others.
It always feels like this should work, that I should be able to paste this URL into the Get Data experience in Power Query, and if it’s been more than a few months since the last time I tried, I expect it to work. But it never does.
In the Excel ribbon, click on the File tab, and on the Info screen that appears click on the label under the file name. There’s no visual indicator that this label is a menu button until you hover over it, but when you click on it, a menu appears with a “Copy path to clipboard” option.
Update May 2020: Since this post was originally published, the Excel team has made this option a little easier to find. Your Excel options may look something like this:
Clicking “Copy path” will give you a path in this format:
With this starting point, all you need to do is remove the ?web=1 query string parameter at the end of the URL, and you have the path to the workbook that Power Query needs.
Choose “Web” as the data source in the Get Data menu, and paste this URL into the Dialog:
Power Query is smart enough to understand that this URL is pointing to an Excel workbook, so from this point on, you’ll get an experience where you can select tables and worksheets, and can start manipulating and analyzing your Excel data.
Life is good.
Full disclosure: Were it not for Power Query program manager Miguel Llopis, I never would have found this. During one of our dataflows sessions at Microsoft Ignite, Miguel spent a few minutes walking through the these steps at the beginning of a demo. My initial reaction was along the lines of “Why are you showing this? This isn’t a dataflows topic!” but when I realized what he was showing it quickly changed to “Holy crap – I didn’t know you could do that!”
In any event, I’m working with data in Excel again today, and spent a few minutes trying to find this technique documented anywhere. When I couldn’t find any, this post was born… but all credit should go to Miguel.
 None of which will I list here. It’s not that kind of blog.
 Neither will the limitations be enumerated. Feel free to take my word for it, or go build a data warehouse or two that extract data from Excel, and then come back and let me know what you think.
 To be fair, this may be another of those “get off my lawn” moments. It’s hard to tell sometimes.
17 thoughts on “Power Query: Connecting to Excel Workbooks in SharePoint Online”
This is why I would like to be able to use an Excelsheet uploaded in the Workbooks section of my App Workspace as a data source: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/36137743-add-the-ability-to-use-excel-files-in-workbooks-as
Connect to folder in sharpoint also works fine
Thank you for your post… such a simple fix… this solved a problem that had been haunting me for some time.
Outdated again, this isn’t working for me and the URL looks different now
It looks the same here on multiple up-to-date machines. If you’re hoping to help, a description of what you’re seeing would be a good starting point.
Anyone know how to handle “Access to resource is forbidden error” even if you have access to it?
Hi, I have problems traying to access from Excel 2016 (professional plus) to a workbook on sharepoint shared folder (Microsoft 365). I clean the credentials, I reinstalled the Office but I still have the same problem. Power Qwery cant sing in to share point. I just signed in with the excel.
If the steps in this post don’t work for you, I’d recommend working with your SharePoint admins, or opening a support ticket with Microsoft.
Mathew, can you elaborate on the access needed on the SP file/folder/site? I am trying to do this but the user using the PQ have only edit/view access to the SP file but not to the site (only to the file) and its not connecting to it. I am requesting full access to the entire site to see if that solves the problem but wondering what is the minimum access needed for this connection to work.
Sadly I cannot elaborate – I do not know the details of the required permissions, and would not guess or make something up. This might be a great question to ask at community.powerbi.com.
There is an easier way!
Select the Excel file
Click the ellipsis button
In the Details pane, scroll down in the pane to find the Path section
Click the Copy Direct Link button
Documented here under Method 2: https://www.biinsight.com/quick-tips-connecting-to-excel-files-stored-in-sharepoint-online-from-power-bi-desktop/
This also looks to be the only way if you don’t have a license for desktop Excel
ACL’s steps are PERFECT. This is even more straightforward than editing the ?web=1 off of the URL.
And then just a note that in the newer Excel client that I have, “From Web” is no longer under the “Get Data” button dropdown, it’s its own button on “Get & Transform Data” section of the Data tab.
This shouldn’t be as helpful as it was 😉
This worked fine, untill apparently something changed a few weeks ago. Since then it seems it is quite impossible to use an excel on a sharepoint location as source… It always gives the ‘invalid CRLF characters’ error. Anyone has the same problem ?
Jes, here I have the same issue.
I’m not sure what might be behind this error. I can’t reproduce the problem for any of my Excel-based data sources. I recommend opening a support ticket at https://powerbi.microsoft.com/en-us/support/ so the right folks can help.