Impossible to share dataset with users having Build permission

[molegris]

Hi,

I've been reading a lot of MS docs, as well as posts here on the blog but I can't figure out how to give access to users to build their own reports and analyse data in Excel using our "shared" datasets.  

N.B. Our dataset are in the same workspace as our main reports and web App.

No matter what I do to setup the permissions, users never get access the the "shared" datasets.

This is what I see: 

This is what users see (and they get an error when trying to refresh an Excel I made for them with a connection to that dataset) :

Here's more about our configuration and what I did to give them access.

N.B. Everything was done in the New workspace experience.

Workspace	Description	Access
Staging	Basic dataflows that load each individual source file, without history.	Only to IT professionals (Admin role)
Database	Dataflows picking up data from Staging and merging them into facts and dimension tables, with some business rules and incremental refresh.	IT professionals (Admins) BI Analysts (Members and Contributors)
Application	Datasets, star models with all the business rules. Reports App publishing. N.B. Datasets are in the same workspace as reports!	IT professionals (Admins) BI Analysts (Members and Contributors) Other users are VIEWERS. The App was published with the option “Allow all users to connect to the app's underlying datasets using the Build permission.” In Manage permissions, all users have Viewer, Build access to the underlying datasets. (see image below) I also went to Explore Datasets page and give individual access to the users directly to the datasets using the Share option and selecting the checkbox saying : Allow recipients to build content with the data associated with this dataset. (see image below) N.B. No RLS was applied at any time.

Please help?!

Thanks

--mo

[GilbertQ]

Hi @molegris 

You could look at the Excel connected workbook: Power BI May 2021 Feature Summary | Microsoft Power BI Blog | Microsoft Power BI

[Burningsuit]

Hi @molegris 

Granting "Build" rights to a Workspace viewer does allow them to build reports, in Power BI Desktop. It's true that the Workspace Viewer can't see the Dataset in Service, so can't build reports there. But in Power BI Desktop if they select "Power BI Dataset" as a datasource they will see the Dataset if they have build rights and can connect and build a report there. 

Hope this helps

Stuart

[molegris]

Hi @Burningsuit,  Thanks for the reply,

Adoption of Power BI Desktop for "citizen-users" is very slow and I'm not sure our organisation will aim for that within the next 3-5 years.  

So for now, BI experts create reports then they publish them via the new WebApp experience.  Most users access data through the App.

Since most users are familiar with Excel, I wanted to give them more autonomy by sending them Excel files with a pivot table that is already connected to their favorite Power BI Dataset.  But  the keep getting error messages like this one:

I'm also looking at @v-janeyg-msft reply, and it seems like users would need to install the Analyze in Excel plug-in.  I'll try with a user next week but I'm worried that they cannot run msi packages without the intervention of an IT admin from our help desk team.

to be continued...

[v-janeyg-msft]

Hi, @molegris 

Please check this tutorials to learn how to analyze in excel:

Analyze in Excel for Power BI - Power BI | Microsoft Docs

The screenshot you displayed is about some permissions when sharing or using reports in the service. They are different. If you want to anaylze data in excel you should follow the steps in document and note some limitations and considerations.

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,

Community Support Team _ Janey

[molegris]

Hi @v-janeyg-msft , thanks for the reply,

Is there a way to share dataset within the service?  Since our users are already connected to the Power BI portal when they use our Web Apps to access published report, it would be more intuitive to send them to the dataset right there and tell them to simply click on Analyze in Excel to establish the pivot-table connexion with the dataset.

I checked the link above and it seems like the reason our users cannot refresh the Excel files I pre-setup for them with the pivot-tables connected to the dataset, might be because they never installed the plug-in.  I’ll try to do it with a user next week but I’m afraid they might not be allowed to run MSI packages on their pc without the help of an Admin from our IT help desk.

In the link above, they also point out another way to connect to a Power BI Dataset using the get Data menu in Excel.  I have the most recent version of O365 (I’m even part of the insider program) but I don’t see that option.  Do you any idea why ?

According to the documentation I should see this :

But I see that :

Thank you

--mo

[v-janeyg-msft]

Hi, @molegris  Hand slipped.

Have you updated the excel version? Have you downloaded analyze in excel?

[molegris]

Hi @v-janeyg-msft ,

Yes, I updated Excel, and install Analyze in Excel.

Yet I still don't have the "From Power BI" option et the Get Data menu.

As this point, the workflow we suggested to our users is :

1. Open Power BI Desktop

2. Connect to the Dataset

3. Build a matrix visualisation with the data you want.

4. Export data as CSV

5. Open the CSV file in Excel

6. Create a pivot table over the data

7. Save as Excel.

It's ridiculous, they hate it, and to refresh the data you must repeat step 1 to 7... it's obviously not going to work for long.  😞

[v-janeyg-msft]

Hi, @molegris   

Today I found out that my excel can’t be used anymore. I’m sorry. Have you signed in to your o365 account?

[molegris]

Hi,

Yes I'm signed in o365 of course.

So, Excel cannot by used anymore to connect to Get Date from Power BI ?!  Can you elaborate on that? Did Microsoft published any official document with regard to that limitation?

Thanks

--mo

[v-janeyg-msft]

Hi, @molegris 

This version can still be connected normally.

The new version of excel seems to have changed this, but I don’t know much about the updates on excel.

Can you find this: it seems the new version way to connect to powerbi dateset.

You can also directly use the analyze in excel function in the service and then open the corresponding file.

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,

Community Support Team _ Janey

[GilbertQ]

Hi @molegris 

Can you confirm that your users have a Power BI Pro license?

[molegris]

Hi,  

Yes, they have Power BI Pro for faculty.  We don't have Premium service yet, so we bought individual Power BI Pro license for each user since we have a small amount of users at the moment.