Analyze in Excel: Invalid value for key 'integrated security'

[Anonymous]

I have a dataset in the Power Bi Service that I'm trying to use in Excel.

The "Analyze in Excel" option works fine when generating the Excel file and I can even refresh without issue.

The problem arrises when I try to use Power Pivot with the connection. I get the error: "Failed to connect to the server. Reason: Invalid value for key 'integrated security'."

This is the connection string in the Excel file that was generated by the Power Bi Service:

Provider=MSOLAP.8;Integrated Security=ClaimsToken;Persist Security Info=True;Initial Catalog=sobe_wowvirtualserver-36ae5565-a0f0-47c1-80f3-1948749003cd;Data Source=pbiazure://api.powerbi.com;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Identity Provider=https://login.microsoftonline.com/common, https://analysis.windows.net/powerbi/api, 929d0ec0-7a41-4b1e-bc7c-b754a28bddcc;Update Isolation Level=2

I tried uninstalling/removing and then reinstalling add-ons/drivers that were suggested by other threads unfortunately to no avail. In fact it might have done more harm than good but that's beside the point.

I can't seem to figure this out and would appreacite any insight.

[GilbertQ · Accepted solution]

Hi @Anonymous 

As far as I know currently it is not possible to connect from Power Query in Excel to a Power BI Dataset in the service.

You can vote for the idea on https://ideas.powerbi.com

[RobertSlattery]

It is quite possible to connect to a PBI Dataset in the service, with power query in excel.  A dataset is an AAS Cube and power query has an AAS connector: Get Data\From Database\From Analysis Services, then enter the Workspace Connection URL.  Select Use the following User Name and Password and leave them blank (assuming MFA is enabled).