With Power Query and M code, dataflows are a powerful tool to quickly transform and import lots of data into dataverse tables. Usually, the table you are writing data to will have a relationship with another table via a lookup column.
If you’re reading this then there’s a good chance that, like me, you’ve built a dataflow and have fallen at the last hurdle: trying to map lookup column. Thankfully, there is an easy solution.
Problem
Here I have 2 custom tables, City and Customer, joined by a one-to-many relationship.
The City table already has some records added with City[Name] as the primary column of the table. There is a second column, City[Country], which defines where the city is.
Next up, I have created some records in Excel that I want to import via a dataflow into my Customer table.
| First Name | Last Name | City |
|---|---|---|
| Elizabeth | Terry | New York |
| Ella | Rees | London |
| Hannah | Rampling | London |
| Thomas | Randall | Amsterdam |
| Carl | Glover | Rome |
I’ve already built a basic dataflow to import the data from excel into the Customer table. However, when it comes to mapping, there is no option to map the records to the City table – even if the column is marked as required.
I only have the option to map the First Name, Last Name and ID (autonumber).
Adding Alternate Keys
In order to map the lookup column, we need to add an alternate key to the City table. With an alternate key, you define a column that contains unique data, which is separate from the primary key (normally the Dataverse GUID). We need this alternate key to identify exactly which row in the City table we are referring to when mapping the column in the dataflow.
In this scenario, I can add an alternate key to the City[Name] column because I know it will always have unique values. Alternatively, during the initial table configuration, you could set the primary column of the table to an autonumber (instead of default text), which will give you a unique column you can use for the alternate key.
Setting up an alternate key is really easy and can be completed via the Keys button under the schema tab, in the main table screen. You’ll need to add a new key with an appropriate name and select the column to assign the key to – here we will use the City[Name] column.
Now if we re-attempt the dataflow, we can see the option to map against Customer[City].
Leave a Reply