Data - A walkthrough of how to migrate the data source of an app from Excel to Sharepoint
How easy is it to migrate the data source of an app from from Excel to SharePoint? This post describes the steps that are needed to carry out a data migration, and provides a demonstration by walking through the steps to migrate the sample Service Desk app from Excel to SharePoint.
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
- 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
Step 1 - Prepare the app and migrate the data
Step 2 - Delete the Excel tables from the app
Step 3 - Add the replacement SharePoint lists
Step 4 - Resolve the errors using the App Checker toolAfter 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.
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.
Final Step - Test the app
ConclusionWhen we need to change the underlying data source for an app, we can accomplish this by migrating the data to a new data source and choosing table/list/field names that closely match the original data source. We then delete the original data sources from the app, and add the replacement data sources. Most likely, there will be errors that we need to correct, and we can locate these through the app checker tool. For reference, it took me just under 1hr to migrate this sample app with 7 tables from Excel to SharePoint.
- Data - Combine columns from separate tables into a single table
- Formula - Transposing/converting rows to columns- an almost impossible task?
- Data - How to rename field names in a record
- Data - How to hide duplicate rows in a gallery / show distinct multiple columns in a gallery
- Data - Retrieving news/forum/blog articles with RSS
- Data - How to sort by partial numbers in a text field
- Data - How to return the last record from a table
- Data - How to create bulk test/dummy records with random values
- Data - 3 things you should know before using the MySQL or PostgreSQL connectors
- Data - How to enforce unique values (or prevent duplicate values) in one or more columns
- Data - How much mobile data does Power Apps consume? What ways can we minimise this?
- Data - How to save and retrieve Google calendar entries
- Data - How to save and retrieve Google contacts
- SQL - Caution! This is how users can hack shared SQL connections
- SharePoint – 2 Mistakes to avoid when importing Excel data
- SQL - Don't let this DateTime bug catch you out!
- Settings - What's the purpose of the "Explicit Column Selection" Setting?
- SQL Server for Beginners Part 3 - Installing On-Premises Gateway
- SQL Server for Beginners Part 2 - Installing Management Studio
- SQL Server for Beginners Part 1 - Installing SQL Server
- Searching data–What you need to know about case sensitivity
- Images - How to create images that can change depending on data
- Excel - Reasons NOT to use Excel as a data source
- SharePoint - What you need to know about Filtering Data
- Formulas - Generating Row Numbers