Data modelling is the process of defining how your tables are structured and how they relate to one another. Instead of dumping everything into one giant flat table, you organise your data into multiple purpose-built tables and link them together. Power BI then uses those links to filter, aggregate, and display data correctly across your entire report.
Joins
How Tables Connect at the Query Level
A join is how you combine rows from two tables based on a shared column. In Power BI, joins happen inside Power Query before the data even hits your model.
There are four main join types:
Inner Join
Returns only rows that have a match in both tables. If a farmer exists in your Sales table but not in your Customers table, that row is dropped.
You only use when you want complete, matched records.
Left Outer Join
Returns all rows from the left table, plus any matches from the right. Unmatched rows from the right come back as nulls.
you use it when you want to keep all records from your primary table regardless of whether a match exists.
Right Outer Join
The mirror of a left join keeps all rows from the right table and fills nulls where the left has no match.
Full Outer Join
Returns all rows from both tables. Nulls appear wherever there's no match on either side.
Use when you want a complete picture and you are willing to handle the nulls.
In Power Query, you access these through Home → Merge Queries, then pick your join type from the dropdown.
Relationships
How Tables Connect in the Model
Once your tables are loaded, relationships are how Power BI understands the links between them inside the data model. Unlike joins (which physically merge rows), relationships are virtual they let Power BI filter one table through another without duplicating data.
You define relationships in the Model view, and they work automatically whenever you use fields from multiple tables in a visual.
The Three Relationship Types
One-to-Many (1:*) —The most common type. One row in Table A matches multiple rows in Table B. Example: one County matches many farmers. One Product matches many sales transactions.
One-to-One (1:1) Each row in Table A matches exactly one row in Table B. Example: one Employee record maps to one HR Profile. Rare in practice, often a sign you could just merge the tables.
Many-to-Many (:) Multiple rows in Table A match multiple rows in Table B. Example: one Order can contain many Products, and one Product can appear in many Orders. Power BI supports this natively, but it can create ambiguous filter paths best handled with a bridge table in between.
Filter Direction
Every relationship has a filter direction it controls which way filters flow between tables.
Single direction
filters flow one way only (from the "one" side to the "many" side). This is the safe default.
Both directioners flow both ways, Powerful, but can cause unexpected results in complex models. Use sparingly.
How Joins and Relationships Are Connected
This is where it clicks: joins and relationships solve the same problem at different stages of your pipeline.
A join in Power Query physically combines two tables into one before loading. A relationship in the model keeps tables separate but links them logically. The choice between them comes down to this:
Join (Power Query) Relationship (Model)
When it runs At data refresh / load time At query / visual render time
Result One merged table Two separate linked tables
Best for Lookup columns you need in the fact table Filtering and aggregating across tables
Performance Can increase table size Keeps model lean
A common pattern: use a join to bring a single lookup column (like County Region) into your fact table, while using relationships to connect your full dimension tables (Date, Product, Customer) for filtering.
Schemas
How You Arrange Your Tables
A schema is the overall blueprint of your model how many tables you have and how they're arranged. There are two schemas you'll encounter most in Power BI.
Star Schema
The star schema has one central fact table surrounded by several dimension tables. The fact table holds your numbers (revenue, yield, quantity). The dimension tables hold your descriptive context (who, what, when, where).
dim_Date ─┐
dim_County ─┤
dim_CropType ─┼──── fact_FarmerProduction
dim_Season ─┤
dim_SoilType ─┘
Every dimension connects directly to the fact table in a one-to-many relationship. This is the recommended structure for Power BI it's simple, fast, and the DAX engine is optimised for it.
Snowflake Schema
The snowflake schema normalises the star further by splitting dimension tables into sub-tables. Instead of one flat dim_Product table, you might have dim_Product → dim_Category → dim_SubCategory.
It reduces data duplication but adds complexity. In Power BI, the extra join hops can slow down queries and make DAX harder to write. Unless you have a strong reason (very large dimension tables with many repeated values), stick with the star schema.
Flat Table
A single flat table with no relationships is fine for small, simple datasets. If you're working with under 10,000 rows and don't need to join to anything else, a flat table keeps things uncomplicated. The moment you need to combine data sources, or your dimension data is repeated hundreds of times, move to a star schema.
Putting It All Together
Load your raw tables into Power Query
Use joins to pull in any lookup values you need directly in the fact table (e.g., a region name from a county lookup)
Load separate dimension tables Date, County, Crop Type, Season — without merging them
Define relationships in Model view between your fact table and each dimension (one-to-many, single-direction filter)
Arrange your model as a star schema — fact table in the centre, dimensions around it
Concept : What it does, Where in Power BI
Inner join: Keep only matched rows Power Query → Merge
Left join: Keep all left rows + matches Power Query → Merge
One-to-many relationship: Link dimension to fact table Model view
Many-to-many relationship:Complex links, use bridge table Model view
Star schema: Fact + flat dimensions Model view layout
Snowflake schema:Fact + normalised sub-dimensions Model view layout
Conclusion
Data modelling in Power BI isn't about complexity, it's about clarity. A well-structured star schema with clean one-to-many relationships will outperform a messy flat table every time, both in query speed and in how easy your DAX becomes to write and maintain.
Start with your fact table, build your dimensions, connect them with single-direction relationships, and keep it flat.
Top comments (0)