Can't change data source when using Value.NativeQuery

[RichardBroadley]

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 if you could direct me to the post?

Anyhow, I'm trying to find out why, when using Value.NativeQuery the data source details (server and database) can't be changed outside of the Advanced Editor.

With a simple query using the Sql.Database with the Query option it is possible to change the data source details. For example, with this query:

It is possible to change the source details both using the Data Source Settings dialog in the Power BI Desktop app (the Change Source button is enabled) and also through API calls when the pbix is published to a workspace.

But when the query is changed to use Value.NativeQuery so that incremental refresh can be configured the data source settings can't be changed outside the Advanced Editor. Here is the changed query details:

But with this query the Change Source button on the Data Source Settings dialog is disabled:

And, more importantly, the API calls to change the data source details fail when the pbix is published to a workspace.  We are trying to deploy a data model / pbix for a number of clients. When the data model was small the refresh wasn't too resource intensive but the model has grown both in structure and in data as clients use it more and more so we need to move to incremental refresh as we're now hitting resource limits when the bigger clients refresh their data. The above example is using one query, but the data model we've developed is using multiple queries so it's not feasible to edit all the queries for all clients whenever we make a change, so we need a way of changing the data source programatically. 

Any help would be appreciated.

[Deku · Accepted solution]

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]

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]

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