This post is a part of the PL-300: Microsoft Power BI Data Analyst Exam Prep Hub; and this topic falls under these sections:
Prepare the data (25–30%)
   --> Transform and load the data
      --> Identify and Create Appropriate Keys for Relationships

Note that there are 10 practice questions (with answers and explanations) for each section to help you solidify your knowledge of the material. Also, there are 2 practice tests with 60 questions each available on the hub below the exam topics section.

Establishing correct relationships is fundamental to building accurate, performant Power BI data models. At the core of every relationship are keys — columns that uniquely identify records and allow tables to relate correctly. For the PL-300: Microsoft Power BI Data Analyst exam, candidates must understand how to identify, create, and validate keys as part of this topic domain.

What Is a Key in Power BI?

A key is a column (or combination of columns) used to uniquely identify a row in a table and connect it to another table.

In Power BI models, keys are used to:

• Define relationships between tables
• Enable correct filter propagation
• Support accurate aggregations and calculations

Common Types of Keys

Primary Key

• A column that uniquely identifies each row in a table
• Must be unique and non-null
• Typically found in dimension tables

Example:
CustomerID in a Customers table

Foreign Key

• A column that references a primary key in another table
• Found in fact tables

Example:
CustomerID in a Sales table referencing Customers

Composite Key

• A key made up of multiple columns
• Used when no single column uniquely identifies a row

Example:
OrderDate + ProductID

PL-300 Tip: Power BI does not support native composite keys in relationships — you must create a combined column.

Identifying Appropriate Keys

When preparing data, always evaluate:

Uniqueness

• The key column in the one-side of a relationship must contain unique values
• Duplicate values cause many-to-many relationships

Completeness

• Keys should not contain nulls
• Nulls can break relationships and filter context

Stability

• Keys should not change frequently
• Avoid descriptive fields like names or emails as keys

Creating Keys in Power Query

Power Query is the preferred place to create or clean keys before loading data.

Common Techniques

Concatenate Columns

Used to create a composite key:

ProductID & "-" & StoreID

Remove Leading/Trailing Spaces

Prevents mismatches:

• Trim
• Clean

Change Data Types

Keys must have matching data types on both sides of a relationship.

Surrogate Keys vs Natural Keys

Natural Keys

• Already exist in source systems
• Business-meaningful (e.g., InvoiceNumber)

Surrogate Keys

• Artificial keys created for modeling
• Often integers or hashes

PL-300 Perspective:
You are more likely to consume surrogate keys than create them, but you must know why they exist and how to use them.

Keys and Star Schema Design

Power BI models should follow a star schema whenever possible:

• Fact tables contain foreign keys
• Dimension tables contain primary keys
• Relationships are one-to-many

Example

• FactSales → ProductID
• DimProduct → ProductID (unique)

Relationship Cardinality and Keys

Keys directly determine cardinality:

Exam Insight: One-to-many is preferred. Many-to-many often signals poor key design.

Impact on the Data Model

Poor key design can cause:

• Incorrect totals
• Broken slicers
• Ambiguous filter paths
• Performance degradation

Well-designed keys enable:

• Predictable filter behavior
• Accurate DAX calculations
• Simpler models

Common Mistakes (Often Tested)

❌ Using descriptive columns as keys

Names and labels are not guaranteed to be unique.

❌ Mismatched data types

Text vs numeric keys prevent relationships from working.

❌ Ignoring duplicates in dimension tables

This results in many-to-many relationships.

❌ Creating keys in DAX instead of Power Query

Keys should be created before load, not at query time.

Best Practices for PL-300 Candidates

• Ensure keys are unique and non-null
• Prefer integer or stable identifier keys
• Create composite keys in Power Query
• Validate cardinality after creating relationships
• Follow star schema design principles
• Avoid unnecessary many-to-many relationships

How This Appears on the PL-300 Exam

You may see scenario questions like:

A relationship cannot be created between two tables because duplicates exist. What should you do?

Correct reasoning:

• Identify or create a proper key
• Remove duplicates or create a dimension table
• Possibly generate a composite key

Quick Decision Guide

Final PL-300 Takeaways

• Relationships depend on clean, well-designed keys
• Keys should be prepared before loading
• One-to-many relationships are ideal
• Composite keys must be explicitly created
• Key design directly affects DAX and visuals

Practice Questions

Go to the Practice Exam Questions for this topic.