Row Level Security in Excel (Saved on Sharepoint)

[telra5118]

Hello,

I have saved an excel file in Sharepoint and I want each respective student to only view their data. 

In my case it would be each student view his/her own score's

--------

I have done several transformations using PQ and create a dashboard with few visuals and slicers (for Subject, Assignments etc).

@VahidDM @watkinnc @amitchandak @CNENFRNL @d_gosbell @dm-p @StefanoGrimaldi @ValtteriN @TheoC @bcdobbs @ponnusamy @MFelix @OwenAuger @Mohammad_Refaei 

Now the important part is I can see the data for all the students and would want it like that, BUT I want each student to view his or her dashboard only. Obviously we have a O365 license so each student will go to Sharepoint, open the file and only his/her data must be visible to the student.

How do I achieve that. 

NOTE: I do not have a Power BI license so I need this is Excel (Sharepoint)

[v-jingzhang]

Hi @telra5118 

To my knowledge, Row Level Security cannot be applied to an Excel File.

Row Level Security in Power BI requires that you have at least Power BI Pro license because you need to publish the Power BI report to Power BI Service and share it with your students. For your students, they also need Power BI Pro licenses to see the report shared with them. If you don't need to use Power BI often, this cost should be taken into consideration. 

If you store data in a local SQL Server Database and deployment data model to SQL Server Analysis Services, you can implement RLS for free when the data size is not very large. But this requires that you and your students should have local accounts that have access to the on-premises SSAS server or the SQL Server database. This would need to implement on-premises Azure AD to manage your local accounts. IT team needs to get involved. And you may have difficulty accessing the data when outside of your organization's network. 

You could try the alternatives provided by @bcdobbs. Currently I could not think of any better alternatives. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[bcdobbs]

To the best of my knowledge you can't achieve this directly in excel with out Power BI licences.

Some alternatives:

1) Look at storing data in sharepoint lists. You can control acces to rows there (https://sharepointmaven.com/2-ways-to-manage-row-level-security-in-a-sharepoint-list/). Not really sure how scalable that is though.

2) If the data is in a sharepoint list you could wrap a power app around it. I believe for free if using a non premium connector like sharepoint. You have to fiddle permissions to "hide" the sharepoint list but can then embed security logic in power apps which is more scalable.

3) It sounds like you work in education so worth speaking to your IT team. I know ours have versions of on prem sql server/analysis services included in their licencing packages. Might open some options in terms of using excel.