Forum Discussion
Can I create an efficient INNER JOIN in Power Query?
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
When loading the data to Power Pivot, it does also pull in the entire customer table, besides the correctly reduced merge table. It seems that I need to disable the loading of the entire customer table, is there some kind of setting for that? Power Query is fetching the reduced merge table first, so it seems to be working directly on the DB. If I try to remove the original full table, I get an error though.
Thanks again.
Edited this response as I realized previous response made no sense...
I think you need to merge your Sales table to your Customers table. This way the native query will act on the Customers table and will not require bringing in the whole Customer table. Also, you can right click the Sales table and unselect "Enable Load" if you don't want the whole Sales table being loaded into the data model.
-Parker
Thanks for all your help, I was just a little unsure whether the JOIN would pull Customer data directly from the DB, or work with the locally cached version it pulled in earlier. It seems to work perfectly with the approach you suggested. I'll mark your answer as the solution.