Although this article would be a great fit for my series of articles on Migration Pitfalls, I decided to add it to my Power Platform articles, because it mainly focuses on the usage of Power Automate to support a migration to SharePoint Online.
Lift and Shift Migrations
Those of you who know my talks on SharePoint migrations know, that I am not a big fan of lift and shift migrations. Sure, there are some scenarios where moving data from source to target without any changes could make sense, but often lift and shift is used because organizations think they can save some money. Unfortunately, often that's not the case and the decision to perform a lift and shift migration turns out to be a misjudgment. Honestly, that is the main reason why I usually advise against lift 'n' shift migrations. I have written this article because I want to give you an example of how a substantial improvement can be achieved with a little bit of extra effort.
Initial Situation
Let's assume there is a fictitious company and this company uses a corporate file share for some years, but now they decided that they want to migrate to Office 365 and SharePoint Online. Basically, this is a very common "File-share to SharePoint Online migration" scenario. But here is the tricky part: this organization maintains some Excel spreadsheets which need to be migrated to SharePoint Online as well. Similar spreadsheets are used by multiple departments to maintain lists of contacts, inventories of different kinds, etc. Their first approach was to just migrate the spreadsheets to a Document Library and continue to utilize Microsoft Excel. To me, this is using SharePoint Online as an improved file-share in the cloud and detracting from most of the benefits and improvements that this modern platform offers. After my assessment, I advised against this approach. Instead, I presented a solution that allows migrating the data that is hosted within the Excel spreadsheets to SharePoint generic lists while rearranging some columns and updating items. For sure, I recommended using Managed Metadata and Content-Types to ease managing the lists and prepare for the roll-out of Project Cortex once available.
Power Automate Approach
Although Excel spreadsheets can be migrated to SharePoint generic lists almost out-of-the-box, I decided to use Power Automate to provide options to update, change or edit the data, before saving the data to a generic list. Power Automate (formerly known as Microsoft Flow) provides many activities that can deal with Excel spreadsheets:
My approach takes advantage of those powerful activities. Let's have a look at some scenarios and how Power Automate can be used to automate those.
Listing Rows in a Table
To be able to update and migrate specific data items from an Excel spreadsheet to a SharePoint generic list, first, we need to read the data rows from tables within the Excel spreadsheets. The following screenshot shows how to do that:
In this example, the spreadsheet is opened from a Document Library and all rows from table Table1 are read. This activity returns a collection of rows, but to be able to deal with individual rows, an Apply-to-each loop needs to be used:
The loop takes the output value from the previous activity and utilizes a Parse JSON activity to extract rows and columns. In a previous article, I already explained how to use the Parse JSON activity. Please have a look there if you want to know how to use this activity. If you look at the Send Notification activity, you'll see how to use the columns and rows extracted by using the Parse JSON activity. Basically, utilizing my Swiss-Army-Knife activity Send Notification is used to ensure, that data as extracted by the Parse JSON activity can be used to perform data manipulations and to save the data to SharePoint.
In my example, I use a very basic Excel spreadsheet that looks like this. Admittedly, this is a rather basic spreadsheet, but as I want to showcase the fundamental idea, I decided to keep things simple.
Once the data has been extracted from the columns, it can be updated, edited or re-formatted (CRUD operations), before it is saved to a SharePoint generic list. If you wonder, how the notification output looks like - here it is.
Migrating the data to SharePoint
Once the Power Automate workflow is able to access the data, we can add activities to manipulate the data as well. Once the data is in the correct format, it can be saved to a SharePoint generic list.
Saving the updated data to a SharePoint list is easy to achieve as the following image shows:
In my basic example, I have created a Contacts list in SharePoint (based on the generic list template) and added two columns (First Name and Phone). After that, I renamed the Title column to Last Name. The SharePoint activity Create item can be used to migrate the data items from the Excel sheet to SharePoint. In my example, I didn't perform any data manipulation as I want to explain the procedure, but the major benefit of this approach is that it offers great options to manipulate (or even enrich) data sets, before they are migrated to SharePoint - especially if many similar spreadsheets are used.
After this activity has been finished, the SharePoint list looks like this:
No big surprise as this was expected :-)
Manipulating Excel Spreadsheets
The major benefit of this solution is, that it offers opportunities to update and enrich the data before it is saved to SharePoint. Sometimes, this requires changes applied to the Excel spreadsheet as well. Let's have a look at how this can be done with Power Automate.
The following screenshot shows, how an additional row can be added to an existing table:
This activity adds a data set (Peter Franks) to Table1. Once this activity has been finished, this is how the table looks like - the new row has been added as expected:
Power Automate can also delete rows from existing tables. The following screenshot shows how to use the corresponding activity. Here I delete a row from the table. The row is identified by a key column (Column1 holds the last name) and the key value - in my case the last name Franks.
As we are looking at CRUD operations for tables, let's have a look at how an existing row can be updated. The following example shows how to update the phone number of the contact Peter Franks:
Power Automate can even create a new table within an Excel spreadsheet, which can be handy when manipulating data before migrating it to SharePoint. The following screenshot shows how that looks like:
Here I create a new table called Sales within the area D20-F23 in the existing spreadsheet. The new table has the columns Week, Product and Sold Items. Here is how the new table looks like:
Listing existing tables works similar to listing existing rows. As shown before when working with rows, listing tables also require the Parse JSON activity. This is how this looks like:
Conclusion
As I mentioned at the beginning of this article - very often migrations to SharePoint offer great opportunities to improve efficiency. Instead of continuing to use Excel spreadsheets with the Excel desktop application, Power Automate is used to update and migrate data from Excel spreadsheets to SharePoint generic lists. This will provide additional benefits to the entire staff because after the migration the data can be accessed and used without the need to utilize Excel. In addition, access permissions can be applied in a much more granular way and mobile devices can be used as well. This approach becomes even more beneficial if multiple spreadsheets using a similar data layout are used and/or if the data needs to be enriched (like joined with other data). I think you will agree that the little bit of extra effort spent on creating a Power Automate workflow pays off instantaneously as the new solution integrates with SharePoint completely, provides much better efficiency and improved user experience. Using the new UI flows would have been another option, but I decided that this would be a little too early as they still appear to be in preview mode. Still, UI Flows would be a great alternative to achieve the same goal.
Comments