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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Excel not conntecting to BI Models

Hi, 

 

Some of my team members are receiving  the following error: "Can't access the dataset or the report  You can't access the dataset or report in PowerBI.  Make sure it exists and you have permissions to use it" when trying to use the Power BI publisher for Excel tool.  All of the people that are trying to accesss it have permissions to.  Any light that you could shed on this is greatly appreciated.

 

Respectfully 

 

Phil 

Status: Accepted
Comments
gh_de
Frequent Visitor

Same problem here, started some days ago. 

After pressing "Connect to Data" the List of available Datasets is shown (so credentials and network connection work). After selecting any of the available Datasets the error message above "Can't access the dataset or the report  You can't access the dataset or report in PowerBI.  Make sure it exists and you have permissions to use it" shows up.

Anonymous
Not applicable
plaliberte
Frequent Visitor

Thank you for your help but the issue you are referring to via the link is not the same thing.

 

v-haibl-msft
Employee

@plaliberte

 

The Product Team are aware of this issue (CRI 48799786) and will be fixed on next release of the addin. no ETA for now.

The workaround is tricky, please use the "Analyze in excel" from Power BI portal for now.

If the user still wants a workaround from excel please follow the below steps after tries to analyze some dataset.

attached a photo to help with the steps

  1. go to the Insert tab
  2. click pivot table
  3. choose "Use an external data source" and then "Choose connection" button
  4. right click on the connection, it should be "Power BI - <dataset name>"
  5. click "Edit Connection properties"
  6. go to Definition tab
  7. on the start of the connection string there is "MSOLAP.7" please change it to "MSOLAP" (remove the ".7")
  8. click ok => open => ok

 workaround steps.jpg

 

Best Regards,
Herbert

Vicky_Song
Impactful Individual
Status changed to: Accepted
 
gh_de
Frequent Visitor

Hi there, 

 

seems to work on some PCs.


Other PCs keep asking for credentials, or say ("Initialization of Data Source failed" (translation from German, so maybe not the literal wording))

 

After the download if x64_SQL_AS_OLEDB (Analyze in Excel Update) wasn't available for some days, the installation of it doesn't help in all cases.

 

As in https://community.powerbi.com/t5/Service/analyze-in-excel/m-p/286379#M33828 it seems beside the "Publish in Power BI" Plugin you also crashed the installation package of the workaround. 

 

Shouldn't work that way if Power BI is supposed as an easy to use BI Solution. People rely on the availability.

 

 

 

edhans
Super User

This has been a mess this week. First it took 24+ hrs to get an English version of the SQL as OLEDB file from MS as the Power BI portal is delivering German only, at least in the US.

 

Now this.

 

Power BI is supposed to be a top notch piece of software for enterprises. These kinds of things should not happen like this.

 

Thanks for the workaround. I will give that a shot, but this is not something I would try to walk a remote non-techie through over the phone. 😕

plaliberte
Frequent Visitor

I have noticed one thing with the people in my office.  The version of Excel is different in those that are having problems than from the ones that are not.  My initial thoughts are that it is Excel causing this issue and not Power BI.  The version of Excel causing our issue ends in 2139.  Hope this helps

 

edhans
Super User

Ugh. This isn't working for us. We keep getting the error:
The following system error has occurred. (The operation completed successfully)

 

No other info.

We've reinstalled both the SQL OLEDB thing, and the Excel Power BI publisher.

 

This was working last month when I help set up a few connections on another computer.

gh_de
Frequent Visitor

This helped reliably for us: 

 

  1. Update Excel to latest version
  2. Update SQL OLEDB thing via Power BI Web "Analyze in Excel Updates"
    1. If you already have an "old" Excel File: Edit the connection string in Queries - select query - Tab definition: There is sth with MSOLAP. If it is MSOLAP.7 or MSOLAP.8 delete the dot and the number so it is only MSOLAP.
    2. This works also after the connection with the publish in Power BI Plugin gives the error message. In this case: After editing the connection string you can insert a Pivot Table by hitting Pivot Table button. You find you connection at external connections
  3. After that:
    1. Make the user account to an Admin account (don't leave this out and don't use another Admin account)
    2. Open Excel as Admin (type excel in start menu, right click, start as Admin)
    3. Open the file
    4. Update your data - it should work now!
    5. You can close Excel now
    6. You can downgrade the usser account to Standard again - it still works 

 

Best way to start a new Excel file after that is in the Webfrontends - the dots beside the dataset Analyze in Excel. It downloads an odc file which should work. 

 

By the way: The latest Excel update should be the cause of that. It stopped workind in our company user after user, always after the recent update has installed automatically.