Combining tables in Power BI is a pivotal aspect of data integration and modeling, offering a gateway to unlocking the true potential of your data. Seamlessly consolidating and analyzing data from different tables grants you the power to extract meaningful insights and facilitate informed decision-making.

This article delves into the various techniques and methods for combining tables in Power BI, catering to various data integration scenarios. Whether establishing relationships between tables, merging datasets, appending data, or performing advanced data transformations, each method plays a crucial role in shaping your data into a coherent, informative whole.

Understanding these techniques is paramount, whether dealing with diverse data sources, creating custom-calculated columns, or constructing complex data models.

Through step-by-step guidance and real-world examples, this article empowers users with the knowledge and skills needed to harness the full capabilities of Power BI in combining tables. It’s a journey that begins with raw data and ends with compelling visualizations, actionable insights, and a deeper understanding of your business or organization’s dynamics.

The Dataset

I am using the Famous AdventureWorks dataset in the SQL .bak file format for this demo. But don’t worry. I will be sharing it with you all in Excel format. I am specifically interested in people’s data from this database. I will use the tables EmailAddress, Person, PersonPhone, and PhoneNumberType.

Let’s get going! I hope you are as excited as I am!

For your reference, the sample data files are stored here.

Combine Tables using Relationships

Suppose you are a frequent Power BI user or someone willing to get your hands dirty. Knowing how to establish relationships between tables inside Power BI is essential in any given scenario. Combining data by establishing relationships between two tables in Power BI is fundamental when working with multiple data sources that share common data points.

Let’s look at a simple scenario. We have two tables with us, Person_1, representing the employee details. We also have another table named PersonPhone that contains information about employee phone numbers. We wanted to fetch the correct phone number of the right employee on the Power BI report. Let’s see how establishing a Power BI relationship helps us combine these entities from two different tables.

Click on the Get data dropdown and select the Exel workbook as the data source. Navigate to the path where your Excel file is stored. Please open it and select the Person_1 & PersonPhone tables from the sheet. Load this data to Power BI.

Now, go to the Report view and click on any visual from the Visualizations pane. Add FirstName and LastName from the Person_1 table and PhoneNumber from the PersonPhone table. You will see that the visual can’t be built with this combination, which shows an error, as shown above.

This error exists because you are selecting columns from two different tables and trying to combine them. But since there is no relationship between these two tables, Power BI doesn’t understand against the FirstName and LastName what values of the PhoneNumber to show.

Once the data is loaded into Power BI,

1. Click on Model view to see both tables you loaded recently.
2. Navigate to the Home tab.
3. Click on the Manage Relationships option placed inside the Relationships group.

Inside the Manage relationships dialogue box that opens up –

1. Click on the New button to be able to create a new relationship between the two tables loaded.
2. This will open up the Create relationship window, where you can set up the relationship based on key columns for both tables loaded.
3. Select the PersonPhone table as the first table from the dropdown.
4. Select the Person_1 table as the second table with which the relation needs to be established. Both these tables have a common column named “BusinessEntityID,” and Power BI identifies it precisely.
5. For Cardinality, select One to Many (1:*). This means for every BusinessEntityID in the PersonPhone table; there are one or more BusinessEntityID values present in the Person_1 table.
6. Set the Cross-filter direction to Single. Also, don’t forget to tick the Make this relationship active checkbox. Otherwise, the relationship will be inactive, and combining the data won’t work.
7. Click on OK.

Note: The cross-filter direction has two value options.

Single – specifies that the filter direction between two tables is one-sided or filters can only flow from one table to another.

Both – means the filter direction is applied to both tables, and filters can flow either/or both of them.

If you see the Model view now, the relationship is active between the PersonPhone and Person_1 table. You can confirm it by looking at a line that bridges two tables.

Also, if you see, there is one arrow on the relationship towards the direction of the Person_1 table. This represents the cross-filter direction that we set to Single. Looking at that arrow, you can see that the filters flow from the PersonPhone table to the Person_1 table.

Note: If you have checked both of the tables

1. Go to the Report view in Power BI again.
2. Select the Table visual from the Visualizations pane.
3. Select FirstName and LastName from the Person_1 table and PhoneNumber from the PersonPhone table, and you will see that the Table visual now correctly shows PhoneNumber for each FirstName and LastName combination.

Power BI now understands that there is this one-to-many relationship between these two tables, and values can be combined from these two tables based on it.

Therefore, this first section taught you the concept of building relationships to combine tables in Power BI.

Combine Tables using the Merge Queries

Before we dive into the practical steps, it’s crucial to understand the concept of merging queries in Power BI. Merging queries allow you to combine data from two or more tables based on a common column or key. This operation is similar to SQL joins, particularly useful when working with data from different sources or databases.

In Power BI, there are several types of merges, including inner join, left outer join, right outer join, and full outer join. These merge types determine how rows from the source tables are combined in the resulting table. Let’s take a closer look at each type:

• Left Outer Join: A left outer join includes all rows from the left (or first) table and matching rows from the right (or second) table. If no matches exist in the right table, null values are displayed.
• Right Outer Join: A right outer join includes all rows from the right table and matching rows from the left table. Similar to the left join, null values are displayed if there are no matches in the left table.
• Full Outer Join: A full outer join includes all rows from both tables. If there are no matches in one of the tables, null values are displayed.
• Inner Join: An inner join combines rows from both tables based on the common column’s values. Only the matching rows are included in the result.
• Left Anti Join: A Left Anti Join, also known as a Left Anti Semi Join, returns all the rows from the left (or first) table that do not have corresponding matches in the right (or second) table.
• Right Anti Join: A Right Anti Join, also known as a Right Anti Semi Join, is the counterpart of the Left Anti Join. It returns all the rows from the right (or second) table that do not have corresponding matches in the left (or first) table.

Before merging the queries to combine two tables, let’s first bring them into Power BI. You already have the Person_1 table in there. Load another one named EmailAddress. As the table name itself suggests, the table contains information about the email addresses of each employee.

You can combine two tables using the Merge Queries option through the Powe Query Editor. To open the same, click on Transform Data through the Home tab.

1. Go to the Home tab inside the Power Query Editor.
2. Click on the Merge Queries dropdown from the Combine section.
3. Select the Merge Queries as the New option.

This will allow you to create a new query with merged columns. Selecting the Merge Queries option will merge the two tables and replace the first table with merged results. However, you don’t want to lose the original table, and that’s why the other option suits very well to this purpose.

Inside the Merge dialogue box that opens up

1. From the first dropdown, select the Person_1 table.
2. Choose the EmailAdress table from the second dropdown.
3. The BusinessEntityID is common between these two tables, so select this column from both tables so that those two tables can be merged based on this column.
4. The Join Kind should be set to Left Outer (all from first, matching from second).
5. Click on the OK button to perform this merge operation.

Explanation

Merging two tables is similar to SQL joins, as explained earlier. You are using the Left Outer join because the Person_1 table has 9986 rows, and the EmailAddress table has 19972. If you check the Use fuzzy matching to perform the merge checkbox, the merging will happen even if there is a partial matching between BusinessEntityID. We want to have the merge based on exact values and keep this option unchecked. Based on the Let Outer join option that you selected, it shows that 9986 rows from the Person_1 table have values matching in the EmailAddress table.

You will now see that a new table query named Merge1 is created, and at the end of it, you will see the EmailAddress column, which is added as a Table. This means the merge operation that you performed is successful.

Now comes the next part! You have merged tables, but you also need to select columns out of the EmailAddress table that can be combined with the original Person_1 table.

1. Go to the EmailAdress column inside the Merge1 query and click on the Expand button at the right-hand side of the column itself.
2. Select the Expand radio button to add columns from this table.
3. By default, all columns are selected. However, you only wanted to see the EmailAddress column in this case, so uncheck all other columns from the list.
4. Uncheck the Use original column name as prefix checkbox because if it is kept checked, all the column names will be prefixed with the EmailAddress inside their name. You don’t want that.
5. Finally, click on the OK button to add the EmailAddress column to the merged table.

If you now check the Merge1 query, at the end of all previous columns, there is an extra column named EmailAddress.1, which expectedly shows the actual email IDs of each employee.

Pro Tip: Depending on your requirement, feel free to utilize the other five join types to combine data from different tables together using the Merge Queries option. The process remains the same with the only change being the way you perform join between the tables.

Note: The different join conditions inside the Merge Queries option are really helpful when you only want to limit the number of rows inside the combined table. For example, in this case, the EmailAddress table has 19,972 rows, but only 9986 Email IDs were picked inside the combined table based on the Inner Join type with the Person_1 table.

Combine Tables using the Advanced Query Editor

Similar to Merge Queries but based on the M-Code in Advanced Editor

Combine Tables using Custom M Code

Power Query is often ignored even though it is a game changer in the world of data modeling through Power BI. In this section, you will be writing a custom M-code of your own to combine two or more tables in Power BI.

For this section, you will be utilizing the AdventureWorks products data. The Excel file shared with you contains three sheets, which consist of products, product category, and product subcategory data, respectively.

To start with, load the product data into Power BI through the source Excel files. Navigate to the Home tab, select Get Data, and choose the Excel file containing this data from your source location. As shown above, you should have three tables loaded in Power BI.

The screenshot you are seeing above is from the Power Query Editor, which can be accessed through the Transform Data tab.

If you look at the three tables you have loaded, the Product table can be combined with the ProductSubcategory table with ProductSubcategoryID as a common column. You aim to pull the Name column from the ProductSubcategory table and combine it with other columns from the Product table.

To be able to write a custom M-code of your own to combine two tables, you should first create a blank query that can hold this code. Navigate towards the New Source dropdown inside the Home tab and select Blank Query out of all available options.

Open the Advanced Editor inside the Power Query Editor. It is placed under the Query section in the Home tab. Here, you can write a custom M-code of your own to combine two tables.

Inside the Advanced Editor, write the following piece of M-code that combines the two tables based on the ProductSubcategoryID. Then, pull the Name column from the second table and combine it with the first table.

let
    Source = Table.NestedJoin(Product, {"ProductSubcategoryID"}, ProductSubcategory, {"ProductSubcategoryID"}, "ProductSubcategory", JoinKind.LeftOuter),
    #"Expanded ProductSubcategory" = Table.ExpandTableColumn(Source, "ProductSubcategory", {"Name"}, {"ProductSubcategory.Name"})
in
    #"Expanded ProductSubcategory"

Click the Done button once this code is pasted inside the Advanced Editor.

Now, if you look at the newly created query, it has the product subcategory name inside it at the end and also consists of the other columns from the Product table.

Note: You can also add multiple columns from the other table at your convenience. You just need to mention all of these columns as a list inside the curly braces.

Explanation

• You first use the Table.NestedJoin method to combine the Product and ProductSubCategory tables based on the common key ProductSubcategoryID. The NestedJoin method allows the system to combine two or more tables based on a supplied column and then stores the joined result in a new column.
• The JoinKind.LeftOuter specifies that all values from the left table (Product) should be returned, and only matching values from the right table (ProductSubCategory) should be returned. This is really important because there are few products for which a subcategory is not present in the right side table. You don’t want to miss on those products ideally.
• Then, you use the Table.ExpandTableColumn method to expand the table of columns that are created in the previous step and then specify the columns that you want to select from the second table in combination with the first table. In this case, we only selected the Name column from the second table.
• Every code in Advanced Editor is encapsulated within the let and in clause. That’s the relevance of those two in this code.

Congratulations! You have successfully combined two tables by writing a custom M-code of your own. The method again provides a robust solution for combining two or more tables.

I generally don’t prefer to give challenges, but If you can extend this logic between the ProductSubCategory and ProductCategory table together, you can actually have a hierarchy inside the Product table for products, categories, and subcategories, which is a chained combination of three tables together. Let me know your results in the comments section below.

Combine Tables using the Append Queries

As you saw until now, Power BI, with its robust suite of data transformation tools, provides multiple methods for combining tables. One such straightforward approach is using the Append Queries option, allowing you to stack tables vertically or horizontally. In this step-by-step guide, You will explore how to seamlessly combine tables using the Append Queries option in Power BI.

The Append Queries option works best when you have data that has multiple rows, but the columns are the same. For example, if you have monthly sales history coming to you, but while analyzing it, you wish to combine them to have a single file containing all month’s data.

For this example, let’s assume you have two different tables consisting of the employee information (First, Middle, and Last Names) as shown in the screenshot above.

Now, you want to combine these two tables vertically in Power BI.

Let’s start by adding the data to Power BI. Since you are well versed with it by now, I will not spend much time in mentioning how to get these two tables back to Power BI. Just follow the article from the start, and you should be good on that front.

Once the data is with you in Power BI, go to the Home tab and click on Transform Data to open the Power Query Editor. This is where all magic is going to happen.

Inside the Power Query Editor, you will see the Emp1 and Emp2 tables imported inside the Queries section.

Click on the Emp1 table to select it and then go to the Home tab > click on the Append Queries dropdown > select the Append Queries as New option.

Note: The Append Queries as New option will allow you to append the data from two or more tables and then create a new query where this appended data is stored rather than altering the original table. If you don’t wish to create a new appended query, just select the first option named Append Queries, and it will combine the data from two or more tables into the first table.

1. In the Append dialogue box that opens up, click on the Two tables radio button since you have two tables to combine. If there are more than two tables, click the Three or more tables radio button.
2. Inside the First table dropdown, select the Emp1 table.
3. In the Second table dropdown, select the Emp2 table.
4. Click on the OK button to append these two tables together.

Now, if you look inside the Queries pane, a new table, Append1, is added, and if you pay close attention to the content by clicking on that query, the table now contains 30 rows, a vertical combined operation of Emp1 and Emp2 tables.

Well, just like that, you have another way to combine two or more tables in Power BI using the Append Queries option.

Combine Tables using Calculated Tables

Power BI’s Calculated Tables feature enables users to generate new tables by combining data from existing tables using Data Analysis Expressions (DAX). In this step-by-step guide, you will create a calculated table based on two different tables in Power BI.

Recall that you have been tasked to combine ProductCategory and ProductSubcategory tables in a few sections above. Let’s combine them together by creating a calculated table.

Once you have both the tables in Power BI, the first task is to define a relationship between them based on a common column. If you look at both tables, the ProductCategoryID is shared between them and can be used to establish a relationship.

1. Go to the Model view by clicking on the icon on the left side pane in Power BI.
2. Use the mouse to drag the ProductCategoryID column from the ProductCategory table and drag it over the ProductCategoryID column in the ProductSubcateogry table.

The relationship would be one-to-many from the left table to the right table, and you can identify that by seeing 1 at the ProductCategory table side and * at the ProductSubcategory table side.

To add a new calculated table, go to the Home tab and click on the New table option placed inside the Calculations group. It will open up the formula bar.

Inside the formula bar, write the following DAX formula that combines the two tables together and pick columns of your interest from them.

ProdCatSubcat = SELECTCOLUMNS(
    ProductSubcategory,
    "ProdCategoryID", ProductSubcategory[ProductCategoryID],
    "ProdCatName", RELATED(ProductCategory[Name]),
    "SubCategoryName", ProductSubcategory[Name]
)

Explanation

• The newly created table is renamed as ProdCatSubcat.
• The SELECTCOLUMN function in Power BI allows you to select multiple columns from a table and create new columns based on an expression.
• The source table is ProductSubcategory. From this table, you select the ProductCategoryID column and name it ProdCategoryID in the calculated table.
• You want the Name column from the ProductCategory table to be pulled. But for that to happen, you need to use the RELATED function to return related values from the ProductCateogry table.
• Finally, you also pull the Name column from the ProductSubcategory table to be shown in this calculated table, so add that column as well.

That’s it! You have combined ProductCategory and ProductSubcategory tables together using a calculated table functionality in Power BI. Even though there are only a few columns from both tables pulled, you can extend this logic and add more columns to the calculated table.

To view the output of this DAX, go to the Table view and select the ProdCatSubcat table to see its content. You will see that the product categories and subcategories are residing one after another in columns (maybe not in the exact same order, but don’t worry about it).

Combine Tables using different DAX Functions

In the realm of Data Analysis, the potency of Power BI not only lies in the visualization solutions that the tool provides but also in how DAX language allows you to slice and dice the data to create valuable measures.

The DAX formulas can be used to their fullest potential for combining or merging multiple tables imported into the source system. Through this section of the article, I will walk you through different DAX functions using which you can combine tables in Power BI. Following is the list of functions you will look at throughout this section.

• UNION
• INTERSECT
• EXCEPT
• SUMMARIZE
• CROSSJOIN

Combine Tables using the UNION Function

In Power BI, the UNION function is a powerful tool used to combine tables with identical column structures into a single table. This function allows users to merge data from multiple tables vertically, stacking rows on top of each other, thereby creating a unified dataset.

If you remember, this way looks similar to using the Append Queries method to combine two tables in Power BI.

You already have Emp1 and Emp2 tables inside the Power BI environment for this example.

1. You can choose to be in either of Report view, Table view, or Model view. However, I would suggest being in Table view because you can view the table from there once it is created (a totally subjective call).
2. Go to the Home tab > click on the New table option from the Calculations group.
3. Rename the table as per your convenience and write the following UNION function inside the formula bar to combine Emp1 and Emp2 tables together.

UnionTable = UNION(Emp1, Emp2)

The DAX expression above, as expected creates a new table named UnionTable with all rows from Emp1 and Emp2 tables together (You can look at the number of rows, there should be 30 inside the UnionTable).

Warning: Please make sure that the column structure of both of the tables you are trying to combine through the UNION function is the same. Otherwise, the function will not be able to combine the tables together precisely.

Combine Tables using the INTERSECT Function

One of the essential functions in DAX is INTERSECT, which allows you to combine tables based on common values. Let’s see how to use the INTERSECT function to merge tables seamlessly.

INTERSECT is a set operator that returns the common rows between two tables or table expressions. This function is particularly useful when you want to find overlapping records based on a specified column or columns.

The sales_1 table above contains four product categories and the sales quantity for each of them.

The sales_2 table is the one with the same four product categories and sales quantities as shown above.

If you observe both tables closely, the product categories Components, Clothing, and Accessories share the same values.

Now, if you do, an INTERSECT of these two tables, ideally, it should return a table with three rows for each of the above-mentioned product categories. Let’s check if that’s how the function works.

In Power BI,

1. Go to the Home tab.
2. Click on the New table option inside the Calculations group.
3. A Formula bar pops up. Add the following formula to it, and that’s all!

Intersect_Table = INTERSECT(Sales_1, Sales_2)

You will see a new table with three common rows between sales_1 and sales_2 tables.

This is how the DAX INTERSECT function can combine two tables in a way that only returns the shared/common rows between them.

Notes & Tips:

• INTERSECT is case-sensitive, so ensure that the columns used for intersection have consistent casing in both tables.
• Another thing that we should consider is that INTERSECT is not commutative. Meaning, INTERSECT(T1, T2) will give different results than INTERSECT(T2, T1). T1 and T2 are two tables, respectively.
• You can access the New table option from any of the Report, Table, or Model view from Power BI. For this demo, I accessed this functionality from the Table view.
• To avoid unexpected results when INTERSECT combines data from two tables, ensuring the common columns have the same data type is suitable.
• While the function itself is powerful, it may cause performance issues for some of the large data sets. So, checking the function’s performance before combining large datasets is always recommended.

Combine Tables using the EXCEPT Function

EXCEPT is a set operator in DAX that returns the rows from the first table that do not exist in the second table. In other words, it subtracts one table from another, giving you the unique rows from the first table.

If you think of it, it is the exact opposite of the INTERSECT DAX function that returns the common rows between two tables.

Consider the same sales_1 and sales_2 tables from the previous example.

1. Navigate to the Home tab.
2. Select the New table option from the Calculations group.
3. Add the following DAX expression in the Formula bar.

Except_Table = EXCEPT(sales_1, sales_2)

The EXCEPT function then returns a single row from the sales_1 table, which doesn’t exist in the sales_2 table. Here, you are only receiving a single row because, for Bikes, the first table does have 25 quantities, but the second one has 500 quantities.

Note:

Similar to the INTERSECT Function, the EXCEPT function is also non-commutative. and EXCEPT(T1, T2) provides different results than EXCEPT(T2, T1). The other tips are similar to the ones for the INTERSECT function.

Combine Tables using the SUMMARIZE Function

Until now, the functions you were using are all the ones allowing you to combine two tables directly in Power BI. However, you don’t always need the entire data from both tables in a combined view. You want the aggregated view from both (or more) tables.

The SUMMARIZE function solves this problem for you by aggregating the data based on groups from different tables and creating a new summary table out of it.

The function is a powerful way to reduce large tables into summarized ones and, by doing so, provide you with a way to optimize your Power BI reports.

Let’s return to our Product table and Product & Subcateogry table for this demo. You want to identify the product subcategory Name for each Product ID and List Price. Since the first two columns are from different tables and the third one is from a different table, you will use the SUMMARIZE function to group this data by ProductID and then provide you the result as expected. A summarized grouped view of both the tables.

The steps to create a new table are as shown in the previous selection, and till now, you must be well versed with those sowill not to repeat them again.

Type the following DAX expression inside the Formula bar to combine the Product, and Product & Subcategory table.

Summarize_Table = SUMMARIZE(
    'Product',
    'Product'[ProductID],
    'Product'[ListPrice],
    'Product & Subcategory'[Name]
)

The final output is shown above, which only shows 504 summarized rows grouped by the ProductID and the Listing Price for each product Subcategory.

Note: I haven’t used any aggregation function in this formula to make the table more compact. I am just reliant on the groups. But it is recommended also to use the different aggregation functions to provide a more compact summary table.

Combine Tables Using the CROSSJOIN Function

Suppose you have two tables, Products and ProductCategory as shown in the screenshot above.

Now, you want to combine these two tables together. What will you do? You will not be able to use the INTERSECTION, UNION, EXCEPT DAX Functions. You will not be able to merge or append them as well.

All these methods won’t work because there is no common column between these two tables that can act as an identifier to build a relationship or base on which the joins, merge, or append could happen. So, you can only join these tables by staking each row of the first table with all rows of the second table and vice versa.

This is where the CROSSJOIN DAX function comes in for your rescue!

The CROSSJOIN function combines two tables by creating all possible combinations of rows from each table. This function is particularly useful when you want to generate a Cartesian product of two tables, where each row from the first table is combined with every row from the second table.

Inside the Formula bar in Power BI that opens up when you click on the New table option, write down the following formula that combines Products and ProductCategory tables together as a new table named Crossjoin_Table like a Cartesian product of all from one table against each row of another.

Crossjoin_Table = CROSSJOIN(Products, ProductCategory)

Combine Tables using the Import Query Mode

As you know already, Power BI allows users to connect to various data sources. When fetching data from SQL Server Management Studio (SSMS) in Import or a DirectQuery Data Connectivity mode, it is practically possible to write an SQL script that combines different tables and loads a single source table inside Power BI.

You need an active SQL Server Management Studio (SSMS) connection and database named AdventureWorks2019 ready for this demo. If you don’t know how to install and keep SSMS running on your system, I recommend searching online. There are tons of videos explaining this to you, for this article explaining the installation process is out of the scope.

Let’s see how this works!

1. Click on the Home tab in the ribbon.
2. Click on the Get Data dropdown and select SQL Server.
3. Enter the server details inside the textbox named Server. For me, the SSMS server name is .\SQLEXPRESS.
4. Enter the database name AdventureWork2019 inside the Database (optional) textbox.
5. Click on the Import radio button from the Data Connectivity mode section.
6. Expand the Advanced options section, and inside the SQL statements (optional, requires database) textbox, write down the following query.
7. Click OK once done.

SELECT 
p.ProductID
,p.Name AS ProdName
,ps.ProductCategoryID
,pc.Name AS ProdCatName
,p.ProductSubcategoryID
,ps.Name AS SubCatName
FROM Production.Product AS p
LEFT JOIN Production.ProductSubcategory AS ps
ON p.ProductSubcategoryID = ps.ProductSubcategoryID
LEFT JOIN Production.ProductCategory AS pc
ON ps.ProductCategoryID = pc.ProductCategoryID;

Note: If you install SSMS locally, you will probably have the server name .\SQLEXPRESS, which you can use in step 3. If you have some custom server name, please use that instead of .\SQLEXPRESS.

SQL Script Explanation

For any SQL query, the paradigm starts with the FROM clause.

1. You join the Production.Product table aliased as “p” to the Production.ProductSubcategory table aliased as “ps” based on the ProductSubcategoryID. The join performed is LEFT JOIN, which means all the rows from the Product table will be preserved, and matching rows from the ProductSubcategory table will be pulled in.
2. But you also wish to have the details from another table containing the product category-related information. Therefore, you perform another LEFT JOIN between the Production.ProductSubcategory and the Production.ProductCategory table aliased as “pc” based on the ProductCategoryID column.
3. Finally, inside the SELECT clause, you select the ProductID, ProdName from the Products table, ProductCategoryID, ProdCatName from the ProductCategory table, and ProductSubcategoryID, SubCatName from the ProductSubcategory table as six columns from three of these different tables.
4. The query above fetches the required columns from three different tables based on the Primary Key and, during this process, combines these three tables at the source itself to provide you with a combined view inside Power BI.

Note: You have to combine the ProductSubcateogry table first with the Product table because there is no direct relationship between the Product and ProductCategory table. It is connected to the Product table using a transitive relationship, which transits from the ProductCategory and ProductSubcategory tables to the Product table and allows it to combine with the ProductCategory table.

If you now see the newly loaded table in Power BI, it will be a combined view of these three tables together which is renamed as Product_Combined for reference.

This table also has blank rows because not all products are categorized. Those are filtered while showing you this view in the screenshot above, and hence, if you see the first few rows as blank, it is still fine, and you are good to go!😅

Conclusion

With this, I would like to mark the end of this long but pretty important article, which explains to you around more than 10 (thirteen being precise!😉) ways to combine tables in Power BI. However, there might be some ways to get this done, which I failed to cover here. Feel free to let me know through the comments section about those cool ways, and I would be happy to add those to this article.