Get data from online service missing in Excel Office 365 E3 and E5

[ivan02]

Hello everyone

I have an Office 365 E3 license and was able to use the option "Get data from online services" in Excel. But now the option is missing and I supposed it was removed from E3 licenses. So I purchased an E5 license and assigned to my Office user, but the option is still missing.

Please, does someone know what has happened with this option?

If I run previous xlsx files where the option was used in power query, I am still getting the data from the online service. Also I can manually create the query to the online service and get the data, but the having the option in the menu is more convenient because it writes the query.

Regards

[#2]

@ivan02 

Strange. I'm on E3 and it works

[#3]

Hi @SergeiBaklan , these are the options shown in my Excel. Months ago the option appeared but after an update (I don't know what update) the option suddenly disappeared:

I have Excel version 2208 build 15601.20148 installed in a Windows 11 Insider running using Parallels 18.0.1 (53056) on a M1 Mac

[#4]

@ivan02 

Sorry, I have no idea. Windows version doesn't matter if that's after Windows 7. Build number is also not very important, if you are on Current channel I guess it shall be. If below channel - who knows.

[#5]

Good Morning.

Were you able to resolve this. Experiencing the same scenario.

I have other users with Business Standard that is able to access these functions.

Reinstalled everything and on MAC.

[#6]

@ivan02 

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 work 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 hen 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