How to Join Tables in Power BI

In Power BI, tables are a basic step to create the data model. It is like a spreadsheet, which stores data in rows and columns, with each column representing a different field. Every table contains specific, structured information that keeps the data organized, clean, and efficient. Joining tables is very important when you are creating reports, as it provides a clear view of data, and it is very easy to manage. In this blog, let us explore the role of tables and the different ways to join them, along with examples.

Table of Contents:

• Understanding the Basics of Power BI Tables
• Why Join Tables in Power BI?
• Types of Joins in Power BI
• Steps to Join Tables in Power BI
  • Method 1: Using Power Query Editor
  • Method 2: Join Tables Using DAX
• Performance Comparison: Power Query Join vs DAX Join
• Performance Considerations When Joining Tables in Power BI
• Best Practices for Joining Tables in Power BI
• Troubleshooting Common Issues in Power BI
• Conclusion

Understanding the Basics of Power BI Tables

Tables are a structured way of representing data in rows and columns, and they keep your data clean and well-structured. Tables in Power BI are just like Excel spreadsheets. Tables in Power BI can be imported from various sources and are stored in Power BI.

Why Join Tables in Power BI?

Joining tables in Power BI allows you to create and manage your data from multiple sources.

• It helps in creating reports and dashboards.
• It helps in performing a detailed study of data.
• It helps in preventing data loss during analysis.
• It helps in filtering data across connected datasets.

Types of Joins in Power BI

Power BI offers many types of joins that are similar to SQL:

• Inner Join: Inner Join allows only rows that have matching values in both tables.
• Left Join: Left Join is used to return all rows from the left table and matched rows from the right table.
• Right Join: Right Join allows rows from the right table and matched rows from the left table.
• Full Outer Join: Full Outer Join allows you to return all rows when there is a match in either of the tables.

Steps to Join Tables in Power BI

Let’s join the two tables on the “CustomerID.“

• A Sales table.
• A Customer table.

1. Customer Table:

2. Sales Table:

Method 1: Using Power Query Editor

Power Query Editor is an ETL tool (Extract, Transform, and Load) that is used to extract and reshape the data. Here, we have used Power Query Editor to join tables based on a common column.

Step 1: Load the dataset into Power BI

Click on Home > Get Data > Text/CSV

1. Customer Table:

This is the Customer table that looks after loading it into the dataset

2. Sales Table

This is the Sales table that looks after loading it into the dataset

Step 2: Go to Power Query Editor

Click on “Transform Data.”

Step 3: Merge Queries

Select the Sales Table and click “Merge Queries.”

Step 4: In the Merge Window

Select Sales[CustomerID] and Select Customer[CustomerID] and choose the kind of join you want to apply (we will choose Left Outer Join because we want to keep all records from the sales table and matching customer details, which makes sure that no sales data is lost even if some customer information is missing).

Step 4: Expand the Joined Table

After combining tables, a new column will appear in the table

• Click on the expand icon
• Select the field you want to bring
  • CustomerName
  • Country

Step 5: Result

Explanation: Here, this table is formed by joining the Sales and Customer tables on CustomerID.

Method 2: Join Tables Using DAX

DAX stands for Data Analysis Expressions, which is a powerful language in Power BI used for custom calculations and aggregation of data. We can also use DAX to join tables.

Step 1: Load the dataset into Power BI

Click on Home>Get Data>Text/CSV

1. Customer Table:

2. Sales Table

Step 2: Go to the Modelling Tab and write the formula below

Sales_Customer_Join = 

SELECTCOLUMNS (

    ADDCOLUMNS (

        Sales,

        "CustomerName", RELATED(Customer[CustomerName]),

        "Country", RELATED(Customer[Country])

    ),

    "SaleID", Sales[SaleID],

    "CustomerID", Sales[CustomerID],

    "Product", Sales[Product],

    "Amount", Sales[Amount],

    "SaleDate", Sales[SaleDate],

    "CustomerName", [CustomerName],

    "Country", [Country]

)

Explanation: Here, this table is formed by joining the Sales and Customer tables on CustomerID.

Performance Comparison: Power Query Join vs DAX Join

Both Methods are correct and efficient for joining rows; however, let’s look at a comparison of their performance.

Performance Considerations When Joining Tables in Power BI

• Before joining two tables together, try to remove unwanted rows and columns, which helps to improve performance.
• Make sure the column through which you are joining tables together does not contain any blank or null values.
• Remove duplicate data before joining the table, as it can lead to wrong results.
• Using Left Join over Full Outer Join leads to performance issues.
• Only select the columns you want to join. Avoid joining unnecessary fields, and it increases memory usage.

Best Practices for Joining Tables in Power BI

• Name your table: Try to name your table when working with large and multiple tables, as this will avoid confusion.
• Check Data Types: Always double-check the data types of the table that you plan to join.
• Limit the Tables: Working on multiple tables can slow down the performance, so try to keep the number of tables fewer to increase the performance.
• Use DAX: For transforming your data, you can use a DAX formula to make your data more efficient.

Troubleshooting Common Issues in Power BI

• Different Data Types: There is no possibility of joining tables when two tables have columns with different data types. But you can fix this error by transforming the data types of columns in Power Query.

• Duplicate values: If there are duplicate values in the column you want to join, then the join operation will not give you the correct result. You can fix this problem by removing all the duplicate values in the Power Query Editor.

• Blank or Null values: If your table contains blank values or NULL values, then the joined table will also have blank values. To avoid this problem, try to remove blank values or try to fill them by applying statistical formulas like average, mean, mode, and median.

• Case Sensitive: Make sure your records are either in uppercase or in lowercase. You can resolve this by making all records in uppercase or lowercase with the help of Text.Upper() or Text.Lower().

• Incorrect Join Type: Applying the wrong join type in tables produces incorrect results, so make sure you are applying the correct type of joins and that it’s fetching you the correct result.

Conclusion

Joining tables in Power BI is one of the most effective ways to work with data from multiple sources. It allows you to combine related information and create more meaningful insights. By using the correct join type and identifying the right common columns, you can ensure data accuracy and performance. Properly joined tables make the data model cleaner, faster, and easier to manage. This not only improves report quality but also helps in making better business decisions.

To learn more about Power BI and its functions, check out this Power BI Course and also explore Power BI Interview Questions prepared by industry experts.

How to Join Tables in Power BI – FAQs