Manage Roles and View Roles from Excel Spreadsheet

[Anonymous]

Hello,

I'm trying to create rules/roles for my PowerBI project, however I uploaded the info from an Excel Spreadsheet. I'm reading around that RLS is not possible in Excel. A) Is this true? B) if so, is there a very simple way to convert the Excel Spreadsheet to a PowerBI Data Set and just redo the report (i.e. rewrite all the formulas etc.)? Thanks for the help! 

[swise001 · Accepted solution]

@Anonymous 

Don't give up too soon on this.  I promise - you can get this to work.  

Also remember that you have imported the data into Power BI - so the fact that it's from Excel no longer matters - as it now resides in your Power BI file.  

I'll demo with a simple example: 

Let's say I have this table imported in Power BI Desktop

And I want your coworker to only see the data for Florida - you could create a role specific for only Florida like this

When I import this to Power BI service - anyone I drop into this Role - will only be able to see Florida. 

Importantly though - Row Level Security ONLY applies to users in a "Viewer" role of dataset workspace.  - So if you invited your coworker to your workspace as a "Member, Contributor, or Admin" - he/she will not be bound by RLS.

If you still run into issues - import a few screenshots that I can reference.  I'll be able to better guide what needs to be fixed.  

[swise001]

@Anonymous 

Row Level Security in Power BI doesn't care where your data is coming from.    Once it's in Power BI - its' in. 
You build your roles/rules against those imported tables using the Security tab in the ribbon.  

It's possible to convert an Excel data model to a Power BI data model (but that assumes you built a Power Pivot model in Excel first).  If you didn't do this - there is nothing for you to import using this process. 

It sounds like your data is just sitting in an excel spreadsheet  and is  maybe driven by in-cell formulas, etc.  If this is the case - you can import the values into Power BI, but there is no way to import the 'formulas' that you used in Excel to get those values (if you did this work using native excel formulas (i.e. =VLOOKUP.... ).  

[Anonymous]

@swise001 Hi, thanks for the reply and suggestions. No, I'm not using any formulas in the Excel sheet; it's all stagnant data. But It's ongoing, so I'm adding to the spreadsheet monthly. Would using the Power Pivot still work? If this is not the case, when does using RLS work? Would it have to be a SQL DB in order to use RLS in PowerBI?

[swise001]

@Anonymous 

Ok - that helps. 

1.  Import your Excel spreadsheet to Power BI using the default button on the home ribbon. 

From here select your Excel file - and then select the sheet you want to import.  If the file has more than 1 sheet - you can select more than one. 

You can potentially transform your data - but for simplicity - you should be able to simply load the data into Power BI 'as-is'. 

Row Level Security is defined in Power BI Desktop - on the security Ribbon: 

For it to work - the data you're importing from Excel has to include some context or grouping that you can define. 

There's plenty of support on Microsoft's site for creating RLS:  

https://docs.microsoft.com/en-us/power-bi/admin/service-admin-rls

Although you 'create' Row level security in Power BI desktop - it doesn't actually get applied until you publish the file to Power BI Service and add users to your role membership.  

At this point - now when you share this report with someone from the Service - if that user is part of the role membership that you specified - the data they see should be filtered based on your rules.  

This is a huge oversimplification of the process - but it sounds like you are just trying to wrap your head around it.   Hope this helps. 

[Anonymous]

@swise001  - Thanks for the follow-up and the recommendations. I have tried all this, however the filtering isn't working. I added someone to a Role and she's still able to see all the information that's on the page, not just the rows that I put in the role for her to be able to view. A lot of things I've been reading is that RLS cannot be used for an Excel Spreadsheet upload, so that's where my OP is spawning from. It seems silly to me that MS would have PowerBI unable to apply RLS to XLSX files...(Coincidentally, I had the RLS article pulled up and was reading it for the 20th time to see what I'm missing).

So with that said, what do I need to do with my data in Excel with the Power Pivot tool to make RLS applicable? Thank you again for your help on this.

[swise001 · Accepted solution]

@Anonymous 

Don't give up too soon on this.  I promise - you can get this to work.  

Also remember that you have imported the data into Power BI - so the fact that it's from Excel no longer matters - as it now resides in your Power BI file.  

I'll demo with a simple example: 

Let's say I have this table imported in Power BI Desktop

And I want your coworker to only see the data for Florida - you could create a role specific for only Florida like this

When I import this to Power BI service - anyone I drop into this Role - will only be able to see Florida. 

Importantly though - Row Level Security ONLY applies to users in a "Viewer" role of dataset workspace.  - So if you invited your coworker to your workspace as a "Member, Contributor, or Admin" - he/she will not be bound by RLS.

If you still run into issues - import a few screenshots that I can reference.  I'll be able to better guide what needs to be fixed.  

[Anonymous · Accepted solution]

@swise001  - thanks for posting this. This was helpful for sure! However, I fiddled with it some more, and found out it was based on the fact that the person was in my workspace, so even when I had her only in an exclusive role, her status as being in my workspace overruled this and that's why the filters weren't working! Such a simple solution....Thank you for your perseverence!

[v-xicai]

Hi @Anonymous ,

You can define RLS only on the datasets created with Power BI Desktop. If you want to enable RLS for datasets created with Excel, you must convert your files into Power BI Desktop (PBIX) files first. Learn more.

For reference:

Row-level security (RLS) with Power BI

Best Regards,

Amy 

Community Support Team _ Amy

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

[Anonymous]

@v-xicai - Hi Amy, thanks for the reply. I tried doing what is in that article, but I get a "Migration Failed - The following Excel Workbook doesn't contain any queries or a model to import" error. How do I get around that? The XLSX file I'm importing is just a bunch of data points. 

[amitchandak]

@Anonymous , Publish, and use power bi dataset in new pbix

Now in the new pbix. Copy-paste visual page by page and it should work.

You can select all visuals on a page and copy in one go to the other pbix and paste.

Try if that can work for you.

[Anonymous]

@amitchandak Thanks for the response, Amit. This still doesn't allow me to enable RLS for Roles when I do this, though. Any other suggestions?

[pranit828]

Hi @Anonymous 

My understanding is that RLS is not supported in ecxel.

You can use the excel as a power BI dataset.

No easy way to convert a excel report to Power BI report. you have to re-develop the report again.

Did I resolve your issue? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!!

Regards,
Pranit