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

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