Forum Discussion
Solved
i want to share some sheets from my workbook
Hello, I want to share a workbook with some of our franchiseorganisations, but there are three sheets that lead with pivot tables (powered by power query). And if they click those pivot tables they...
If Power Query loads data to data model and PivotTable is built on data model, franchisee in any case could have access to all data, even if separated PivotTables will be built.
If PivotTable is created from Power Query that's only to create set of queries, one for each franchisee (perhaps by referencing and filtering master query) and create separate PivotTable for such franchisee. Yes, they could be placed in separate sheets, with hiding for this franchisee other sheets with password protection of workbook structure and other sheets. But that's lot of manual work and in any case you have different files for franchisees. That's not reliable solution. Perhaps approximately that's how you work now. It looks like you send to each franchisee the same PivotTable but filtered for this concrete franchisee. The only to add here is sheet protection not to allow filter PivotTable. Again, here is better to filter Power Query for concrete franchisee.
Even better to create franchisee files from scratch. But that depends on how many franchisees you have, e.g. 3 or 300. With very few such files could be created manually. With lot of them that doesn't work. In general, in such case entire process could be fully automated, from creating separate Excel files to email distribution of them. All by one button or by scheduled process. That's not easy task and depends on what is included in your 365 subscriptions.
If Power Query loads data to data model and PivotTable is built on data model, franchisee in any case could have access to all data, even if separated PivotTables will be built.
If PivotTable is created from Power Query that's only to create set of queries, one for each franchisee (perhaps by referencing and filtering master query) and create separate PivotTable for such franchisee. Yes, they could be placed in separate sheets, with hiding for this franchisee other sheets with password protection of workbook structure and other sheets. But that's lot of manual work and in any case you have different files for franchisees. That's not reliable solution. Perhaps approximately that's how you work now. It looks like you send to each franchisee the same PivotTable but filtered for this concrete franchisee. The only to add here is sheet protection not to allow filter PivotTable. Again, here is better to filter Power Query for concrete franchisee.
Even better to create franchisee files from scratch. But that depends on how many franchisees you have, e.g. 3 or 300. With very few such files could be created manually. With lot of them that doesn't work. In general, in such case entire process could be fully automated, from creating separate Excel files to email distribution of them. All by one button or by scheduled process. That's not easy task and depends on what is included in your 365 subscriptions.