Query Folding & Incremental Refresh

Ok stick with me here.

We are updating our reports to utilise PowerBI Pro now being able to utilise Incremental Refresh (Yay!)

We made all the required changes, parameter editions filtered rows entries - everything.

When we setup incremental refresh we get the warning..

Unable to confirm if the Query can be folded.

We proceed and publish to the PowerBI Service and on refresh via an On Premise Gatewya and received the following error

Something went wrong  Unable to connect to the data source undefined.

Please try again later or contact support. If you contact support, please provide these details.

Underlying error code: -2147467259

Underlying error message: Resource name and Location need to match. Resource name: Table Name.

Firstly if this error related specifically to Query Folding then......

1) Why couldnt the PowerBI Desktop prevent Incremental Refresh from being configured

2) Why wouldnt the powerbi service just report that the incremental refresh parameters could not be added to the database query

Anyway I digress. Documentation suggests that query folding is only recognsied when the "View Native Query" context menu option is available in the Query Editor for that particualr query.  None of our queries have this option available as - for good reason - our queries are in the structure...

let
    Source = Sql.Database(#"Microsoft SQL Server Name", #"Database Name", [Query="SELECT * FROM TABLE", CreateNavigationProperties=false])
in
    Source

Everything in our queries are in the "Sql.Datbase" query. Whenever you right click and try to select "View Native Query" for these queries - PowerBI does not provide you that option - and I therefore suggest it would be unable to query fold that query also.

3) When there is a [Query] element to the Sql.Database command I would have thought this would be easy - but it wouldnt appear as if that this has been taken into consideration.

So I have to look at what works for incremental refresh and look to restructure the queries so the native query can be accessed and subsequently the incremental refresh work without error.

So for View Native Query to work - it has to look more like...

let
    Source = Sql.Database(#"Microsoft SQL Server Name", #"Database Name", [CreateNavigationProperties=false]),
    dbo_TABLE = Source{[Schema="dbo",Item="TABLE"]}[Data]
in
    dbo_TABLE

This is great and all but how do we pass a "Where" Clause to the query that is executed so only the a limited number of records are read into the PBI report dataset ? As I look through examples it would appera you are meant to do this via a "FilteredRows" step. 

Kudos to https://blog.pragmaticworks.com/power-query-controlling-m-query-functions-with-user-driven-parameters

This results in the query looking more like something below (in an example the query is to only ever get records where the title is a MR)

let
    Source = Sql.Database(#"Microsoft SQL Server Name", #"Database Name", [CreateNavigationProperties=false]),
    dbo_table = Source{[Schema="dbo",Item="TABLE"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_TABLE, each [TITLE] = "MR")
in 
    #"Filtered Rows"

This still works and I can still see through the View Native query option that it appends a "WHERE" to the SQL query to be executed. So this looks fine in theory - HOWEVER when I run a trace on my SQL Server I can see that the SQL Server while it executes a query including the WHERE clause, there is a seperate execution of code that does not append the WHERE or ORDER BY clauses (if a sort order was specified also in a seperate step).

So here is my great worry.  I have a transactions table with 2billion records in it.

In the FIlteredRows step I am telling the Native Query to just get 50 rows matching a criteria.

If PBI does what I am seeing and still executes a SELECT without the WHERE clause its going to get messy very quickly.

That is even before i get to Incremental Refresh.

4. If the View Native Query includes a WHERE clause because of a Filtered Rows step - Can we guarantee that the WHERE clause would be included in every Refresh ??