In a previous article I have detailed the potential risks & common pitfalls when using SharePoint as data-source for your important/mission-critical applications built using Power Apps. Here is a direct link The potential risks & common pitfalls when using SharePoint as data-source in your important/mission-critical apps built using Power Apps/ Automate | LinkedIn if you have not already I suggest you read the previous article so you can understand the importance and the reasoning behind performing such migration

In most cases such transformation will require two main steps. The first step is to migrate the data from SharePoint to Dataverse and the second step is to reconfigure the app to use the new data-source.

In this article I will go through the one of the best ways to migrate your data from SharePoint Lists to Dataverse tables and the nuances around that. I will leave the reconfiguration of the app to for a future article.

The amount of work required to accomplish the migration depends on the complexity of the data and security model of the app and by that I mean the number of SharePoint Lists the data is stored in, the relationships between those lists and the use of complex column types, and finally the RBAC model if applicable.

The easiest and the most straightforward way to move your data from SharePoint to Dataverse is to use Power Platform Dataflows. Power Platform Dataflows are able to automatically create tables in Dataverse based on the schema of the SharePoint Lists and migrate your data over, however there are some caveats and manual work needed to handle complex SharePoint field types i.e. Lookup, Multi Value Lookup, Choice and of course the RBAC model might require configuration (compared to SharePoint creating sophisticated scalable RBAC model in Dataverse is a matter of clicks).

I will try to cover most common cases in the following scenario. While having one SharePoint list that does not have any complex column types is surprisingly a very recurring scenario. However, I will use a more complicated scenario to cover most common nuances when planning such migration

Travel Request Scenario

Scenario Elements

1) Canvas App for Employees to submit travel requests, it uses the travel request SharePoint list as its main data source.

2) Travel Requests SharePoint list, the travel list has among other columns two complex column types “Department” a column of type lookup (Single Value) and Cost Centers lookup column (Multi-value), see the ERD above to better visualize the data-model. Of course SharePoint does not support many-many relationships, it fakes its way around it with the multi-value lookup field storing a string containing pairs of ID and Title separated with “;#”. In some implementations people go the extra mile of creating an in-between list holding the foreign key from both lists.

3) Departments SharePoint list, this list contains departments. A travel request can be associated with 1 department, a department be associated with many travel requests

4) Cost Centers SharePoint list, this list contain cost centers, a travel request can have 1 or more cost centers associated with it, and a cost center can also be associated with many travel requests

In this scenario we will be setting up the data-flow to create the required tables in Dataverse (based on SharePoint schema and also migrate the data over..

Setting the Data Flow

To setup the data flow you need to navigate to make.powerapps.com

Select Data => Dataflows from the left navigation and then select New dataflow

You will need to provide your dataflow a name, in this example “MoveTravelRequestsData” and select Create

Next step you will be choosing the data source in our scenario here you need to select SharePoint Online list.

SharePoint Online list connector will require a Site URL (The URL of the SharePoint website hosting your data, for example https://xxxx.sharepoint.com/sites/PowerPlatform/) and if you haven’t create one already it will also require creating a connection using credentials of an account that has rights to access the desired data in the SharePoint site. After supplying the information click Next..

Select the SharePoint lists where your app is storing data and also any lookup lists associated. For this scenario I select Travel requests (Main list) and Cost Centers and Departments (Lookup Lists) and then click Transform data

For each of the queries created to import the data from the lists, make sure to select the columns that you need. This can be different from one scenario to another but definitely you don’t want to move data(columns) you don’t need at the destination.

A few additional things to pay attention to here

1) Make sure you select the right data type for your columns, dataflows will default to Any Text as data-type which is not desired for fields such has Date, Integer,… For the purposes of this scenario I made to sure to set “Whole number” data type for all ID columns and also Date for the Travel Date Column in the Travel Requests list

2) For Lookup field with single value, make sure to pick the field containing the ID as string in this scenario Departmentid and not the field that returns data type as table (Departments), you will need this later to properly setup One-Many relationship between tables in Dataverse

3) For Look-up field allowing multi-value, the data type will be list, make sure to use Power Query Transformation to convert the column to a String with all values delimited, you will need this later to setup Many-Many relationship in Dataverse.

#"Added custom" = Table.AddColumn(Navigation, "CostCentersString", each Text.Combine(List.Transform([Cost CentersId], each Text.From(_)), ","))

For further reading on Power Query M see this reference Powe Query M function reference – PowerQuery M | Microsoft Docs

1) Now since we are aiming to leverage the data flow for provisioning tables in Dataverse, under load setting selected Load to new table, and specify new table name. of course if you had created the tables previously you can select Load to existing table.

2) Under Column mapping make sure to select unique primary name column, for example I choose the Title column from Departments and Cost Centers

3) Verify the destination column types are as expected

Repeat the three steps for every query under the queries section, and then click on Publish

Wait for the Data flow to be published, once published it will automatically run a refresh. One done make sure the Last refresh status is Green (Successful)

Post Dataflow run steps

Now you can navigate to your Dataverse tables page and examine the created tables and check on the data. Of course if you decided you need changes to the migrated data you can edit the Dataflow and re-run a refresh. I have added LinkedIn to the table name so I can find easily when preparing this blog post. See screenshot above for all tables created automatically using the data flow :). Just imagine how much time you will save specially when migrating many apps utilizing many SharePoint lists

Next step would be to verify data was moved over as expected.

If the SharePoint lists in the Data source did not include complex column (More common scenario than you think), you would be done by now.. However I chose to implement a more complicated scenario to cover some of nuances you may need to pay attention to for some apps

Dealing with Lookup Columns – Single Value

Then you can use a Power Automate flow to set the Lookup Column value based on the value stored in the Departmentid field. This will set the lookup column value for all records. You can of course improve on the flow below by adding Try-Catch and also add a status field to use as filter to process only unprocessed records.

As you can see from the screenshot above I tried to parametrize the flow to make it reusable by providing four variable Main Table Name, Main Table Lookup Column, Lookup Table Name, Lookup Table ID Column.

The screenshot above should help you recreate flow easily, Of course happy to answer questions and provide more guidance on the Power Automate flow creation but since its not the main topic of this article I will stop here.

If you want to learn more on setting Lookup column in Dataverse using Power Automate, Follow the link below to watch a great video created by the great Shane Young

(96) Power automate Dataverse Lookup – How to write to the hard column – YouTube

Dealing with Lookup Columns – Multi Value

Surprisingly this is simpler to achieve compared to dealing with single value lookup 🙂

All you need to do is add Many to Many relationship between your main table (Travel Requests) and lookup table (Cost Centers). This won’t required creating an extra fields in the TravelRequests table.

Given we have imported Cost Center Value in a delimited string, we can use this value stored in this field to relate Travel Requests with all associated Cost Centers using a Power Automate flow, and specifically the Relate Rows action Relate or unrelate rows in Dataverse – Power Automate | Microsoft Docs. See below..

Once you execute the flow you can validate the data by adding a Subgrid to Travel Requests Main form to render the related cost centers. See below

After doing some data validation tests you can go ahead and delete the extra columns, in this case Departmentid (Replaced by Department Lookup Field) and Costcenter(storing string delimited cost center values) as this one is now replaced by proper Many to Many relationship)

I will post a solution containing both flows so you can reuse, I will add a link here shortly

Recap

I think by this we have covered the majority of scenarios you will face for when moving your Data from SharePoint lists to Dataverse Tables. In the next article I will cover the changes you will need to do in the app and the best practices to insure its a frictionless experience.

A quick summary of the steps followed in the article above

1) Identify all elements involved in your solution

2) Identify relationships between lists and plan steps to recreate in Dataverse

3) Lookup Columns (Single Value) Migrate data as text => create a Lookup column in Dataverse linking main table with lookup table => use Power Automate flow to populate the correct value in the Dataverse lookup column

4) Lookup Column (Multi-Value) Multi-value lookup columns are used in SharePoint to workaround the unsupported Many to Many relationship between lists. Similar to Single Value Lookup, you can move over column data to Dataverse as Text (String delimited i.e. Cost center ID’s) and then use Power Automate flow with Dataverse Relate Row action Relate or unrelate rows in Dataverse – Power Automate | Microsoft Docs to create proper Many to Many relationship.

5) Consider security configurations you might require on Dataverse. The good news here is that even if you have complicated RBAC implemented in SharePoint, you can simply mimic that with easy configurations on the Dataverse side. Watch the video in the link below to learn more about Dataverse security, its one of the best resources to help you understand Dataverse security model (97) Common Data Service – Security Concepts Shown In Demos – YouTube

6) Finally consider using 3rd party solutions out there that will make the journey even more seamless for example Power Accelerate – Accelerate your Power Platform journey with Power Accelerate! This is the only one I know about which I came across by chance last month; however if you know of other similar 3rd party tools please mentioned in the comments below and I am happy to add to the article

Stay tuned for part 3 where I will discuss the changes you will need to consider in the Canvas App and the best practices to insure a frictionless experience.

Adding links to videos, discussing the topic and demostrating the steps above

Migrate SharePoint Data To Dataverse – Part 1

Migrate SharePoint Data To Dataverse – Part 2

Easily Move From SharePoint to Dataverse – Power CAT Live

For more great blogs click here.

About the Author:

Sr. Customer Success Manager / Cross-Workload Cloud Solution Architect at Microsoft

Reference:

AlSharfi, S. (2022). Transforming your important/critical PowerApps App Data-Source from SharePoint to Dataverse. Available at: https://www.linkedin.com/pulse/transforming-your-importantcritical-powerapps-app-from-alsharfi/ [Accessed: 27th July 2022]