Forum Discussion

RichardBroadley's avatar
RichardBroadley
Frequent Visitor
1 year ago
Solved

Can't change data source when using Value.NativeQuery

Apologies if this is already answered in another post elsewhere. I tried searching for a relevant post but my searches didn't turn up anything useful. But if it is answered elsewhere I'd appreciate i...
Need Help
  • Deku's avatar
    Deku
    1 year ago

    You don't need to use native query for incremental refresh. Query folding means powerquery steps can be folded into a query that is sent to the data sources. 

     

    So you can just use Views that are centrally updated, and use powerquery for incremental refresh

     

    Like this

     

    let

      Source = Sql.Database("dwdev02","AdventureWorksDW2017"),

      Data = Source{[Schema="dbo",Item="FactInternetSales"]}[Data],

      #"Filtered Rows" = Table.SelectRows(Data, each [OrderDateKey] >= Int32.From(DateTime.ToText(RangeStart,[Format="yyyyMMdd"]))),

      #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [OrderDateKey] < Int32.From(DateTime.ToText(RangeEnd,[Format="yyyyMMdd"])))

    in

      #"Filtered Rows1"

RichardBroadley's avatar
RichardBroadley
Frequent Visitor
1 year ago

Thank you, Deku, for that. I have re-jigged the queries and initial testing does seem to confirm that incremental refresh is working. However, what concerns me is that for every query done like this I'm getting a warning from Power BI saying that it's not recommended to use incremental refresh:

I went the Value.NativeQuery route initially because that removed the warnings.

Deku's avatar
Deku
Super User
1 year ago

If you look at the SQL server logs or use SQL profiler you can confirm if query folding is happening. You can look at the query and see whether the date filters for incremential refresh are included in the query for example