Connecting Multiple Data Tables in Power BI

Connecting multiple data tables in Power BI allows you to combine related data for meaningful analysis and reporting. Relationships help Power BI understand how tables interact with each other.

• Transform the data if you have to make changes and then load it.
• Power BI creates the relationship between the tables themselves.
• To view the relationship, click on the Model view tab on the left side of the Power BI.

You can import data from sources like Excel and CSV or associate it with online services.

One can either create new table by the help of Data Analysis Expression formulas (DAX) to add the values from the loaded tables or create a relationship.

Building a Relationship by Adding CSV Data

Click on Get data tab and load the file, one can transform the data before loading and then load it and can visualize the data in the dashboard.

Now the question arises how does Power BI make a relationship between tables

Relationship between Tables

1. It has the same column names or has some unique values.
2. Let's see what relationship is there between the tables.
3. When you expand the toggle button between the two tables, it shows the relationship the two tables have in between them.

It shows the Relationship between the two table is Many to Many relationships means the record in the table can have many records in the other table or in associated table.

CustomerID and Discount_Id columns are same.

Generally, Power BI catch the relationship automatically. You can create new by clicking the Modeling tab on the upper side, then choose the Manage Relationship tab. You can edit or create new too.

Manage Relationship

The Manage Relationships window displays existing links between tables, allowing you to view, edit or create relationships based on common columns.

Cardinality

It refers to the degree of relationship.

1. One-to-One: This relationship occurs when the entity of one table is related to one entity of another table. An employee has been allotted a laptop to work within a organization. The laptop will be used by the employee only and thus an employee shared one to one relationship.
2. One to Many: This type of relation exists when one entity of one table is related to many entities in another table. In Infosys company many employees work in a particular department. The relations the department holds with the employee is one to many.
3. Many to Many: This relation exists when many entities are related to many entities of another table.

One to One Relationship

This diagram shows a one-to-one relationship where each record in one table is directly matched to a single corresponding record in another table using a common key.

Creating a new relationship

1. Open the data model.

2. Click on Modeling and Mange Relationship tab.

3. Create a new relationship with New tab.

4. Select the columns of the table and save the relationship by pressing the OK button.

Modifying the existing Relationship

1. Open the data model.
2. Click on the modeling and then Manage Relationship tab.
3. Click on the Edit button.
4. Change the Cardinality degree.
5. Press the OK button.

Optimizing the Performance

1. Limiting the number by eliminating the unnecessary tables.
2. Testing and Troubleshooting the model.
3. Understanding the role of Cardinality impact and using it wisely.

Creating a New Table

Like other Power BI Work area tables, determined tables can have associations with different tables.

Click on Modeling and go to tab New Table

Write on the upper space Table name = UNION('Table1','Table2'). It will make a new table. Makes sure the table should have same name of columns.

You can use different functions that includes:

1. NATURALINNERJOIN
2. NATURALLEFTOUTERJOIN
3. INTERSECT
4. CALENDAR
5. CALENDARAUTO
6. DISTINCT
7. VALUES
8. CROSSJOIN
9. UNION

We used the table of Student Name and Name that was already loaded and then we formed a new table with name Table.

Table = UNION ( 'Name', 'StudentName')