Setting up incremental refresh with a MySQL database

[subs]

I'm fetching data from a MySQL database and setting up incremental refresh. As expected, the query was not foldable and I referred to Chris Webb's article for help but it only solves half the problem.

1) I've used Value.NativeQuery and it enables the 'View Native Query' option at the Source. But if I add absolutely any step afterward, even if it's just a date filter step using the RangeStart/RangeEnd Parameters, the 'View Native Query' option is greyed out.

2) And adding the [EnableFolding = true] parameter gives me the error 'EnableFolding' isn't a valid option for this function. Valid options are: (none)

Could someone please advise on how to go about this? 

Thank you!

[Anonymous]

Hi, @subs 

Thanks for the reply from @ibarrau , please allow me to provide addition:

The "View Native Queries" option is grayed out after any step has been added and does not necessarily mean that your query is not collapsed. You can confirm if a query collapses by looking at the query accessing the MySQL database or by doing a simple experiment. Try refreshing the preview or loading a small amount of data (e.g., one day, one-dimensional values) with a narrow filter and confirm if it loads fast. You can check the following post:

Solved: Errors enabling Native Query / Query Folding for S... - Microsoft Fabric Community

Regarding the "EnableFolding" error, native query folding is enabled by default. Operations that can be collapsed will be applied on top of native queries according to normal "import" or "direct query" logic. This parameter does not apply to EnableFoldingValue.NativeQuery().Therefore, you can try removing [EnableFolding=true] from the query. You can refer to the following link:

Solved: 'EnableFolding' isn't a valid option for this func... - Microsoft Fabric Community

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[subs]

Thanks @Anonymous for the detailed response. I removed the EnableFolding=true parameter, the refresh preview was working fine (although the queries after Source do not have 'View Native Query' enabled) and went ahead with the incremental refresh but was still getting that yellow error box 'unable to confirm if M query can be folded'. 

Is this happening because of the table modifications I've done after the Source? 

Here's the code: 

let
    Source = MySQL.Database(" ", " ", [ReturnSingleDatabase=true]), 
    SQL = Value.NativeQuery(Source, "SELECT * from sales s left join rest rr on s.restid = rr.id where rr.id =124"),
    #"Split Column by Delimiter" = Table.SplitColumn(SQL, "user", Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.None, false), {"user.1", "user.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"addressText.1", type text}, {"user.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"new_id", "userId"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [time] > RangeStart and [time] <= RangeEnd)
in
    #"Filtered Rows"

[ibarrau]

Hi. As far as I read over learn in the last years, incremental refresh only works on data sources that support query folding. MySQL is not one of them. You can check it with external tools or clicking "view native query" at a step, if it doesn't show up, it can't.

The Chris Webb example is for SQL Server databases. It won't work with MySQL.

You can try getting MySQL as usual and try configuring the parameters (rangestart and rangeend) for the incremental refresh and everything. But I don't think it's going to work.

I hope that helps,

[subs]

Thanks @ibarrau . I believe query folding is not particular to any database source but is rather a feature of Power BI. 

[ibarrau · Accepted solution]

Well. I didn't deep dive, but it's not the source or the feature. It's specifically the connector or the transformations.  When the query contains transformations that can't be translated to the native query language of your data source, either because the transformations aren't supported or the connector doesn't support query folding. If the connector can't handle it's like the source can't because people won't build their own connectors for sources that already have one.

Regarding the native query, I have never seen it with mysql written like that. That's for SQL in my mind. This might be a powerplatform doc, but it's about power query: https://learn.microsoft.com/en-us/power-query/native-query-folding

It depends on the connector and mysql can't handle it.

You can write native query, but the parameter for folding can't be read by the connector. You can just do like the picture: 

I hope that make sense,