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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
MattAllington
Community Champion
Community Champion

Anyone got the new Analyze in Excel feature working?

I have tried to get this new feature to work.  I click Analyze in Excel, installed the new software (64 bit because i am running 64 bit Windows/Office) and then tried to Authenticate.  It gave me a forbidden erorr and then reprompted for me to authenticate against the Power BI SSAS Server.  No matter what I try, I can't authenticate.  I am using my Power BI email address for the user name and my correct Power BI password.



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.
35 REPLIES 35
IlanH
Microsoft Employee
Microsoft Employee

While "allow free users" is now fixed.

I would like to mention 2 scenarios where "analyze in Excel' will fail.

1st is currently a limitation. the 2nd is due to choosing wrong account for authentication.

 

1) Current limitation - "Analyze in Excel" doesn't work for "on premise analysis services" dataset 

AS_OnPrem.jpg

2) Choosing wrong account - you must choose your work/school account and not your personal account if you get multi accounts choice. see below

 

WorkAccount1.jpg

 

 

 

Anonymous
Not applicable

Do you know when this feature would be available for SSAS cube? We have few tabular cubes in house and I think  the users would be extremely happy if it will work.

IlanH
Microsoft Employee
Microsoft Employee

Would it help if we will provide ODC that you can open and Excel will connect directly to on premise SSAS cube ? assuming you have direct connectivity to your on premise SSAS cube.

When this says "Work or School Account", does this mean O365 or something else?



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.
IlanH
Microsoft Employee
Microsoft Employee

We fix the issue and now "Analyze in Excel" is available and working also for free users.

Please try and lets us know in case you still have issue


@IlanH wrote:

We fix the issue and now "Analyze in Excel" is available and working also for free users.

Please try and lets us know in case you still have issue


 

I had extensive support from Tal via Skype last week, so I have a pretty solid understanding of how to test this.  Having said that, i still cannot get it to work.  I have multiple accounts but one of them is a "work and school" account - what ever that means.  It is an O365 account that I have for my business.  I am also the O365 Administrator for the O365 service.  I get the same errors I got previously.



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

I am one of those who have "multiple accounts", the original for my O365 E3 subscription (tied to my domain) and then later when I needed a new official "Microsoft Account" when upgrading to Win 8.

 

The way I understand "work or school account" is that it refers to an account set up and provided by someone else, as distinct from my "personal" Microsoft Account.

 

On the upside, having the same account name / email address makes it easy to remember. And I make sure to keep my passwords in sync for both accounts. (bad practice, but I do use a VERY strong password + two-factor auth to mitigate)

 

On the downside, lots of authentication issues for services that don't understand the need to disambiguate, or for which EITHER is a valid login. Hence, when I go to the Azure portal and they "think" I'm only logged into O365, all my Azure services disappear. Scary at first, but then a simple log-out and re-log gets me back. The same goes for Sway and a few other services.

 

I suspect that is my issue with Analyze in Excel.

 

 

Tal will work with you to investigate your scenario.

re multi accounts. O365 account should work of course.

My post in respect to accounts refer to a specific scenario where you have 2 accounts with exact same account name. in that case you get authentication form with 2 account options - one is personal and one isn't personal (work/school) you should choose the work/school option in that specific case.

IlanH
Microsoft Employee
Microsoft Employee

We have understanding of the reasons you got "Forbidden"

1) Common scenario - free users get forbidden

  • We are working to fix that and allow Analyze in Excel for all users
  • Currently it is open for pro users

2) Advance case - using multi users for Analyze in Excel

  • To resolve this case you should follow Tal workaround steps. Tal steps provide a way to sign in Excel with specific user (and sign out from prev user). This is supported by explicitly setting user ID property in the ODC file.

 

We will update once the fix to allow free user is available

 

Thanks all for helping us understand the issue!

mwmckee
Frequent Visitor

I am running Pro and have followed Tal's workaround steps by editing the ODC file.  I now do get AAD prompted but still receive the same error after entering my credentials.

 

Is there anything else I might be missing?

 

Thanks

It is a known problem for some users (free accounts and multiple account users).  I was advised by Microsoft it should take about a week to fix.  They will advice back here once it is fixed.



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

Hi IlanH,

 

Thanks for letting us know that Free user will not able to use this feature yet.

Any ideal when MS Power BI team is able to get the 2 issues fix? Thanks.

IlanH
Microsoft Employee
Microsoft Employee

free user issue should be fixed relatively shortly - will update you once the fix is in. I assume ~1 week

My problems are solved as well. The menu to download the msi package have both the x64 and x86 version. In my excitement and button highligh I selected x86 and not x64. You might consider better language and equal weight to both types on the menu.

teroman
Frequent Visitor

I'm getting this problem, I assume it's because I'm using a free account for testing.

 

Will I need to get the networking bods to set up AAD too? Not sure that we are all joined up to that yet.

 

Thanks

 

col

tarostok
Power BI Team
Power BI Team

Hi all,

 

A question and a possible solution for those who got "Forbidden" error:

When you opened the ODC file did you get AAD sign-in window (see picture below)?

If the answer is no please try the following solution:

1. Open your ODC file in notepad.

2. Inside <odc:ConnectionString> tag add the following property: ;User ID= your PowerBI user name;

3. Save the ODC file.

4. Open it in Excel.

Now you should get the AAD sign-in window and successfully log-in.

 

Highly appreciate your feedback.

 

Thanks,

Tal

 

 

 

 

 

AAD sign-in.png

 

 

 

Hi Taro,

 

Not familiar with ODC script. Thus need your advise is the below script to include the User ID in ODC file is correct? I'm still not able to the AAD sign-in windows prompt. Note: I'm on Free Power BI version.

 

</odc:ConnectionString ="User ID = PowerBI user name">

NH
Advocate II
Advocate II

sorry I've entered the User ID = BI account name at the wrong place. Corrected as shown but still no AAD prompted.

<odc:ConnectionString>Provider=MSOLAP.7;User ID= BI account name; .....

Hi Tal

 

I have tried what you recommendation but I still get the same forbidden error.  THen I get this popup (same as always happens) however now my username is pre-popluated (it was not like this previously).  The Password box was blank.  I enter my password (shown below) 

login.png

Then I get this error shown below.

error2.png



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.
jmalone
Resolver III
Resolver III

I am having the same issue. I first see the message: "The HTTP server returned the following error: Forbidden."

 

I try to connect using my Power BI account, but end up with the message "An operation that uses the database driver could not be completed."

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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