Automating Excel Page to copy all the Data in a New Workbook

[mandar5118]

I have attached a file.

It's basically a Dashboard which is an output of few transformations in Power Query and creating Pivot Table, Slicers and Visuals.

The data consists of Students and their performance.

The file is working as expected. The challenge is....


I want to be able to create 'views' exactly like this Dashboard, BUT limiting the access to only that particular student who logs in to his account. In other words the students should only see his/her data (similar to RLS in Power BI).

Is there a way I can duplicate each Students data in multiple excel files (dynamically) such that once I share the file with them on may be One Drive, they will have permanent access to the file. As and when new data arrives in the Raw Dump, the file would automatically be refreshed.

The Raw Dump comes from a folder with multiple CSV files and I have done a 'from folder' in Power Query to setup the connection.

I tried my hands on some macro but could not solve the problem.

Any other ways to resolve this issue is also most welcome. Thank You!