Joins, Relationships and Schemas in Power BI

Joins

Imagine you have two separate tables with a common column and you'd like to merge them into one table.
Joins help in merging such kind of tables by using the "Merge Queries" feature in Power BI.

Types of Joins

1. Inner Join

Returns only rows with matching values in both tables. If there is no match, the row is not included in the result.

2. Left Join / Left Outer Join

Returns all records from the left table and matching rows from the right table. If there is no match, the result will contain null values for columns from the rights table.

3. Right Join / Right Outer Join

Returns all records from the right table with matching values from the left table. If there is no match, the result will contain null values for columns from the left table.

4. Full Join / Full Outer Join

Returns all records from both tables, with matching values from both sides where available. If there is no match, the result will contain null values for columns from the table that lacks a match.

5. Left Antijoin

Returns records from the left table that don't have matches on the right table.

6. Right Antijoin

Returns records from the right table that don't have matches on the left table.

Relationships

Managing relationships in Power BI is crucial for connecting multiple table and enabling accurate data analysis and visualization.
Relationships define how tables interact.
A model relationship relates one column in a table to one column in a different table.

Cardinality

Each model relationship is defined by a cardinality type. It defines how rows in one table relate to rows in another table.

Types of cardinality

• One-to-many(1:*) and Many-to-one(*:1) - are essentially the same and the most common. The "one" side means the column contains unique values; the "many" side means the column can contain duplicate values.

• One-to-one(1:1) - Both columns, in the different tables, contain unique values. It isn't common, and it likely represents a suboptimal model design because of the storage of redundant data.

• Many-to-many(:) - Both columns can contain duplicate values. This cardinality type is infrequently used.

Schemas

This is how facts and dimension tables are structured after modelling.
-> Facts table - records of what happened
-> Dimension table - contains a common column with the facts table

Star schema

Has one facts table with multiple dimension tables

Snowflake schema

It's more like star schema (one facts table with multiple dimension tables), but one dimension table will not be directly related to the facts table.

Conclusion
By understanding joins, relationships and schemas in Power BI, we can create meaningful connections between datasets and generate reliable insights for better decision-making.
Mastering these fundamentals provides a strong foundation for developing powerful and interactive Power BI reports.