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'?
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
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.
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
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
You could try using VBA to programatically open the file, refresh, save and close so your colleagues always have up to date data.
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...
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
Hi Austris,
Currently you cannot refresh Power Query queries in Excel Online. Eventually you'll be able to, but we can't yet.
Mynda
Thanks for getting back!!
It's a shame - it would be a massive boost to productivity. Fingers crossed.
