From Online Service Option disable How to enable that in Office 2016

[kirankk91]

I have office 365 i have also installed Power query (64) bit but when I click Get Data under "Data" in excel that time "From Online Services" Option showing disable, How to enable that.

Please suggest Thanks in advance..

[v-yingjl · Accepted solution]

Hi @kirankk91 ,

As far as I know, this feature “From Online Services” is only available to the Office applications with Product Information Microsoft 365 for enterprise.

If you are using Microsoft 365 for business etc., this feature is not available.

See: Excel: Unable to get data from online services 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Connor_L]

What I have found is if I use power bi (Get Data->More and then in the next window Online Services->SharePoint Online List)

Put the URL in (for the site not the full URL to the list [remove everything from /lists onwards])

Go through any authentication using your Microsoft account.
Click next and then select the list and load the data.

Once you have the data in your power bi go into the power query editor for that data request (I right click on the table in the data sidebar and click edit query)

In power query click advanced editor

Copy the advanced editor query 

Now that you have this you can go to excel.

Get Data -> From Other Sources -> Blank Query

Open up the advanced editor again.

Paste in the query you copied.

Go through the authentication.
And hey presto got your list into excel.

Why they have removed all the UI to make this possible from excel when it still works fine in power BI baffles me. Complete disregard for all their users trying to make use of these features. But this is a workaround. (Or if you magically know all of the incantations in the power query advanced editor then I guess you could do it directly that way).

We use business premium licences, not even E3 or E5 so I am sure it would work for those licences.

Hope that helps.
Connor

[Ehren]

What license/SKU are you running? Please see this page for more info on what Power Query features are available in each version of Office.

https://support.microsoft.com/en-us/office/power-query-data-sources-in-excel-versions-e9332067-8e49-...