Forum Discussion
Transformations in SQL rather than Power BI but using Direct Query
Hi everyone
Up until recently I've been pulling data from our SQL database into Power BI via Direct Query Mode and then doing all my Transformations from within Power BI.
However, I've read that it's better to let SQL do most of the work (e.g. SELECT the columns you want rather than use Power BI to remove any unwanted columns etc). So, I've written SQL statements for each table and placed them in the Source within the Applied Steps section.
This works fine until I then add a Transformation within Power BI (in this case adding a Custom Column). Power BI wants me to switch fo Import Mode which is not what I want to do (I want to stay in Direct Query Mode).
Is it asking me to do this because the SQL script within Source is not actually pulling the data into Power BI? If so, how can I get around this, if at all?
Many thanks
Jon
jonclay If you put the SELECT statement into the Source step then any transformation step after that will break query folding and you don't want to do that. What you want to do is create Views in SQL that are based on your SELECT statements and point your query to that view. This will not break query folding when you do most transformation steps.
To follow on to what Greg_Deckler posted, you could also add the custom column in the view in SQL assuming all the data you need for the custom column is available in your database.
Creating a View is definently the way to go as Greg_Deckler .
You can though make enable query folding on top of a select statement if really need with the following: https://blog.crossjoin.co.uk/2021/02/21/query-folding-on-sql-queries-in-power-query-using-value-nativequery-and-enablefoldingtrue/amp/
5 Replies
jonclay If you put the SELECT statement into the Source step then any transformation step after that will break query folding and you don't want to do that. What you want to do is create Views in SQL that are based on your SELECT statements and point your query to that view. This will not break query folding when you do most transformation steps.
To follow on to what Greg_Deckler posted, you could also add the custom column in the view in SQL assuming all the data you need for the custom column is available in your database.
Thanks so much to both of you 🙂
Creating a View is definently the way to go as Greg_Deckler .
You can though make enable query folding on top of a select statement if really need with the following: https://blog.crossjoin.co.uk/2021/02/21/query-folding-on-sql-queries-in-power-query-using-value-nativequery-and-enablefoldingtrue/amp/
Hi Greg_Deckler & jdbuchanan71 & bcdobbs
Many thanks for your help. After much time spent trying to create SQL Views (something that I'm very familiar with on different databases), I've been told by our consultants that our Dynamics is Read Only and won't let me create Views. Oh well, it was worth a try!Many thanks
Jon
