The Power Apps designer provides no way to rename tables/data sources but thankfully, the introduction of source code editing makes it possible to carry out this task. This post describes why it's useful to rename data sources, and walks through the process of how to carry out this task.
Why is it important to be able to rename tables/data sources?
The ability to rename tables/data sources can be very useful for a number of reasons.
First, it makes it easier to migrate the data storage of an app to a different storage provider. Let's say we build an app that uses an Excel data source. When we reach the limits of Excel and decide to modify the app so that it uses a SQL data source instead, the process can be very time consuming without the abiltiy to rename the original data source.
Once we add the new SQL data source to our app and delete the old Excel data source, it's a painstaking task to rename every control and formula reference to the old Excel data source with the new SQL data source name. Renaming the new SQL data source to match the name of the old Excel data source simplifies this task.
Second, when we delete and re-add a data source (particularly with apps that are corrupt), Power Apps may re-add the data source with a numeric suffix, or a name that we prefer not to use. The ability to rename tables/data sources can help tidy the formulas in our apps.
Third, a recent change to the naming convention that Power Apps uses to identify SQL tables and views can cause difficulties for app builders with existing apps. The ability to rename problematic data sources can help mitigate issues.
How does the new SQL naming convention look, and why is it a problem?
Traditionally, when we add a SQL Server table (or view) to an app, Power Apps adds the table using a naming convention that includes the schema name and table name. For example, if we add a SQL Server table called 'Issue' to an app, Power Apps assigns the identifier '[dbo].[Issue]'.
Following an update to Power Apps from two weeks ago, the naming convention has changed. When we now add a table, the identifier no longer includes the schema name and table name in square brackets.
The screenshot beneath shows an app that was created several months ago. The existing tables are called '[dbo].[Issue]' and '[dbo].[Property]'. If we now add a SQL Server table called Client, we see that Power Apps names this table Client, rather than '[dbo].[Client]'.
Why is this a problem? The biggest cause of contention is that there are instances where we need to remove and to re-add a data source, particaularly when apps misbehave or behave in an unexpected way. Because we now cannot re-add a table and retain the existing name, we need to spend significant time renaming controls and formula that reference the old data source.
The second problem is that as we develop an existing app, we'll end up in a mess, where half the objects in an app use the old naming convention, and the remaining objects use the new naming convention. This applies particularly with SQL Server views, because app builders frequently create and modify these objects, in order to resolve delegation limitations.
To minimise the problems that arise from this new naming convention, a workaround is rename any new SQL Server objects that we add, so that they use the old naming convention.
The remainder of this post describes how to rename data sources from two perspectives:
- How to rename a new data source to use the old naming convention (eg, rename 'Client' to '[dbo].[Client]')
- How to rename an existing data source to use the new naming convention (eg, rename '[dbo].[Issue]' to 'Issue'), and to rename all formula/control references to use the updated name.
Extracting the source code for an app
The first step is to extract/unpack the
source code for the target app. We save our app as a MSApp file, and call the pasopa utility to unpack the files. The command looks like this:
pasopa -unpack C:\PowerApps\MSAppFiles\SQL-Property-App.msapp
My previous post describes how to install the pasopa utility, and how to unpack an msapp file.
To carry out this task, it's very important to use the latest version of pasopa. If we unpack and repack an app using the initial release of pasopa (v0.14), the resulting app will fail to load in Power Apps. The version that I've used for this post is v0.2.
Once we unpack our msapp file, we can open it in Visual Studio Code or a text editor of our choice. For this example, we've extracted our files to C:\PowerApps\ProjectFiles\SQL-Property-App.
How to rename a table/data source
To demonstrate how to rename a data source, here's how to rename the 'Client' data source to '[dbo].[Client]'. From the source code, there are 3 places that we need to edit.
- The DataSources\<TableName>.json file
- The Connections.json file
- The pkgs\<TableName>.json file
The first file to edit is the 'data source json' file that corresponds with the table/data source. The DataSources folder contains a json file for each table. We locate the file that corresponds with the Client table (Client.json), and modify the Name attribute.
The value of the name attributue is "Client", and we change this to "[dbo].[Client]".
The second file to edit is the Connections\Connections.json file. The dataSources section contains the data source name "Client". We change this to "[dbo].[Client]"
The final file to edit is the pkgs\Client.json
file. We change the EntityName value from "Client" to "[dbo].[Client]".
For tidyness, we can optionally rename the json files so that they match the format of the other data source files (eg, rename Client.json to [dbo].[Client].json), but this is not mandatory.
Finally, we repack our app by calling pasopa with the pack switch, like so:
pasopa -pack C:\PowerApps\MSAppFiles\SQL-Property-App-New.msapp
We can now open our msapp file in Power Apps Studio and as the screenshot beneath shows, the 'Client' data source has been successfully renamed to '[dbo].[Client]'.
For more details on the files that define tables and data sources, we can refer to my post here:
How to rename a table/data source and to update associated controls and formulas
how to rename a data source and to update existing references in controls and formulas, here's how to rename the '[dbo].[Issue]' data source to 'Issue' (that is, to rename a data source from the old naming convention to the new naming convention).
Similar to before, we change '[dbo].[Issue]' to 'Issue' in the following 3 places:
- The DataSources\[dbo].[Issue].json file
- The Connections.json file
- The pkgs\[dbo].[Issue].json file
Next, we use the Search bar in Visual Studio Code to search all places that contain the original data source name ([dbo].[Issue]). We can replace these instances with the new data source name ('Issue' in this example).
Note that there will be many matches. The specific places we need to modify are formula references in the screen YAML files.
Once we complete all the modifications, we can re-pack our app and open it in Power Apps. As the screenshot beneath shows, the two existing data sources have now been renamed to comply with the new naming convention.
ConclusionWe can rename data sources by editing the source code of a canvas app. There are 3 files that we need to edit - the DataSources\<TableName>.json, Connections.json, and pkgs\<TableName>.json files.
Renaming a data source in this way can mitigate problems that arise due to the new SQL Server naming convention, and it can also simplify other tasks such as migrating an app to use a different data storage provider (eg, moving an app from Excel to SQL).