There are occasions where it's necessary toto migrate the data source of an app. A typical example is where an app builder builds an app that's based on an Excel data source. When the amount of data that app stores exceeds 2,000 records or 2MB, a need arises to migrate to a more capable data source. SharePoint is often the most natural choice, as it requires no additional licensing for Microsoft 365 users.
To demonstrate the data migration process, this post walks through the steps to modify the sample Service Desk app to use a SharePoint data source, rather than the default Excel data source.
Steps to migrate a data source
To change the data source for an app, there are 4 primary steps. These are:
- Migrate the data from Excel to SharePoint
- In Power Apps, delete the existing data sources from Data panel
- Add the SharePoint datasource
- Fix any errors that arise
We'll now go through these steps in more detail.
Step 1 - Prepare the app and migrate the data
This reveals the tables in the app, as shown in the screenshot beneath.
The next step is to create the 7 SharePoint lists that will replace the orginal 7 Excel tables. The easiest way to do this is to import the source Excel tables from SharePoint, as I describe here
To make this process as simple as possible, we should name the replacement SharePoint lists with names that are exactly identical to the Excel tables. This will minimise the need to apply additional modifications to formulas that reference the source data.
Step 2 - Delete the Excel tables from the app
Once we import our Excel data into SharePoint, we can delete the Excel tables from the app. The screenshot beneath shows the appearance of the app after we delete all the tables.
Step 3 - Add the replacement SharePoint lists
Next, we click the 'Add data' button to add the replacement SharePoint lists..
Step 4 - Resolve the errors using the App Checker tool
After we add the SharePoint tables, we can fix any errors by working through the App Checker list. The screenshot beneath shows that there are 60 errors.
Many of these relate to invalid formula references because I named the replacement SharePoint list 'FilterBy', instead 'Filterby'. This difference in casing invalidates the existing formula in many places, and I recreating the SharePoint list with the name 'Filterby' to correct the errors . This provides a good reminder to make sure to create SharePoint lists with names that match the exact case of the source Excel tables.
The next set of errors in the App Checker list relate to 'incompatible type' failures when setting the variable 'type' on the HomePage. To resolve these errors, we can use the Variables section of the designer and review all the places that set this variable.
We discover that many of these errors are caused by a difference in field names. The orginal formula referred to the 'Filter By' field as 'Filter_x0020_By'. The '_x0020_' identifier is what we use with Excel to specify a space in the field name. With SharePoint, we can use the friendlier idenfier 'Filter By' instead, and repacing all instances of 'Filter_x0020_By' with 'Filter By' fixes the remaining issues.