Forum Discussion
power query - no sharepoint folder option as data source
From SharePoint connectors (SharePoint folders in particular) are available only for enterprise subscriptions, not for business ones.
Power Query data sources in Excel versions - Excel (microsoft.com)
You may try to M-code it directly SharePoint.Files - PowerQuery M | Microsoft Docs , not sure it works or not in such case.
From SergeiBaklan on Aug 18 2021: You may try to M-code it directly https://learn.microsoft.com/en-us/powerquery-m/sharepoint-files...
It's my understanding (I can be wrong) that https://support.microsoft.com/en-us/office/power-query-data-sources-in-excel-versions-e9332067-8e49-46fc-97ff-f2e1bfa0cb16?ui=en-US&rs=en-US&ad=US actually tells you - when there's a check mark - if the option (connector) is available in the Excel menu/sub-menu and nothing else. However, it's not because i.e. SharePoint Folder doesn't appear in your menu that you can't connect to a SharePoint folder - IMHO this has to be checked
I have a 365 Home subscription and don't have SharePoint options in the Excel menus, though the SharePoint connectors are actually available in my product:
Other examples:
- JSON option not avail. in Excel 2016. After a few exchanges the OP opened a JSON file
- https://answers.microsoft.com/en-us/msoffice/forum/all/power-query-get-transform-get-data-from-pdf/1bd2afdc-2912-4b08-aae6-40250b0d589d?tm=1657958531852
To check if Sharepoint connectors are available in your product:
I don't have SharePoint in my environment so can't be detailed. Also note that from an Excel version to another the options are not exactly at the same place in the Excel User Interface so I can be precise:
#1 Download attached file SharePointOrNotSharepoint - it only contains a query > Refresh it. If no SharePoint function is listed, stop there. Otherwise...
#2 Go the Excel Data tab then navigate in the menus/sub-menus until you find option Blank Query
(It seems to be in sub-menu From Other Sources in all versions > 2013 but not sure)
#3 Select that option - The Power Query Editor should open (it auto. creates a query)
#4 In the formula bar enter exactly (ex. for a SharePoint folders) - Replace with your URL between double-quotes:
=SharePoint.Contents("https://ToBeReplacedWithYourSharePointUrl")
then Validate
NB:
- Power Query is case-sensitive (SHARePoint.FOLdER won't work)
- You might have to enter options => check the doc. https://learn.microsoft.com/en-us/powerquery-m/sharepoint-contents
If you want to know all Power Query functions (most connectors are PQ function) available in your Excel version you can download attached file PQ_AvailFunctionsInProduct and refresh the query, it'll list them all
I didn't play with that for couple of years at least, as I remember even if SharePoint connector is within #shared, code with it could work for one person and doesn't work for another. The message is like "Data could not be retrieved" or like.
Another option is to use OData connector with URL as
https://<tenant>.sharepoint.com/sites/<siteName>/_vti_bin/listdata.svc
with Organizational account, it shall work.
Data could not be retrieved / Data Source error msg are/were not necessarily encountere(d) at connection time and are/were not SharePoint specific, just a few: Web, Excel, https://answers.microsoft.com/en-us/msoffice/forum/all/access-to-excel-data-could-not-be-retrieved-from/720fa3e9-9d7c-4b72-b83a-631a90b83df3 (re. SharePoint https://powerusers.microsoft.com/t5/Building-Power-Apps/Excel-Data-source-error/td-p/835539/page/2 is quite interesting although error & context are different)
https://support.microsoft.com/en-us/office/handling-data-source-errors-power-query-7b0b4a6e-9402-4f80-ab66-2239dbc7d6e8 is good to read - IMHO - for people who start from scratch with external data source(s)
Anyway... Previous post was only to dev. a bit what you suggested to try 2 years ago as a possible option when the connector isn't available in the UI. OData could be another one as you said