cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PietPoelsma
Helper I
Helper I

Update Lookup Columns in Power Query

Hi,

 

In the last step of Power Query in the Dataverse Dataflows, is there a way that you can select columns in the Destination Columns part, that are part of the  Dataverse created columns, like a Lookup column?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

@PietPoelsma, I have implemented the same use case before, and I found out that the right way to fill the lookup column values is to create the Dataverse tables and relationships first then create the the Dataflows and map the columns into Existing Table instead of creating a new one. Yet my previous post steps are very important to follow, specially the Alternate Key part. 

 

 

Regards,
Ahmed
If my reply helped you, please give a 👍. And if it has solved your issue, please consider Accepting it as the Solution to help other members of the community find it more.

 

 

 

View solution in original post

4 REPLIES 4
AhmedSalih
Super User
Super User

@PietPoelsma, yes you can, first you have to make sure that the One side of the relationship has an Alternate Key which is the primary key value in the one table and it will be in Foreign Key value in your Many table.

 

Let me know if you run into issues with this.

 

 

Regards,
Ahmed
If my reply helped you, please give a 👍. And if it has solved your issue, please consider Accepting it as the Solution to help other members of the community find it more.

 

 

PietPoelsma
Helper I
Helper I

Hi @AhmedSalih 

Thanks for your quick reply. Not quite sure if this helps.

 

Let me get some more context:

I imported two tables form SQL Server into the Dataverse:

  1. UnitType (Parent Table)
  2. Item (Child Table)

In SQL Server the table Item there is a (foreign key) column that contains the primary key value of the table UnitType.

After the data import I created a 1-n relationship between UnitType and Item, which results that in the Item table a Lookup column is created, which however is not filled... And that is actually what I want to achieve!

 

In another Dataflow I would like to fill this Lookup column with Power Query. I was able to add a column to Item (Child table) that contains the values of the GUID of the table UnitType (Parent Table) in the Transform step. I used Merge Queries for that. 

 

In the step Map tables for the table Item, I do a Load to an existing table and I would like to map the column with the GUID values to the Lookup column, but that column doesn't show up as a Destination Column...

 

Kind regards,

Piet

 

(BTW I was able to fill the foreign key via editing the two tables in Excel and use VLOOKUP , but that is a lot of manual work and not easy repeatable, hence I would like to use a Dataflow)

 

 

@PietPoelsma, I have implemented the same use case before, and I found out that the right way to fill the lookup column values is to create the Dataverse tables and relationships first then create the the Dataflows and map the columns into Existing Table instead of creating a new one. Yet my previous post steps are very important to follow, specially the Alternate Key part. 

 

 

Regards,
Ahmed
If my reply helped you, please give a 👍. And if it has solved your issue, please consider Accepting it as the Solution to help other members of the community find it more.

 

 

 

PietPoelsma
Helper I
Helper I

Thx @AhmedSalih 

 

I finally made it to work!

 

Kind regards Piet

Helpful resources

Announcements
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Users online (3,360)