Merge Querry - efficiency

[Anonymous]

I have an SQL table that more than 400 Million rows and I only need about 200 thousand. I thought I could inner merge the querries based on project number to only pull the 200k rows. But it seems as though it is still pulling the 400M rows and then trys to merge them.

Obviously this is a wasting a huge amount of time and processing power. Is there a better way to do this?

[SteveCampbell]

Depending on your data source. Read up on query folding (https://exceleratorbi.com.au/how-query-folding-works/). You are able to push this back to the data source to let it do the work, but check your steps and data source as described in the article, here's some important points:

1. The source must be a database that can accept a folding request.  Most relational databases are included.  It can also be an OData source, SSAS and Google Analytics (thanks Miguel and Chris).
2. If you start your query with SQL code you wrote yourself, no further steps inside Power Query will be folded.
3. Once you perform a step that cannot be folded, query folding stops for the rest of the query.

To check it is still working, you can right click on the applied step and see if you are able to select "View native query".

Try to load the raw data, do the merge, then apply other steps first.

Appreciate your Kudos
Connect with me!

Stay up to date on  
Read my blogs on  

[Anonymous]

Thanks, both your answers helped me resolve my issue. This really opens my eyes to many new possibilities

[RandyPgh · Accepted solution]

I'm assuming that your SQL table means SQL Server. For this either you or your DBA should write a query with WHERE conditions to restrict the data being returned. Better yet, a stored procedure or view could be used. However you do it, you enter the query in this way.

1. Click Get Data.

2. Choose SQL Server.

3. Enter your Server and Database names.

4. Choose Import or DirectQuery.

5. Under the DirectQuery radiobutton, click the "Advanced options" item to open several additional options.

6. Enter your query or stored procedure statement in the "SQL statement" box.

If you want to go the route of a stored procedure, there are probably several articles about using one with Power BI. Here is one.