Power Platform Data Flows are made to sync/import data from external data sources or other environments into your Dataverse database.
For one time imports, you can also use other open source tools like XrmToolBox. With XrmToolBox, when moving data between Power Platform environments, the GUID of a record doesn’t change which can be helpful sometimes. For example if you do filtering on Dataverse view level using a lookup column. The moment you move your solution to another environment, your Dataverse view lookup filter breaks if the GUID of that record is not the same as in the other environment.
In this blogpost we focus on moving SharePoint Online data to Dataverse. Data Flows use Microsoft Power Query for the ETL process. ETL stands for Extract, Transform and Load.
The first step to execute in your data flow is defining the extraction step. You can select a data source. After you confirm that data source, you land in the transformation part. With Power Query, you can transform your data before you import it. When you have transformed the data, you can load it into a new or existing Dataverse Table.
Text columns, number columns, Yes/No columns and other easy column types are easy to migrate. The complexity lays in the more advanced columns:
- Managed metadata columns
- Multiple line of text columns
- Lookup columns
For now we will focus on Lookup columns. In future blogposts, I will explain the other complex columns.
The secret of successfully migrating SharePoint lookup relations to Dataverse lays in defining an Alternate Key in the Table to which you want to do a lookup.
To keep it easy, we take following UML schema as an example:
In the Artwork list, we have a lookup column to an artist in the Artist list.
Before we can start to move data from SharePoint into Dataverse, we should create our data structure in Dataverse. In Dataverse, a lookup column has the same name: Lookup.
If you just create a Lookup column “Artist” in Dataverse and you do nothing else, you need to feed the GUID of the Artist record to the Artworks table. This is not easy and useful.
If we just want to feed for example the Artist “Full Name”, we need to create an alternate key on the “Full Name” column of the Artist table.
Go to your table and click on “Keys”.
Click “New key”, give your key a name, select the appropriate column and click “Save”.
Your key will appear.
From now on, when you import artworks, you can feed the “Full Name” of an Artist instead of the GUID of an Artist record within that table.
Before you import artworks, of course you need to make sure that all your Artist records exist before you try to make a relation between those two tables.