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

[mmace1]

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 either writing the M code by hand, or - starting the connection in Desktop, then copying the M Code generated there,  and using that code in the Dataflow. 

In doing that, in a simple example, the code would go from

--M Code that Dataflow generates

let
  Source = Sql.Database("ServerName", "DbName"),
  Navigation = Source{[Schema = "DW", Item = "TableName"]}[Data]
in
  Navigation

And instead the code would be: 

--M Code using custom SQL script, either handwritten or generated from PBI Desktop

let
    Source = Sql.Database("ServerName", "DbName", 
[Query="SELECT *#(lf)  FROM [DW].[TableName]"])
in
    Source

Is there any downside to this? 
Why isn't custom-SQL script a direct option in Dataflows, like it is in Desktop? 

[v-yingjl]

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]

@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]

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.

[Greg_Deckler]

@mmace1  Well, @edhans  is against it and has good reasons. He will likely be along at some point to explain. But it involves query folding