Forum Discussion
Unable to get the incremental refresh work
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!!
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.
5 Replies
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 20220218Hi @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.
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.
