Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
cassidy
Power Participant
Power Participant

Excel Version 2311

For those of you who use BI via connected PivotTables, the latest Excel version of 2311 is preventing both refreshes of existing connections as well as creating new connections.

 

Please vote for the Issue I posted if you experience the same.  We only have a few users with the update rolled out so far, 2310 is still functional

 

https://community.fabric.microsoft.com/t5/Issues/Excel-version-2311-broken/idi-p/3566713

 

 

cassidy_0-1701720030298.png

 

cassidy_1-1701720037968.png

 

 

 

2 ACCEPTED SOLUTIONS
DHayes
Frequent Visitor

@GilbertQ @cassidy I was able to fix this issue by creating a new Excel document from PowerBI online (I assume the same source) and then saving that as a copy to my PC.  This Excel document works with no problem but I noticed that the connection string is different than my original Excel documents so I copied the new connection string and overwrote the existing connection strings. This fixed my issue.

 

Change Connection StringChange Connection String

 

OLD connection string (renamed GUIDs for privacy)

Provider=MSOLAP.8;Integrated Security=ClaimsToken;Persist Security Info=True;Initial Catalog=sobe_wowvirtualserver-<GUID>;Data Source=pbiazure://;Location="https://wabi-us-north-central-redirect.analysis.windows.net//xmla?vs=sobe_wowvirtualserver&db=<GUID>&app=PBIAddin";Extended Properties="DataSource=pbiazure://";MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Identity Provider=https://login.microsoftonline.com/common, https://analysis.windows.net/powerbi/api, <GUID>;Update Isolation Level=2

 

New connection string

Provider=MSOLAP.8;Integrated Security=ClaimsToken;Persist Security Info=True;Initial Catalog=sobe_wowvirtualserver-<GUID>;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, <GUID>;Update Isolation Level=2

 

The error we got for searchability:

"Microsoft Excel
COM error: COM error. Microsoft.AnalysisServices.AzureClient, Authentication failed; we cannot locate Azure Active Directory in your environment. This could be because your client application does not currently have the required components. Install the latest version of your client application or contact technical"

View solution in original post

cassidy
Power Participant
Power Participant

Ignore the other "solution" that was selected by a MSFT employee incorrectly.

The actual solution was that our company had TLS 1.3 enabled on Windows 10, which is not compatible.  Version 2311 of Excel apparently started using 1.3 when trying to reach out to Power BI Service, causing an error.  

This also was preventing Power BI Desktop from Signing In, and also Excel from connecting to Azure SQL DB's

View solution in original post

8 REPLIES 8
cassidy
Power Participant
Power Participant

Ignore the other "solution" that was selected by a MSFT employee incorrectly.

The actual solution was that our company had TLS 1.3 enabled on Windows 10, which is not compatible.  Version 2311 of Excel apparently started using 1.3 when trying to reach out to Power BI Service, causing an error.  

This also was preventing Power BI Desktop from Signing In, and also Excel from connecting to Azure SQL DB's

DHayes
Frequent Visitor

@GilbertQ @cassidy I was able to fix this issue by creating a new Excel document from PowerBI online (I assume the same source) and then saving that as a copy to my PC.  This Excel document works with no problem but I noticed that the connection string is different than my original Excel documents so I copied the new connection string and overwrote the existing connection strings. This fixed my issue.

 

Change Connection StringChange Connection String

 

OLD connection string (renamed GUIDs for privacy)

Provider=MSOLAP.8;Integrated Security=ClaimsToken;Persist Security Info=True;Initial Catalog=sobe_wowvirtualserver-<GUID>;Data Source=pbiazure://;Location="https://wabi-us-north-central-redirect.analysis.windows.net//xmla?vs=sobe_wowvirtualserver&db=<GUID>&app=PBIAddin";Extended Properties="DataSource=pbiazure://";MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Identity Provider=https://login.microsoftonline.com/common, https://analysis.windows.net/powerbi/api, <GUID>;Update Isolation Level=2

 

New connection string

Provider=MSOLAP.8;Integrated Security=ClaimsToken;Persist Security Info=True;Initial Catalog=sobe_wowvirtualserver-<GUID>;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, <GUID>;Update Isolation Level=2

 

The error we got for searchability:

"Microsoft Excel
COM error: COM error. Microsoft.AnalysisServices.AzureClient, Authentication failed; we cannot locate Azure Active Directory in your environment. This could be because your client application does not currently have the required components. Install the latest version of your client application or contact technical"

cassidy
Power Participant
Power Participant

FYI, @v-xiaoyan-msft accepted this as a solution, it is not.  The problem persists regardless of connection string and does not solve the inability to create a new Power BI Pivot via Insert>PivotTable>From Power BI

cassidy
Power Participant
Power Participant

@DHayes after creating an Excel doc with a Power BI pivot online and saving to my local machine and opening in Desktop Excel, the error persisted.  I also see some differences in the connection string, but neither Desktop created or Online created work while in Desktop.

Digging deeper into it I found that the first GUID is unique to the report or semantic model whereas the second is 929d0ec0-7a41-4b1e-bc7c-b754a28bddcc which is public. The first GUID is in the URL of the report in PowerBI online.  I would make sure the first GUID matches the same source as the original connection string (i.e. the "sobe_wowvirtualserver-" string is the same). 

GilbertQ
Super User
Super User

Yeah agreed, glad you have a workaround!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

GilbertQ
Super User
Super User

Hi @cassidy 

 

As a workaround what happens if you create the pivot table in Excel online and then open it in the Desktop App, that MIGHt work?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

@GilbertQ , yes that does work and I've instructed users to do so as a solution.  We also have at least have of users on the older version, so people are literally using their neighbor to refresh a workbook for them.

 

Excel updates break BI functionality far too often, it'd be real nice if they included it in their testing.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors