CODEWITHSUNDEEP
powerbipowerquery
variable
variable

Reputation: 9664

Why does adding simple SQL query as Power BI data source prevent native query folding?

I have added SQL data source. Then, instead of selecting the table I have typed the following query:

SELECT ID, NAME, AGE
FROM STUDENTS

When I right click on the step then I see that the native query option is disabled. Why does adding simple SQL query as Power BI data source prevent native query folding? Is native query only supported if I select the table via UI and then select the transformations?

enter image description here

enter image description here

Upvotes: 2

Views: 166

Answers (1)

davidebacci
davidebacci

Reputation: 30174

You can enable query folding with a native query. There is an excellent article by Chris Webb where he describes all the details.

https://blog.crossjoin.co.uk/2021/02/21/query-folding-on-sql-queries-in-power-query-using-value-nativequery-and-enablefoldingtrue/

EDIT: CODE ADDED

let
  Source = Sql.Databases("localhost"),
  AdventureWorksDW2017 = Source
    {[Name = "AdventureWorksDW2017"]}
    [Data],
  RunSQL = Value.NativeQuery(
    AdventureWorksDW2017,
    "SELECT EnglishDayNameOfWeek FROM DimDate",
    null,
    [EnableFolding = true]
  ),
  #"Filtered Rows" = Table.SelectRows(
    RunSQL,
    each (
      [EnglishDayNameOfWeek] = "Friday"
    )
  )
in
  #"Filtered Rows"

Upvotes: 3

Related Questions