Unable to get the incremental refresh work

[VModani]

We have connected Power BI service to MySQL Maria DB using MySQL ODBC driver and Power BI Gateway. All connections are fine and full refresh is working properly.

Now, when I try to implement incremental refresh by following the steps mentioned in the documentation, it does not work. The refresh has timed out at 2 hours twice. 

• The data starts from 1 October 2021 and is still updating daily, so I set the range parameters from 1st to 2nd October
• While configuring the incremental refresh in the table settings, I set it to archive to 2 years and incremental refresh to 3 days.
• I have 4 tables in the model so I did the same for all 4.

After applying the parameter filters, the "Native Query" button under Applied Steps is grayed out, so it may be an issue with folding the queries? 

Could it be specific to MySQL ODBC? 

2 of the 4 tables add 300-500 rows each day, so while a full refresh is still viable now, in a month's time it would take a lot of time.

Thanks for any help!!

[v-xiaotang · Accepted solution]

Hi @VModani 

Have you solved this question? If you have solved the question, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.

Besides, let me add some info

(1) Sources that support folding

Most data sources that have the concept of a query language support query folding. These data sources can include relational databases, OData feeds (including SharePoint lists), Exchange, and Active Directory. However, data sources like flat files, blobs, and web typically do not.

(2) If the View Native Query option isn't enabled (greyed out)

Query folding - Power Query | Microsoft Docs

(3) If you see this warning and want to verify the necessary query folding is occurring, use the Power Query Diagnostics feature or trace queries by using a tool supported by the data source, like SQL Profiler.

Incremental refresh for datasets and real-time data in Power BI - Power BI | Microsoft Docs

(4) Also, you could check the steps to set up incremental refresh again,

Configure incremental refresh and real-time data for Power BI datasets - Power BI | Microsoft Docs

Best Regards,

Community Support Team _Tang

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

[GilbertQ]

Hi @VModani 

You have to make sure that you use the RangeStart and RangeEnd within your query

here is my blog post which shows you how to do this using a DateKey which is made up of yyyymmdd if todays date is 2022-02-18 it would look like 20220218

How to configure Incremental Refreshing in Power BI with DateKey or Date (instead of the default Dat...

[VModani]

Hi @GilbertQ thanks for the response.
My original date column is already in Date/Time so I suspect if your solution is right for me? I still tried it though. 
The custom DateKey column was created easily but when I edited the filter querey with the following code as suggested on your blog, now rows were showing up.

Table.SelectRows(#"Added Custom1", each [DateKeyColumn] >= Date.Year(RangeStart)*10000 + Date.Month(RangeStart)*100 + Date.Day(RangeStart) and [DateKeyColumn] < Date.Year(RangeEnd)*10000 + Date.Month(RangeEnd)*100 + Date.Day(RangeEnd)

I had atleast 15 rows in the range but none showed up. So I am stuck there. Also the filter step is still making the Native Querey gray out.

[GilbertQ]

Hi @VModani 

That does look like it should work and even sometimes when the Native Query is greyed out it can still fold back. I would suggest checking in your source system to see what query it is sending back to confirm that it is working as expected!

[VModani]

Okay. Cool, will look into that. Thanks!