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

[Anonymous]

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. 

[v-yamao-msft]

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.

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

[Anonymous]

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. 

[cmcilwain]

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]

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....

[AlCado]

Hi,

I'm exactly, precisely, faithfully in the same situation !

Has anyone found a workaround ?

Regards

[stumac_31]

After a couple of months of getting shunted between the Office 365 and PowerApps support teams, this is what I got (precise issue detailed below):

The issue you are facing is known limitation that the SDK excel connector uses does not support external data connections.

I have taken this to mean the table cannot have a connection to Power Query and PowerApps at the same time, but it makes no sense to me as the Power Query itself updates, its just the table that fails to refresh. I have not been able to find a work-around yet, have been trying to explore Macro and VBA options to force a refresh but not having much joy as PowerApps does not like Macro-Enabled workbooks..

Issue:
Excel workbooks which use Power Query to create tables which are then linked to Power Apps. However when workbook is linked to Power Apps the tables fail to refresh despite the query showing the refreshed data.

Observations:
• You are updating the Excel with the power query, its working fine.
• When you connected the excel as data source for a form in app[Power apps], the connection between the power query and the excel seems to be lost and the table is not getting updated.

References:
https://powerusers.microsoft.com/t5/General-Discussion/PowerApps-connection-to-a-Data-Connected-Exce...
https://powerusers.microsoft.com/t5/General-Discussion/Excel-WorkBook-s-Queries-amp-Connections-does...

[AlCado]

Ouch

I came to the same conclusion but and was still hoping for a trick but I think I should find another solution !

Thanks for sharing,

Regards

[Anonymous]

@Audrie-MSFT This is still a major issue.  I turned to an Excel on OneDrive for Business after having too much trouble cleaning up complex column types (that I'm not allowed to change at the moment) directly from SharePoint into PowerApps.  Instead, I queried the SharePoint from Excel to extract the records in each column as simple text.  But refreshing the Excel to get latest from SharePoint then changes table name (adding "_1"), which breaks PowerApps ability to connect.  If this is "defective by design" to force a CDS upgrade, it's only going to keep me from demo'ing enough basic functionality to prevent initial adoption of PowerApps at all...

And of course, there is the issue of it being too difficult for PowerApps to work with complex SharePoint column types; yes these should be avoided in principle but in an org with established data sources it's not always possible.  Instead, I've gone a bit crazy trying to find the right alchemical combination of First/Concat/Concatenate/.Value/ForAll....Why not a "Simplify" function that turns all the records in a column to their text equivalent, or works with "AddColumns" to do so?

[stumac_31]

Did you ever find a workaround for this?