• 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

PQ within Excel for the Web|Power Query|Excel Forum|My Online Training Hub

You are here: Home / PQ within Excel for the Web|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 QueryPQ within Excel for the Web
sp_PrintTopic sp_TopicIcon
PQ within Excel for the Web
Avatar
Peter Stevenson

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
January 3, 2021
sp_UserOfflineSmall Offline
1
February 11, 2021 - 6:26 pm
sp_Permalink sp_Print sp_EditHistory

Hello there,

 

I have a very simple PowerQuery within a spreadsheet that sources to a table within the same spreadsheet. The PQ works entirely fine in 'desktop mode' or 'desktop app'. However, the spreadsheet is also hosted within a Team (and thus SharePoint) and my less savvy colleagues have a tendency to look at spreadsheets through 'Excel for the Web' within Teams. The PQ is setup to 'Refresh data when opening the file'.

 

When colleagues (and myself) open the spreadsheet within 'Excel for the Web', they get the following messages in the screenshots. To circumvent this, I've also tried to connect to the table via 'From Web' and 'From Sharepoint Folder' although I cant get the 'Organsational' option which I think might have a shot of bypassing these error messages.

 

Would anyone have any advice on running PQs in Teams and/or 'Excel for the Web'?

 

Image Enlarger

Untitled.pngImage Enlarger

 

Untitled.pngImage Enlarger

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
February 11, 2021 - 7:54 pm
sp_Permalink sp_Print

Hi Peter, 

If you have the SharePoint credentials window asking for how you want to connect to the source data then that implies that the data is not in the same file i.e. you haven't used Excel.CurrentWorkbook to get the data.

What errors do you get if you use From Table/Range to get the data with Power Query?

Mynda

Avatar
Peter Stevenson

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
January 3, 2021
sp_UserOfflineSmall Offline
3
February 11, 2021 - 11:52 pm
sp_Permalink sp_Print

Hello Mynda, thank you so much for replying.

 

I should have clarified, the SharePoint window was a second route I went down to try and get data from the same spreadsheet I was creating the PQ on (an odd setup I know, I was desperate!).

 

The original PQ I created was a PQ from a table within the same spreadsheet. The first table are form results, however the answers are in a jumbled order, the PQ was just a resorting of column order so the answers appeared in the order they were in on the form (as new results from the Form are continuing to come in).

 

The query refresh works fine in 'desktop mode' but I get the 'External Data Refresh Failed' message in 'Excel for the web' mode. Bit of an odd message, as the data is within a table within the spreadsheet.

 

Image Enlarger

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
February 12, 2021 - 12:01 pm
sp_Permalink sp_Print

Hi Peter,

Turns out you can't refresh queries in Excel Online yet! You will have to open the file in the desktop to get updates, sorry. If I hear back from Microsoft with anything further I'll let you know.

Mynda

Avatar
Peter Stevenson

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
January 3, 2021
sp_UserOfflineSmall Offline
5
February 12, 2021 - 9:50 pm
sp_Permalink sp_Print

Thanks Mynda, I suspected as much. How annoying, getting my less IT savvy colleagues to open spreadsheets in Desktop mode is going to be a challenge.

 

Best Wishes,

 

Peter

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
6
February 13, 2021 - 9:39 am
sp_Permalink sp_Print

You could try using VBA to programatically open the file, refresh, save and close so your colleagues always have up to date data. 

Avatar
Jim Davis

New Member
Members

Power Query
Level 0
Forum Posts: 1
Member Since:
February 26, 2021
sp_UserOfflineSmall Offline
7
February 26, 2021 - 7:48 pm
sp_Permalink sp_Print

What's worked for me in the past is encrypting the file. You can leave the password blank if you want. Excel Web then forces the user to open the desktop app. No guarantees this will work forever -- MS will most likely eventually "fix" Excel Web to be able to handle encrypted files, but maybe by then they will also "fix" the PQ incompatibiliy too...

Avatar
austris bahanovskis
Member
Members
Level 0
Forum Posts: 14
Member Since:
July 13, 2021
sp_UserOfflineSmall Offline
8
July 13, 2021 - 3:34 am
sp_Permalink sp_Print

Hi Mynda et al,

Any chance there is an update/solution to refreshing PQ (or any external data for that matter) in Excel Online?

I'm reading this Msoft link from front to back and back to front and to me it suggests that it should be possible to refresh data when opening xl in browser (Excel Online I'm thinking...) but I'm no closer to the answer than when I started...

Would you have any guidance/check-list what should be checked/changed/adjusted for any connection in an xl wb would be refreshable if the wb is opened in a browser?

That would be very much appreciated.

Austris

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
9
July 13, 2021 - 11:54 am
sp_Permalink sp_Print

Hi Austris,

Currently you cannot refresh Power Query queries in Excel Online. Eventually you'll be able to, but we can't yet.

Mynda

Avatar
austris bahanovskis
Member
Members
Level 0
Forum Posts: 14
Member Since:
July 13, 2021
sp_UserOfflineSmall Offline
10
July 13, 2021 - 6:23 pm
sp_Permalink sp_Print

Thanks for getting back!!

It's a shame - it would be a massive boost to productivity. Fingers crossed.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Sonja Mason, Denise Lloyd
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Raj Mattoo
Mark Luke
terimeri dooriyan
Jack Aston
AndyC
Denise Lloyd
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Forum Stats:
Groups: 3
Forums: 24
Topics: 6219
Posts: 27276

 

Member Stats:
Guest Posters: 49
Members: 31903
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.