Forum Discussion

mmace1's avatar
mmace1
Impactful Individual
5 years ago

Dataflows - any reason not to use a custom SQL script? It's a slight workaround, and I wonder why

In PBI Desktop, when connecting to a SQL server sourcee ,you have an option to write your own query:     In Dataflows though, this option is missing      I can recreate it, by eithe...
Need Help
v-yingjl's avatar
v-yingjl
Community Support
5 years ago

Hi mmace1 ,

It it by design in dataflow beacuse write custom sql statement will run native query, dataflow marks it unsafe. Although we cannot write custom sql directly which is simliar with power bi desktop when we choose a data source, we can paste the same code which stores in advanced editor in power query editor to dataflow to create a blank query.

You will receive this warning:

The evaluation was cancelled because it contained a native database query for the data source with kind "SQL" and path "xxxxx;AdventureWorksDW2017". Native queries may be unsafe and alter the database. Click Continue to allow all native queries to run.

Dataflow will ask your allow to continue this query, if you click continue, the query using custom sql will complete in dataflow and you will get the corresponding data.

 

About Native query, you can refer this blog and viedo that could help you:

  1. power-bi-checking-query-folding-with-view-native-query 
  2. Native Query: Be careful when using in Power BI 

 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

 

mmace1's avatar
mmace1
Impactful Individual
5 years ago

v-yingjl 


So is the only reason *not* to use native queries (i.e. custom SQL), is that query folding doesn't occur if one makes additional steps in Power Query? 

To me that's a non-issue.  If you're using a custom SQL/...instead of addtional steps in Power Query - just go back to your original custom SQL & change it there. 


Are there additional reasons I'm missing? 
Is there any other reason not to use native queries? 

  • v-yingjl's avatar
    v-yingjl
    Community Support
    5 years ago

    Hi mmace1 ,

    "So is the only reason *not* to use native queries (i.e. custom SQL), is that query folding doesn't occur if one makes additional steps in Power Query? "

    No. As I mentioned before, dataflow does not prohibit native query but will pop warning that it is unsafe, and users can get data as normal as desktop as long as they click to continue. It is by design, not have any special reason.

     

     

    Best Regards,
    Yingjie Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.