Forum Discussion
Can I create an efficient INNER JOIN in Power Query?
I've got two tables in my model. Sales, consisting of 1.000 sales records. Customers, consisting of 150.000 customers. As you can imagine, there are a lot of Customers without any Sales. I'd ideally only pull those customer records, that actually have sales. Is there an efficient way to do this in Power Query, without the necessity to go back to my DBA?
Thanks
Hey Anonymous
I can't really speak to the efficiency of the INNER JOIN but if you merge the two queries using an INNER JOIN in the Query Editor and then filter out the customers with no sales, you will then load only the data for customers with sales into your data model. This will all be accomplished during report refresh so there won't be any performance or efficiency issues to worry about during runtime.
Hope this helps,
Parker
6 Replies
Hey Anonymous
I can't really speak to the efficiency of the INNER JOIN but if you merge the two queries using an INNER JOIN in the Query Editor and then filter out the customers with no sales, you will then load only the data for customers with sales into your data model. This will all be accomplished during report refresh so there won't be any performance or efficiency issues to worry about during runtime.
Hope this helps,
Parker
Anonymous Just ran a quick test and can confirm that after performing the Merge (Inner Join), Power Query wrote the Native Query to perform the INNER JOIN inside of the SELECT statement. This means that proper query folding took place and Power BI will not have to bring in any of the unecessary customers from your data.
I would post a picture of the native query but probably shouldn't for confidentiality reasons, so you'll just have to take my word for it :smileyvery-happy:
-Parker