How to map a Lookup Column in a Power Platform Dataflow

Power Platform dataflow provides an effortless way to unify data from disparate sources and prepare it for use inside Dataverse.

If you know what you are doing.

In the past I have battled with getting a dataflow to relate records together i.e., setting a lookup column on one record to relate it to another.

I’ve also struggled to find complete documentation on this topic, but there is one key tip that makes this all possible. It is all about alternate keys!

This article uses a common scenario to walk you through step by step, how to map a Dataverse lookup column in a Power Platform dataflow.

Read on to find out more. 👇

The Scenario

I had a requirement to load product information from an ERP system into Dataverse so that it can be used with the Dynamics 365 Sales app. To be able to create a Product record in Dynamics 365 you must relate the Product to a Unit and Unit Group.

Relationship diagram for Product, Unit and Unit Group tables in Dynamics 365

The Unit and Unit Group lookup columns on the Product table have the following definition in the Power Apps maker portal. It is a good idea to note the names of these columns for later.

Unit and Unit Group columns on the Product table in Dataverse (Dynamics 365 Sales)

For simplicity, I am going to demonstrate this scenario using Excel as the data source (as opposed to an ERP system). The sample data links the Product to the Unit and Unit Group by their respective names. However, instead of using the name column, this could be a unique id value for the Unit and Unit Group instead (recommended).

Sample product data for use with dataflow

Why is mapping a Dataverse lookup Column in dataflows difficult?

Before I give the solution, I think it is important to discuss why it can be difficult and where a lot of people get stuck.

If you have not set any alternate keys, then at the column mapping stage of creating a dataflow, you will run into issues.

1. There are no Key columns available to select – the key column is used by the dataflow to decide whether to add a new row to the table or update an existing one. If you don’t set the Key column, then you will end up with duplicate rows in Dataverse if you run the dataflow multiple times e.g., if you are configuring your dataflow to auto-refresh on a schedule.

2. The Unit and Unit Group lookup columns are not available in the mapping list.

Both of these issues are due to not having alternate keys defined on any of the tables in Dataverse.

Missing key column and lookup columns in dataflow mapping

How to map Dataverse lookup Columns in dataflows

The solution is really quite simple, once you know what to do.

Create alternate keys on the Product, Unit and Unit Group tables. For this post I am going to keep things very simple and create keys for out-of-the-box columns on these tables. You can create the alternate key for which ever column you want – as long as you know that the column value for each row is always going to be unique.

Note: I recommend not creating a alternate key on the Name column in a production setting, like I am doing for Unit and Unit Group in this example.

Configure the Product alternate key

Configure the Unit alternate key

Configure the Unit Group alternate key

Mapping the dataflow lookups

Now that the alternate keys are configured, we can have another go at the dataflow lookup mapping. On the column mapping screen of the dataflow we are now able to set the alternate key column and the Unit and Unit Group lookup columns are available to map.

Successful mapping configuration of Dataverse lookup columns in dataflow

Notice the names of the Unit and Unit Group mapping columns. They have the format of lookup column name (from Product) + “.” + the related table's key name e.g., the Unit lookup column mapping name is DefaultUoMId.Name.

Once the dataflow configuration is complete and the dataflow runs, the Product records are created in Dataverse correctly with the Unit and Unit Group lookup columns set.

Product data in Dynamics 365 (Dataverse) after dataflow run

Power Platform Dataflow to Dataverse Lookups Conclusion

For more information on dataflows and alternate keys, refer to the following Microsoft docs.

• Define alternate keys to reference rows with Microsoft Dataverse - Power Apps | Microsoft Docs

• Self-service data prep with dataflows in Power Apps - Power Apps | Microsoft Docs

• Create and use dataflows in Power Apps - Power Apps | Microsoft Docs

• Field mapping considerations for standard dataflows - Power Query | Microsoft Docs

I hope this post helps fill a gap in some of the documentation around dataflows and setting lookup columns. Remember, they key is alternate keys!

Note: this post is a re-write of the post titled How to map a Lookup field in a Power Platform Dataflow, April 2020, from my Dynamics Citizen Developer site which is no longer active. Due to multiple requests to be able to see this post I have added it to this site. Glad to see this information is still relevant and helping others!