Join data sources before importing data

[Anonymous]

Hello - i have question that i think should be very easy to solve.  I need to help an end user who does not know SQL.

I have two tables: 

Table A with 20k rows with a key value

Table B with 10M rows with the same key as table A

I need to pull in all rows from Table A and matching rows (based on the key value) from Table B.  The results should be 20K rows from Table A with the selected columns from Table B.  This would be a simple inner join in SQL.

In PowerBI, I can create a connection to each table and then create a Merge Query using the key as the join.

The problem is that PowerBI attempts to load all of table A AND all of table B before it does the merge.  The result is an attempt to load 10M records from Table B.  This is will not work.

I need a solution that will evaluate the table join BEFORE the import step. 

Is this possible?

Note: Tableau has a join function between data sources that does this function exactly and I'm surprised PowerBI/ PowerQuery does not do this.  It is an important function given the amount of data we have available.

Please advise. 

[v-jingzhang · Accepted solution]

Hi @Anonymous 

Are these two tables from the same database? If they are in the same database, the "Merge Query" feature supports Query Folding, which means that this merging operation will be transformed into a native SQL query automatically and it sends the native query to the data source to make the data source execute it. In this scenario, you can right click on Table B query and uncheck the "Enable load" option. This will make Table B not be imported. 

And in Table A query, right click on the "Expanded Table B" step or "Merged Queries" step, then check whether "View Native Query" option is clickable. If it's clickable, you can click it to see the generated native query. This means it supports query folding. If it's disabled, this means it cannot be folded. Please notice that query folding is supported when you merge Table B into Table A with "Merge Queries". If you use "Merge Queries as New" to have the merged result in a third query, this doesn't support query folding. So please ensure that query folding is enabled on this step in Table A query. 

Another option is to enter the left outer join SQL statement under "Advanced option" in the database connection window directly. This can also send this query to the database to make database execute it directly.  

Import data from a database using native database query - Power Query | Microsoft Learn

For example, 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[ChielFaber]

You could use a native query in the advanced editor. You can just make the join using sql.

Take a look at

https://learn.microsoft.com/en-us/power-query/native-query-folding?source=recommendations

Your source step will become a native sql query where you can make a specific join