If you use a connection file to connect to a data source, Excel copies the connection information from the connection file into the Excel workbook. Connection files are particularly useful for sharing connections on a consistent basis and for facilitating data source administration. Each time that you refresh data, you see the most recent version of the data, including any changes that were made to the data since it was last refreshed.Ĭonnection information can either be stored in the workbook or in a connection file, such as an Office Data Connection (ODC) file (.odc) or a Universal Data Connection (UDC) file (.udcx). When you are connected to an external data source, you can also perform a refresh operation to retrieve the updated data.
#Excel queries and connections tutorial how to
The external data source is connected to the workbook through a data connection, which is a set of information that describes how to locate, log in, query, and access the external data source. The data may be stored directly in the workbook, or it may be stored in an external data source, such as a text file, a database, or an Online Analytical Processing (OLAP) cube. Instead, I've gone a bit crazy trying to find the right alchemical combination of First/Concat/Concatenate/.Value/ForAll.Data in an Excel workbook can come from two different locations. 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.Īnd 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. But refreshing the Excel to get latest from SharePoint then changes table name (adding "_1"), which breaks PowerApps ability to connect. Instead, I queried the SharePoint from Excel to extract the records in each column as simple text. 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.
However when workbook is linked to Power Apps the tables fail to refresh despite the query showing the refreshed data. 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.Įxcel workbooks which use Power Query to create tables which are then linked to Power Apps. 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. The issue you are facing is known limitation that the SDK excel connector uses does not support external data connections. 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.Īn elegant solution it isn't but it may be useful to some!Īfter a couple of months of getting shunted between the Office 365 and PowerApps support teams, this is what I got (precise issue detailed below): 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. 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. 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. The spreadsheet data connection still exists and can be refreshed from power query but the tables will not update. 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.
0 kommentar(er)
