Forum Discussion
Best Merge Queries Join Kind for Best Performance on below scenario
In Power Query Editor, you have three queries named ProductCategory, ProductSubCategory, and Product.
1) Every Product has a ProductSubCategory.
2) 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?
We obviously think
1) Inner Join Product with ProductSubCategory
2) Left Join ProductSubCategory with ProductCategory
But i saw this video explaining about best perfomant Merge Queries Join Kind for first question is Left Join
Merge Queries Scenario
which is correct answer , please help with correct option
Hi vidhyasagar
Thanks for your quick reply, as you mentioned, the left join will return the results that match the left table, it seachs the related value from the right table, you can refer to the following picture.
The left table will return all value and the right table only return the related value, so most of its time is spent querying the right table, but fot the inner join, you can refer to the following picture.
It needs to query the data that meets the conditions in both tables, so it will query both tables.
So comare them, the left join will be better than inner join, especially if you have a large amount of data, the left join performance is higher than the inner join.
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Hi vidhyasagar You want to merge these three queries into a single query while ensuring optimal performance. Here’s the recommended approach:
1. Left Join the Product table with the ProductSubCategory table using the common key. This ensures that all products are included, even if they don’t have a corresponding subcategory.
2. Left Join the resulting merged table (which includes both products and their associated subcategories) with the ProductCategory table using the common key. Again, this preserves all records, even if some subcategories don’t have a parent category.
The rationale behind using Left Joins for both steps is to avoid excluding any data. Since not every subcategory has a parent category, using Inner Joins could potentially filter out relevant records.
This ensures that you capture all relevant data while maintaining performance.
If I answered your question, please mark it as a solution!!
Thanks for your time , but in the video i provided , they are mentioning inner join looks for matching rows in both table (from left table to right table once and from right table to left table once) , while left join will look for matching rows from left table to right table once only, using query diagnostics the inner join is taking more time than left join , so best performant is left join ?
Hi,
Thanks for the solution shafiz_p provided, and i want to offer some more information for user to refer to.
hello vidhyasagar , If you want to display all the main categories, subcategories and products in the same table, although there are some subcategories that do not have a main category, then you can consider using a left join to connect the main category and subcategories, if you only need to show the relevant main categories, subcategories and products, then you can consider using inner joins, you can refer to the following link about them.
Inner join - Power Query | Microsoft Learn
Left outer join - Power Query | Microsoft Learn
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for your time , but in the video i provided , they are mentioning inner join looks for matching rows in both table (from left table to right table once and from right table to left table once) , while left join will look for matching rows from left table to right table once only, using query diagnostics the inner join is taking more time than left join , so best performant is left join ?
Hi vidhyasagar
Thanks for your quick reply, as you mentioned, the left join will return the results that match the left table, it seachs the related value from the right table, you can refer to the following picture.
The left table will return all value and the right table only return the related value, so most of its time is spent querying the right table, but fot the inner join, you can refer to the following picture.
It needs to query the data that meets the conditions in both tables, so it will query both tables.
So comare them, the left join will be better than inner join, especially if you have a large amount of data, the left join performance is higher than the inner join.
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
