Excel Online Refresh

Question

I have a Canvas Power app that is connected to tables in Excel as a data source. I created a separate excel file to create reports using the data gathered form the App. Both the excel spreadsheet that is the data source as well as the file with the report tools are shared on One Drive. The reporitng file is shared with others in the company. When I am attempting to do a Data Refresh of my Power Queries in the excel file it is giving me this error:

Is there a workaround to be about to Refresh Queries in the Excel online version. So far I am only able to do so in my desktop version.

Thanks,

Emmy

Answer 1

Hi Emmy,

Thanks for posting in the community.

According to the error messsage, it contains "Unauthorized", and may be an authentication issue. Please try to clear the browser cache and cookies, then refresh the Power Query again.

If the issue still exists, I would like to collect more information.

1. Does the issue exist in a specific workbook or also in other workbooks with connections?
2. Do your colleagues have the same error message when refreshing the Power Query in Excel for Web?

When you have time, please feel free and share the updates with us.

Regards,

George

Answer 2

Good Morning,

Ok, I tried cleared the browser cache and cookies. I tried doing a Data Refresh of queries again and I got this error message:

So it looks like it is an "Unsupported Data Source- The workbook contains Power Query queries that can't be refreshed in Excel for the web." Do you have suggestions of what I could do to remedy this error. Others have the same error.

Thanks,
Emmy

Answer 3

Good Morning,

I got this error message:

So it looks like it is an "Unsupported Data Source- The workbook contains Power Query queries that can't be refreshed in Excel for the web." Do you have suggestions of what I could do to remedy this error. Others have the same error.

Yes, use "real", aka desktop, Excel.

Sorry, online Excel is too dumbed down to support PowerQuery. In other words, no PowerQuery in Excel Online/for the web.

.

oops ...

I googled to find confirmation looking for this page "Power Query data sources in Excel versions" https://support.microsoft.com/en-us/office/power-query-data-sources-in-excel-versions-e9332067-8e49-46fc-97ff-f2e1bfa0cb16 which itself contradicted me

.

Instead I first found this contradiction, then later found the above link ...

https://docs.microsoft.com/en-us/power-query/connectors/excel 

specifically this section, notice the "Authentication Kind" option/drop down box

<snip>

Connect to an Excel workbook from Power Query Online

To make the connection from Power Query Online:

1. Select the Excel option in the connector selection.
2. In the Excel dialog box that appears, provide the path to the Excel workbook.
3. If necessary, select an on-premises data gateway to access the Excel workbook.
4. If this is the first time you've accessed this Excel workbook, select the authentication kind and sign in to your account (if needed).
5. In Navigator, select the workbook information you want, and then Transform Data to continue transforming the data in Power Query Editor.

</snip>

So it seems to be possible.

Color me surprised.

Answer 4

Thank you for your response. However, I am still not having success with the Excel online refresh. Yes, when I open the desktop version I am able to "refresh" the data. No problems there. However, multiple people in the organization are wanting to access the online version of the file as its saved on One Drive and I would like the individuals to be able to do a "refresh" of the power queries to view up to date data. I am still getting an error that the online excel file is not able to refresh. I received this as a possible fix below: How can I check if the workbook uses secure, external data connections? The only connection is to another workbook also saved on One Drive.

Answer 5

Do these people with the problem have Excel desktop? It can open files saved in OneDrive, and do the update.

.

Or do they use Excel online because they are using small devices like phones and tablets?