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

Question

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?

Is this one solved?

davidebacci
– davidebacci

2023-02-03 08:35:23 +00:00
Commented Feb 3, 2023 at 8:35 — davidebacci
– davidebacci, Commented Feb 3, 2023 at 8:35

davidebacci · Accepted Answer · 2023-02-03 08:48:09Z

3

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"

edited Feb 3, 2023 at 8:48

answered Feb 2, 2023 at 13:36

davidebacci

31.4k4 gold badges20 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

variable Over a year ago

Please add the relevant code from that link to the answer so that the answer is useful even if the link goes down.

variable Over a year ago

Suppose I don't use your solution, and continue to use like how I mention in the question, and then in the UI setup the incremental refresh, then is Power BI smart enough to do the query folding automatically (even though it doesn't show the native query option)?

davidebacci Over a year ago

It depends. It may use a hybrid approach. It always query folds as much as it can and then whatever it can't query fold, will be processed directly by PowerQuery. For incremental refresh, the partition refreshed is almost certainly query folded.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related