• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Query - sharing via Microsoft 365; which app to choose so users can refresh, easily navigate...|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Query - sharing via Microsoft 365; which app to choose so users can refresh, easily navigate...|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryQuery - sharing via Microsoft 365; …
sp_PrintTopic sp_TopicIcon
Query - sharing via Microsoft 365; which app to choose so users can refresh, easily navigate...
Avatar
Nesha St
Belgrade, Serbia
Member
Members
Level 0
Forum Posts: 10
Member Since:
September 13, 2020
sp_UserOfflineSmall Offline
1
September 13, 2020 - 4:34 pm
sp_Permalink sp_Print

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!

sp_AnswersTopicSeeAnswer See Answer
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4436
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
September 13, 2020 - 5:05 pm
sp_Permalink sp_Print

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

Avatar
Nesha St
Belgrade, Serbia
Member
Members
Level 0
Forum Posts: 10
Member Since:
September 13, 2020
sp_UserOfflineSmall Offline
3
September 13, 2020 - 7:03 pm
sp_Permalink sp_Print

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

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4436
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
September 13, 2020 - 8:45 pm
sp_Permalink sp_Print

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

sp_AnswersTopicAnswer
Answers Post
sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Hans Hallebeek, Riny van Eekelen, Amin Khajeh
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Lawrence Miu
Jo Saies
Paul Pritchard
Monique Roussouw
Keith Aul
Richard Dunks
Manjula Mangalmurti
Thomas Quidort
Jamie Preece
Bob Smith
Forum Stats:
Groups: 3
Forums: 24
Topics: 6192
Posts: 27146

 

Member Stats:
Guest Posters: 49
Members: 31848
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.