SQL join/Union versus Power Query - Power BI performance and Load on Database to run the View

[arunbyc]

I noted several messages suggesting SQL queries are better to use for joining large tables than using Power BI merge. 

The following thread is just one example:

https://community.fabric.microsoft.com/t5/Power-Query/Power-query-merge-vs-SQL-joins/m-p/3153283

Based on this advice, I set up a view in SQL Server. This view does two things in my case:  (a) it joins two tables, and then (2) It adds another table to the above result using union, after making some modifications so that the table structures of the appended tables are similar.  The "joined" tables have about 600,000 records each and the appended table has about 50000 records. These tables will keep growing by about 30,000/40,000 records per month, Data is added to these tables or changed daily. 

Using these joins, the SQL query performing the joins and union has to be run each time the report is run. Let us assume the report is run about 10 /15 times a day for various reasons, We connect using the Import mode.

However, I was told that if I do the transformations in Power BI (instead of using the SQL view), once the data is loaded, Power BI has to load only the changes to the original data thus reducing the time for subsequent loads and also reducing the load on the database each time the report is run. Is this correct? Given this is not a simple load from a table, but involves merge and append, which is better (SQL queries or Power Query)? Would your answer to my question be different if the tables need not be merged/appended, but simply loaded?

Thanks

AR

AR

[v-fenling-msft]

Hi, @arunbyc 

May I ask if you have gotten this issue resolved? If it is solved, please share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

If it hasn't been resolved yet, please share more details about the issue you're having and we'll do our best to help you solve the problem you're having.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[govind_021]

Hey  ,
It is correct that we should always try to push all the complex data transformations step to the source , it reduces the load to power bi to process and transform the data which is why we have option called query folding in power bi , which pushes all the data transformation to the source.
So , always try to do the transformation to the source and then bring the refined data to power bi , so that power bi have to load and catch less low no of queries everytime user use the report.

Thank You Best Regards,
Govind Sapkade
Data Analyst | Power BI Enthusiast | Microsoft PL 300 Certified Power BI Data Analyst | MS Fabric Enthusiast
🎥Subscribe to my youtube channel for hands on tutorials : YouTube Channel

📊Let’s connect on Linkdin : Linkdin Profile

[danextian]

Hi @arunbyc 

However, I was told that if I do the transformations in Power BI (instead of using the SQL view), once the data is loaded, Power BI has to load only the changes to the original data thus reducing the time for subsequent loads and also reducing the load on the database each time the report is run. Is this correct?

Power BI does have a feature called Incremental Refresh, which allows only changes (e.g., new or updated rows) to be loaded after the initial load. However, this requires explicit setup and configuration in Power BI, such as using range partitioning on a datetime column. So without setting this up, you are pulling all rows from the database with every refresh, not just the new or updated ones.