The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
I am querying my tables from MS Sql server and my query needs to be written as below
let
Source = Sql.Database("db", "tbl"),
Q = Value.NativeQuery(Source,"select 1 as col1",null,[PreserveTypes = true, EnableFolding = true])
in
Q
I must keep `EnableFolding = true` portion in the query for incremental refresh.
However, the queries I am using are complex in nature and they are failing when I use `declare and /cte and / drop`
For example,
// the following fails
let
Source = Sql.Database("server", "db"),
Q = Value.NativeQuery(Source,"delcare @start int = 1 select 1 as col1",null,[PreserveTypes = true, EnableFolding = true])
in
Q
// error message
DataSource.Error: Microsoft SQL: Incorrect syntax near the keyword 'declare'.
Incorrect syntax near ')'.
Details:
DataSourceKind=SQL
DataSourcePath=server;db
Message=Incorrect syntax near the keyword 'declare'.
Incorrect syntax near ')'.
ErrorCode=-2146232060
Number=156
Class=15
State=1
// the following fails
let
Source = Sql.Database("server", "db"),
Q = Value.NativeQuery(Source,"; with cte as (select 1 as col1) select * from cte",null,[PreserveTypes = true, EnableFolding = true])
in
Q
DataSource.Error: Microsoft SQL: Incorrect syntax near ';'.
Incorrect syntax near ')'.
Details:
DataSourceKind=SQL
DataSourcePath=server;db
Message=Incorrect syntax near ';'.
Incorrect syntax near ')'.
ErrorCode=-2146232060
Number=102
Class=15
State=1
I need to keep on using queries that has delcare/drop/dynamic sql/temp table/table variable as well as Enable Folding=true. Is there a way to get around it
The following works but not of any use to me as Incremetal Refresh would detect it as unfoldable source.
let
Source = Sql.Database("server", "db"),
Q = Value.NativeQuery(Source,"; with cte as (select 1 as col1) select * from cte",null/*,[PreserveTypes = true, EnableFolding = true]*/)
in
Q
use stored procedures to hide the complexity from Power Query.