Thomas VineThomas Vine Power Platform Developer

Mapping a lookup column with dataflows

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.


Here I have 2 custom tables, City and Customer, joined by a one-to-many relationship.

A City table is joined to a Customer table by a one-to-many relationship
Entity Relation Diagram of City – Customer

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 NameLast NameCity
ElizabethTerryNew York
Generated customer data

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).

Screenshot of dataflow mapping where the lookup mapping is not available
Dataflow with missing lookup column

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.

When adding an alternate key, you are required to provide a name and the column/s the key applies to.
Adding a new alternate key

Now if we re-attempt the dataflow, we can see the option to map against Customer[City].

At the final stage of the dataflow, the option to map a lookup column is now possible.
Dataflow with the option to map a lookup column

Leave a Reply

Your email address will not be published. Required fields are marked *

Press ESC to close