Error in Analyze in Excel - Connection String

[AndreaCBS]

Hello everyone,

I'm experiencing the following issue: In Power BI Service, when I click on the "Analyze in Excel" option and open the file, it throws an error stating that it cannot connect to the OLAP cube.

However, if I go to the data connection properties and modify the connection string name, it successfully connects.

How is it possible that when I download a report connected to the cube, which displays the data correctly, the "Analyze in Excel" option seems to insert an incorrect connection? And how can i fix it?

[cbernsdorfSCS]

Did you ever find a solution to this?  I am experiencing the same issue. In my case, i have found that if the model name has a space in it, when you download the excel file, the command text gets quotes added around it which causes the excel connection to fail.  This seems to be a bug when Power Bi adds the connection string for the excel file. 

[Anonymous]

Hi @AndreaCBS 

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AndreaCBS]

I didn't find the solution. Probably I will open a ticket to Microsoft.

[Anonymous]

Hi @AndreaCBS 

Here's the explanation from the document:

Troubleshoot the connection from Excel to Power BI data - Power BI | Microsoft Learn

According to my speculation, your problem here is the connection string in the Excel file might be incorrect and cannot match the correct OLAP cube so that you need to update the connection string.

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AndreaCBS]

You got it right — the issue is that the connection string in the Excel file is incorrect and does not match the correct OLAP cube, so it needs to be updated. Once you correct it, it works.

However, my issue is not with the Excel file itself. The client downloads the Excel file from the Power BI report on Power BI Service every 2-3 days, which means the problem will keep recurring. I’m looking for a way to resolve this issue once and for all.

[Anonymous]

Hi @AndreaCBS 

Consider updating the Connection String in Power BI, open the Power BI Desktop file that was used to publish the report to the Power BI Service. Go to the "Transform Data" or "Get Data" > "Data Source Settings" to find the data source for the OLAP cube. Update the connection string to the correct one that points to the OLAP cube. After updating, republish the Power BI report to the Power BI Service.

Or you can create a template Excel file with the correct connection string to the OLAP cube. When you need to update the data, export the latest figures from Power BI to CSV or Excel, and then import this data into your template Excel file.

Save a workbook as a template - Microsoft Support

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AndreaCBS]

Do you know if there's something that can be done from the tenant administration portal?

[Anonymous · Accepted solution]

Hi @AndreaCBS 

As far as I know, this issue should not be related to the Tenant Administration Portal.

Maybe you can create a support ticket for free and a dedicated Microsoft engineer will come to solve the problem for you.

It would be great if you continue to share in this issue to help others with similar problems after you know the root cause or solution.

The link of Power BI Support:  https://powerbi.microsoft.com/en-us/support/

For how to create a support ticket, please refer to How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

Thank you for your understanding.

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AndreaCBS]

I tried to update the connection string then republish the Power BI report to the Power BI Service. But if Analyze in Excel once more, I have the same issue.

I tried different approach, hopefully I will persuade the customer to do things in a different way. But now what's most important to me is how fix this issue.