Inner or Left Outer Join in PowerQuery ?

In Power Query Editor, you have three queries named ProductCategory, ProductSubCategory, and Product.Every Product has a ProductSubCategory.Not every ProductsubCategory has a parent ProductCategory.You need to merge the three queries into a single query. The solution must ensure the best performance in Power Query.How should you merge the tables?

Solution and Explanation :

To merge the three queries in Power Query Editor, consider the relationships between the tables:

1. Product and ProductSubCategory Merge: Use an Inner Join. This ensures that only rows with matching ProductSubCategory for each Product are retained, which optimizes performance. Given that every Product has a ProductSubCategory, an inner join will retain only the relevant rows from both tables, omitting any ProductSubCategory without a corresponding Product.

2. ProductSubCategory and ProductCategory Merge: Use a Left Outer Join. Since not every ProductSubCategory has a parent ProductCategory, a left outer join ensures all ProductSubCategories are kept, while matching them with any available ProductCategories. This ensures all subcategories are retained while avoiding any unnecessary categories.

To conclude :

1. For Product and ProductSubCategory, use an Inner Join.

2. For ProductSubCategory and ProductCategory, use a Left Outer Join.

This method offers optimal performance by capturing all products without retaining redundant or unused subcategories and categories.