data load best practice for relational source but complex transformations

[Anonymous]

All of my data sources are SQL Server, making them a candidate for query folding.  However, my transformations are complex, which prevents query folding.  I have found that writing the transformations in SQL (as opposed to M) gives me much better load performance (as if query folding were occurring).  In this scenario (all SQL sources, but complex transformations), is it best to continue avoiding M and instead writing all transformations in SQL? 

[jdbuchanan71 · Accepted solution]

I would say yes and you also want to be building views in SQL then just pulling the view into PowerBI.  Take a look at this article on the topic.

https://www.sqlbi.com/articles/data-import-best-practices-in-power-bi/

[mhossain]

@Anonymous 

I think it is better to write all the complex transformation and transformations which are not query folded in your sql, performance will be better. However If all the steps in M query are folded then more or less it will be the same as native query.

If you are good at sql query then you will write better optimized query which gives you better performance. Hope this makes sense.