Data Connection Refresh Error

[Paul_Castelino]

Hi. I have successfully been able to import data (in File No. 1) from another excel file (File No. 2). Both these files are stored in Sharepoint and are collaborated with a team. These files also can be accessed thru the Files Tab in a Teams Channel. These files are also stored on my Laptop and can be accessed via OneDrive/Sharepoint. When I open the file from my laptop and perform Data Refresh , the connection is successful and the refreshed data gets imported (i.e. New Data from File No. 1 to File No. 2). When I access the same file thru Teams (or excel online), I get a refresh error as below.

Other users who try to Refresh the File (File No. 2) also get the same error. Appreciate if I can get a solution.

[#2]

This is likely due to the fact that PowerQuery created a connection to a file stored on your local machine, but needs to refresh from a file which in reality is on a https location. See https://excelexciting.com/get-data-from-onedrive-excel-file-using-power-query/

[#3]

Hi Jan,
Thanks for your reply. The file address is a Sharepoint path and not a local path. It has been connected just like the instructions in the link you have sent.

[#4]

OK, pity. In that case I'm not sure what gives.

[#5]

@Paul_Castelino 

One of possible reasons if in desktop application privacy level were ignored. Cloud services ignore such ignorance and take about the privacy levels. 

That's easy to check - on desktop change setting on Combine privacy levels and Refresh All. If works - something else. If not - rebuild your query.

[#6]

@Sergei Baklan 

Thanks for your reply. Could you guide me on how to Combine privacy levels and could not find such an option in Excel.

[#7]

@Sergei Baklan 

I found the settings as attached. Looks okay but still does not refresh data when accessed on online version.

[#8]

@Paul_Castelino 

You may try on another computer in local folder(s) and desirable not in your environment. If error, perhaps it tries to connect to your laptop or on-premises source.

[#9]

@Sergei Baklan 

I accessed the excel file from my Mac thru TEAMS, File Tab. Also accessed thru web browser from my Mac. When I do a Data Refresh I get the same error. 

[#10]

@Paul_Castelino - Did you every resolve this and, if so, can you point me in a direction?  I am dealing with the same issue right now.  

[#11]

@Curtis Beene Since I have not received any support from Microsoft, switched back to Google Sheets.

[#12]

@Paul_Castelino 

You cannot refresh Power Query queries in Excel for the Web today (same applies for Teams which uses Excel for the Web internally). We are working on this, but it will take a while until we get there.

I encourage you to follow the announcements at the Excel blogs channel here on this portal:

Excel Blog - Microsoft Tech Community

Guy

- Excel Team

[#13]

@Guy Hunkin 
Just wanted to say that I have the same problem as the OP (meaning I'd like the same feature).
I understand it's not working yet, hope you can get it soon.

One of the cool usages I've always thought I'd want Teams Excel tabs to be is displaying "simple dashboards" based on Excel files. I guess there are smarter ways of doing that, but as of now we tend to build those using self-refreshing Queries (not even Power Query, just simple Excel to Excel Queries).

Not being able to refresh those in Teams means the idea of the "simple dashboard" just fell into pieces.

[#14]

Hello! See it has just been over a year since this comment was posted. I was curious if there has been any update regarding this matter? I am also trying to use the power query function in Teams and would love to be able to.

[#15]

Hi @ConnorMurphy! It is a complicated project hence the progress is slow. But the work is in progress!

If you followed the news, you have probably noticed that we released a new Queries side pane in Excel for the Web where you can view the queries in your workbook with some additional metadata like the last refresh date and etc. More functionality is coming soon. Here is the link to the latest announcement for you convenience:
https://techcommunity.microsoft.com/t5/excel-blog/what-s-new-in-excel-for-the-web-december-2021/ba-p...

Guy.

[#16]

@Guy Hunkin 

"More functionality is coming soon."

Hope the "soon" will be soon, thanks.

[#17]

@Guy Hunkin- Would you be willing to respond to this inquiry to let us know if this problem has been resolved since you posted about it in December 2020.   I'm having a hard time refreshing a query via ExcelOnline or in Excel under the Teams wrapper.  (Manually or via OfficeScript) 

Only works if I open the workbook. 

Thank you.

[#18]

@BCH78 Don't hold ya breath! lol

[#19]

@BCH78 No updates from Microsoft. Switched over to Google Sheets and it was simple and seamless....lol...

[#20]

Hey all,
We appreciate your patience!
I can share we have now released the ability to refresh Power Query queries in Excel Web / Teams, for 2 data sources: From Table/Range and (anonymous) OData feed.
We are continuing work to support more data sources and refresh automation - stay tuned...
Happy for any feedback.