Hi,
Introduction:
PC, Windows 10, Office 365 (Microsoft 365)
I'm working with Excel (365) file that contains several Sheets sorted by years (2018, 2019, 2020, soon to be 2021...).
Sheets are the same by their structure, right now I need help on latest one, "2020".
Abstract:
Sheet ("2020") has lots of columns; ID, customer names, assigned salesmen, their sectors and the rest is revenues and expenses sorted by months, each month has two columns, one is turnover and the other is price differential. Prices are set as currency. Altogether some of 40 columns and around 200 rows.
Note:
Data is entered from another source, at the end of every month. It's a time consuming and complicated process, but not the subject of this question.
Scope:
Each sector should be able to see its figures without seeing figures of other sectors. There are three sectors. I've created three Queries filtering these data for each sector (basically simple Query with a filter by a sector).
Goal:
I would like to put these Queries somewhere on Microsoft 365 so that any user from these sectors (assigned by name, or Shared to...) can reach its Query, open it, view/read it without making any changes, can refresh to see latest data.
So, employees should see only their Query and not the other two.
Question:
Where do I put these Queries, what 365 App should I choose?
Tried on SharePoint, but it won't refresh when opened (in web Excel), and I can't force employees to open them in Excel app (web is the default one).
Need a simple solution for not advanced users.
Maybe it is Power BI, but it seems its much complicated in order to configure queries.
Don't like the OneDrive option, but, if it is the only one...
Or, maybe there is a new approach to this altogether, that I just don't see/know...
Thanks in advance!
Hi Nesha,
Welcome to our forum!
To be clear, if the source data contains all sectors and you use Power Query in Excel to filter the data for each sector, then there's nothing to stop an employee editing the query and getting access to the data for all sectors, other than being limited by their Power Query skills.
Power Query in Excel can only be refreshed in the Excel desktop App. Power Query refresh is not available online yet, irrespective of whether the file is on OneDrive or SharePoint.
The only way to properly secure the data at an employee level like you describe is with Power BI's row level security.
I hope that clarifies things.
Mynda
Hi Mynda,
Thanks for quick reply. Honored to talk to you.
Users and not that skilled, so I have no worries of them seeing each other values.
Connection would be as I see it now:
Excel master file -> Power BI desktop (as table) -> Published on Power BI web
Or should include existing Excel Query step:
Excel master file -> Excel Query -> Power BI desktop (as table) -> Published on Power BI web
First scenario seems more reasonable, but not sure.
So, I enter values in Excel master file, save the file, then users Refresh in Power BI web and they get latest values?
Nesha
Hi Nesha,
You can get the data from the Excel source file with the Power Query tool in Power BI Desktop and then publish to the Power BI service for sharing. Just make sure the Excel source file is saved on OneDrive/SharePoint and you connect to it there using the Get Data from Web. That way it is automatically updated in the Power BI service.
Mynda
