cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ARnosa
Level: Powered On

Excel WorkBook's Queries & Connections doesn't work after connection with PowerApps

In my Excel workbook, I have few queries & connections from SharePoint. Everything work till I connect my file, using "One Drive for Business" connection, to PowerApps. After PowerAppsID columns are created, refresh function stops to work.I would like to know what kind of limitation is it. Captureasd.JPGCapturesdfgsdf.JPG

Community Support Team
Community Support Team

Re: Excel WorkBook's Queries & Connections doesn't work after connection with PowerApps

Hi ARnosa,

 

According to your description, I create a query that imports data from SharePoint list.


Then I upload the Excel table to OneDrive for Business.


Before I connect the Excel table to PowerApps, I checked the Refresh button.


When I open the Excel table Online, right-click anywhere in the data range, I found that the Refresh button disappeared. While on client, it works fine.

3.PNG

Then created a connection from PowerApps to the table, then a PowerAppsID column will be auto-generated. Went back to check the Refresh button, there was no Refresh button when I opened the table online, while on client, the refresh button worked as previous.

 

I am afraid that to refresh Excel Power query, we need to open the Power Query Excel on the client but not online.

 

Are you trying to refresh the Power query online?

 

Hope this could be a reference for you.

Best regards,
Mabel Mao

ARnosa
Level: Powered On

Re: Excel WorkBook's Queries & Connections doesn't work after connection with PowerApps

Thanks for the answer. I am trying to refresh in the client. Please try to refresh after it's connected with PowerApp. Refresh button doesn't disappear, just is disabled, and it happens right after connection with PowerApp. I tried OneDriveforBusiness, GoogleDrive. 
Seems that all connections and all sheets/tables(even those which are not connected with PowerApp) are deprecated after connection. 

Highlighted
cmcilwain
Level: Powered On

Re: Excel WorkBook's Queries & Connections doesn't work after connection with PowerApps

I have also encountered this issue. It appears that the link between the spreadsheet table and the external data connection is lost after the dataset is added as a PowerApps connection.

 

The spreadsheet data connection still exists and can be refreshed from power query but the tables will not update.

 

If you open the OneDrive spreadsheet in Excel and view where the connection is used from the workbook connections dialog you will find that the connection is no longer used.

 

My only work-around so far is to keep a local copy of the spreadsheet, create the connection then use VBA to refresh the connection, copy the updated data and paste as static values to another table then save the workbook to OneDrive.

 

This is the importnat part - after you have made the PowerApps connection you can then copy the "__PowerAppsId__"  column and add that to the local copy of the static table.

 

It sounds a lot more complicated than it is, effectively you open the local copy and one button press refreshes the data connection and saves it to OneDrive so updating the workbook and allowing PowerApps to conect to the new data.

 

An elegant solution it isn't but it may be useful to some!

stumac_31
Level: Powered On

Re: Excel WorkBook's Queries & Connections doesn't work after connection with PowerApps

I'm having this same issue, has anybody found a solution to this that does not involve using static tables? At the moment my PowerApp is pretty useless unless I can fix this....