Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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:
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.
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?
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.
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.