Populating Lookup Columns in Dataverse Dataflows

Introduction:

Welcome back to another insightful tutorial on Pragmatic Works' YouTube channel! In today's video, Nate Halliwell dives into the world of Dataverse data flows, specifically focusing on effortlessly populating lookup columns. Let's jump right into the details!

Setting up the Demo:

Nate introduces a simple demo scenario featuring two tables: one for schools and another for teachers. The key element is the lookup column in the teachers' table, referencing the schools. The crucial fields are email, name, and the lookup column for schools.

Tables Overview:

• Teachers Table Fields: Email, Name, School (lookup column)
• Schools Table Fields: School Name, SQL ID.

Preparing Dataverse for Data Flow:

1. Creating an Alternate Key:

• Nate emphasizes the necessity of having an alternate key on any table looked up to in a data flow.
• Demonstrates creating an alternate key named "School ID Key" using the "School SQL ID" field.

2. Verifying Key Status:

• Shows how to check the status of the key creation job in the "Keys" section.
• Awaiting the completion of the asynchronous process indicated by the "Pending" status.

Note: Ensure selected key columns have unique values to avoid errors. Once active, the key status changes to "Active."

Building the Data Flow:

1. Initiating a New Data Flow:

• Navigates to "Data Flows" and starts a new data flow, naming it "Data Flow Demo."

2. Selecting Source Data:

• Chooses an Excel worksheet from OneDrive containing two tables: schools and teachers.

3. Power Query Editor Adjustments:

• Stresses the importance of the order of table population in Power Query Editor.
• Reorders queries to ensure lookup tables populate first.

4. Mapping Fields:

• Maps fields from Excel to Dataverse tables (schools and teachers).
• Highlights the significance of loading data into lookup tables before the primary table.

For School Table:

• • Maps School Name and School ID fields.

For Teacher Table:

• • Maps Email, Name, and School (lookup column mapped to School SQL ID).

5. Publishing the Data Flow:

• Completes the process by publishing the data flow.

Verification and Results:

1. Successful Data Refresh:

• Confirms successful data refresh after publishing the data flow.

2. Checking Dataverse Tables:

• Searches for and reviews the schools table, finding entries for Azure Academy, College of Power BI, and Power Platform University.

The Moment of Truth:

• Searches for the Teacher table and rejoices in the successful population of teachers and associated schools.

Key Takeaways:

Nate wraps up the tutorial with crucial takeaways for seamless Dataverse data flows.

1. Order Matters:

• Always prioritize lookup tables in your queries to ensure they are populated first.

2. Alternate Keys are Essential:

• Every lookup table must have an active alternate key for successful mapping.

Conclusion:

In this straightforward tutorial, Nate Halliwell guides viewers through the process of effortlessly populating lookup columns in Dataverse data flows. By following these steps, users can ensure a smooth data flow experience without hiccups. 

For more courses just like this one, sign up for the Pragmatic Works' on-demand learning platform. Here we offer a wide range of courses, including those related to Microsoft products like Power BI, Power Automate, Azure, etc.