Forum Discussion
trying incremental refresh, but keep failed to load data.
Hi Felisia ,
Since View Native Query is greyed out from the source, the OData feed and Business Central connector aren’t supporting query folding, which forces Power BI to pull the entire 23 GB table and breaks incremental refresh. This is a common limitation with on-prem OData setups where authentication or transformations disrupt folding early. Using an Azure Virtual Machine (VM) is still a good interim solution for stability and performance while you wait for Fabric access. The VM can host the On-premises Data Gateway, providing a secure bridge for Power BI Service to connect directly to Business Central. Although the VM itself won’t restore query folding, that depends on the connector, it will help improve refresh reliability and allow proper incremental refresh once folding is supported. Configure the gateway on the VM, reconnect through it in Power BI Desktop, and apply your date filter early in Power Query to minimize load. If needed, check gateway logs or firewall settings for connectivity issues.
Hi Felisia ,
There is no need to manually merge multiple 6-month datasets in Power Query. Instead, maintain a single main query and apply filters using the RangeStart and RangeEnd parameters. During incremental refresh, Power BI will automatically partition and consolidate the data, resulting in the same final dataset with improved refresh performance and reduced data load.
Hi, so the problem is not yet solved.
First time i load the data from get Data using Business Central (table GLE), but, it keep failed to load, then i change it to Odata, it succeed to load, but i can't refresh it in Power BI service (manual first time). I want to use incremental refresh, but the query folding didn't work.
i already prepare the range parameter, only load 1 month data, but the data keep load whole, so it took so much time, cause the table have more than 23Gb data for the whole table. Since the query folding break, it keep load the whole table data. The other way that i havent tried is using dataLake. any other suggestion? i trying the trial Fabric with premium capacity now.
Hi Felisia ,
The main issue seems to be that query folding is disrupted when connecting via OData from Business Central. Without folding, Power BI cannot push filters such as date parameters for incremental refresh to the source, resulting in the full 23 GB dataset being loaded. To address this, please check each step in Power Query to confirm folding is active right click the last step and select View Native Query; if it’s greyed out, folding is broken. Move your date filter step (using RangeStart and RangeEnd) as early as possible in the query and avoid transformations like custom columns, merges, or text manipulation before applying the filter, as these can break folding. Consider using the Business Central v2.0 API endpoints instead of the default OData feed, as they support folding more effectively and are optimized for incremental refresh. Ensure your filter is applied directly to the date column, for example:
Table.SelectRows(Source, each [PostingDate] >= RangeStart and [PostingDate] < RangeEnd)
This approach enables Power BI to correctly apply incremental refresh logic. Test locally in Power BI Desktop with a small date range and confirm that View Native Query reflects your filters. Once confirmed, publish to a Premium or Fabric capacity workspace and perform a full refresh. If folding cannot be maintained, using a Dataflow or Data Lake as an intermediate layer for staging filtered data is a viable alternative for improved refresh efficiency. In summary, preserving folding, filtering early, using Business Central API v2.0, and testing before publishing should help resolve the incremental refresh issue and avoid full dataset reloads.
Hi, i've tried all the possible way you given, but the View Native Query indeed, greyed out from the source (applied step). so it is break from the very beginning. I've tried the Bc conncetion too, but the result is same. Fabric is still in negotiation with my manager. For now, we try to use Azure Virtual Machine. Do you think it can help? at least it support the query folding right?
Hi Felisia ,
Since View Native Query is greyed out from the source, the OData feed and Business Central connector aren’t supporting query folding, which forces Power BI to pull the entire 23 GB table and breaks incremental refresh. This is a common limitation with on-prem OData setups where authentication or transformations disrupt folding early. Using an Azure Virtual Machine (VM) is still a good interim solution for stability and performance while you wait for Fabric access. The VM can host the On-premises Data Gateway, providing a secure bridge for Power BI Service to connect directly to Business Central. Although the VM itself won’t restore query folding, that depends on the connector, it will help improve refresh reliability and allow proper incremental refresh once folding is supported. Configure the gateway on the VM, reconnect through it in Power BI Desktop, and apply your date filter early in Power Query to minimize load. If needed, check gateway logs or firewall settings for connectivity issues.