Understanding Table Relationships vs. Merged Tables

Relationship tables in Power BI establish connections between two or more different tables within the data model. These relationships define how data in one table relates to data in another table and allow users to perform accurate analysis across the tables. and the table relationships are established based on the common fields between tables. These relationships concept is used when we want to perform analysis on multiple tables.

Open Power BI Desktop and load the data sets

Loaded datasets into power bi

Click on Modelling Tab , click on Manage relationships

In Manage relationships click on Autodetect to Generate Relationships between the tables

Creating a relationship table Manually

1. On the Modeling tab, select Manage relationships and New.
2. In the Create relationship dialog box, in the first table drop-down list, select a table. Select the column you want to use in the relationship.
3. In the second table drop-down list, select the other table you want in the relationship. Select the other column you want to use, and then click OK.

Cardinality In Relationship Tables

The Cardinality refers to the Relationship between two tables.

• One-to-One Relationship(1 : 1):-In one to one relationship both tables contains the same value.

One-to-Many Relationship(1 : *):- In one to many relationship the table one has only one instance of a value and other table has more than one.

Many-to-One Relationship(* : 1):-In many to one relationship there is more than one of a value in the connected table, and the related table has only one of the value .

Many-to-Many Relationship(* : *):-In many to many relationship both tables contains multiple of same values.

Table Relationships impact on performance in power bi

1. The Incorrectly defined or inefficient table relationships can lead to performance issues, such as slow query execution.
2. Complex relationships with large datasets may result in increased memory and impacting on overall performance.

Optimization Strategies

1. Avoid unnecessary relationships and keep the number of relationships to a minimum.
2. Bi-directional filtering can increase the complexity of relationships and may lead to performance degradation. Use it only when necessary and consider alternatives.
3. High cardinality relationships (many-to-many) can impact performance. If possible create intermediary tables to reduce cardinality.

Merged Tables In Power Bi

In power bi the merged tables merges data from two or more tables into a single table. this performed using Power Query. Based on common columns between the tables , merge operations unite various datasets or tables horizontally.

• It enables users to perform analysis, reporting, and visualization on combined datasets without the need for complex joins or relationships between tables
• For example, if you have a customer table and an orders table, you can merge them based on a common field (like Customer ID) to create a unified dataset, which can then be used to establish a relationship between customers and their orders.

Merging Tables Using Merge Queries Command

First load the data sets into power bi.

click on Transform Data.

• click on merge queries command .

First we have to select left table for merge and next right table for merge.

And Select type of join you want perform ,the join operations are used join the tables .

Types Of Joins

1. Left outer join:- Merges all the rows from the left table and matching rows from right table.
2. Right outer join:- Merges all the rows from the right table and matching rows from left table.
3. Full outer join:- Merges all the rows from the both tables.
4. Inner join:- Merges only same rows from the same tables.
5. Left anti:- Merges only rows from left table.
6. Right anti:- Merges only rows from right table.

Fuzzy Matching

Fuzzy matching is a technique used to identify and match similar strings or text values within a dataset by measuring their similarity based on various algorithms and criteria. Fuzzy matching often involves setting a matching threshold or defining parameters to control the level of similarity required for a matching

Merged Tables impact on performance in power BI

1. The tables which was created using merge queries in Power Query, can impact performance, particularly during data refresh operations.
2. The Large merged tables with numerous rows or complex transformations can increase data model size and memory usage and affecting the performance.

Optimization Strategies

1. Limit the number and complexity of transformations applied to merged tables.
2. Apply filters and aggregations to reduce the number of rows and columns in merged tables before loading them into the data model.