Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
smpa01
Super User
Super User

Urgent help req - sql

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

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
1 REPLY 1
lbendlin
Super User
Super User

use stored procedures to hide the complexity from Power Query.

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors