cancel
Showing results for 
Search instead for 
Did you mean: 

Excel connection string for Power BI Shared Dataset: "Bad Request"

We have a Power BI shared dataset sitting in a Premium Gen2 P1 capacity that users connect to through both Power BI Desktop and Excel. In the past, we've always instructed Excel users to connect to the shared dataset by going to the Data tab > Get Data > From Database > From Analysis Services.

 

Lately, Excel users have been reporting this error when issuing certain complex, and long running, queries: "Azure Analysis Server returned the following error: Bad Request".

 

Some users, apparently those with a Pro license, now have a new option: Get Data > From Power BI. Users using this approach do not encounter "Bad Request" errors when issuing the exact same queries from Excel.

 

I've noticed that the two methods, the older "From Analysis Services" and newer "From Power BI", generate different connection strings in the "Connection Properties" dialog.

 

Why does the "From Analysis Services" option generate an "inferior" connection string and how do we get users who don't have Pro licenses access to the "new" connection string if they can't use the "From Power BI" option? As the dataset is in a Premium capacity, I would think all users should be able to access it easily.

Status: New
Comments
v-lili6-msft
Community Support

hi

for access to the "new" connection string if they can't use the "From Power BI" option, you could refer to this post:

https://community.powerbi.com/t5/Service/Excel-gt-Get-Data-gt-from-Power-BI-Dataset-SKUs/m-p/1372611

and

https://community.powerbi.com/t5/Power-Query/Connect-Excel-to-Power-BI-Dataset/td-p/1349165

 

Regards,

Lin

nicg
Advocate I

Hi Lin. Thanks for responding. I might just be missing it, but I don't see a resolution in those threads.

 

I think the answer is that we either:

 

1. Buy everyone Pro licenses so they can get the new "From Power BI" button in Excel.

or

2. Have everyone use the "Analyze in Excel" option in Power BI.

 

The first option makes no sense from a licensing standpoint. We have Premium, why should we have to buy everyone Pro just to get that button?

 

The second option makes no sense from an Excel user experience perspective. Why should Excel users need to jump through hoops in the Power BI Service when all they want to use is Excel? They shouldn't have to know anything about Power BI.

 

If the "From Analysis Services" option generated a connection string that worked as well as the "From Power BI" option, there would be no issue. An even better solution would be if the "From Power BI" button worked for everyone in organizations that have Premium.