Data modeling is how you structure your tables so Power BI understands how they connect. Get it right, and your reports are fast, accurate, and easy to maintain. Get it wrong, and you'll spend hours debugging.
SQL Joins in Power Query
Joins combine tables into one and happen in Power Query (Transform Data). Use them when you need a flat table.
Sample tables:
| CustomerID | CustomerName |
|---|---|
| 1 | John Smith |
| 2 | Sarah Jones |
| 3 | Mike Brown |
| 4 | Emma Wilson |
| OrderID | CustomerID | Amount |
|---|---|---|
| 101 | 1 | 100 |
| 102 | 1 | 150 |
| 103 | 2 | 200 |
| 104 | 5 | 75 |
INNER JOIN
Returns only matching rows from both tables.
Result: Customers 1 and 2 with their orders.
LEFT JOIN
Returns all rows from left table, matching from right.
Result: All customers; customers 3 and 4 show NULL for orders.
RIGHT JOIN
Returns all rows from right table, matching from left.
Result: All orders; order 104 shows NULL for customer.
FULL OUTER JOIN
Returns all rows from both tables.
Result: All customers and all orders; NULLs where no match.
LEFT ANTI JOIN
Returns rows from left with no match in right.
Result: Customers 3 and 4 (no orders).
RIGHT ANTI JOIN
Returns rows from right with no match in left.
Result: Order 104 (orphaned order).
How to create: Merge Queries > select tables > choose join kind > expand column.
Joins vs. Relationships
| Joins | Relationships |
|---|---|
| Power Query | Model View |
| Combine into one table | Keep tables separate |
| Use for preparation | Use for analysis |
Best practice: Use relationships unless you specifically need a flat table.
Power BI Relationships
Create relationships in Model View (drag between columns) or Manage Relationships.
Relationship Types
| Type | Description |
|---|---|
| One-to-Many (1:M) | One row matches many rows. Most common. |
| Many-to-Many (M:M) | Many match many. Use bridge table if possible. |
| One-to-One (1:1) | One matches one. Rare. |
Active vs. Inactive
- Active (solid): Used automatically
-
Inactive (dotted): Use with
USERELATIONSHIPin DAX
Cardinality & Cross-Filter
- Cardinality: Many-to-One is default
- Cross-filter: Single direction is default; Both can cause issues
Fact vs. Dimension Tables
| Fact Tables | Dimension Tables |
|---|---|
| Numeric, measurable data | Descriptive attributes |
| Grows with transactions | Relatively static |
| Foreign keys | Primary keys |
| Examples: Sales, Orders | Examples: Customers, Products, Date |
Schemas
Star Schema
Fact table in center, dimensions connected directly. Optimal for Power BI.
Snowflake Schema
Dimensions normalized into sub-tables. Avoid if possible—hurts performance.
Flat Table
One table with all data. Only for simple exports; avoid for reports.
Role-Playing Dimensions
One dimension used multiple ways (e.g., Date table used for Order Date and Ship Date).
Setup: One active relationship, one inactive, use USERELATIONSHIP when needed.
Common Issues & Fixes
| Issue | Fix |
|---|---|
| Circular dependencies | Remove unnecessary relationship |
| Many-to-many | Add bridge table |
| Bi-directional filtering | Change to Single direction |
| Missing date table | Create and mark date table |
| Wrong cardinality | Clean duplicates in dimension |
Quick Steps to Build a Model
- Load data (Get Data)
- Clean in Power Query (fix types, remove duplicates)
- Create relationships (drag in Model View)
- Configure (Many-to-One, Single direction)
- Hide technical columns (Properties > Is Hidden)
- Mark date table (right-click > Mark as Date Table)
- Verify (Manage Relationships)
Summary
- Use relationships over joins
- Build star schema with fact at center
- Set one-to-many with single cross-filter
- Create a date table for time intelligence
- Clean data in Power Query, model in Model View
A solid model saves hours of troubleshooting. Get the foundation right first.
Top comments (0)