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 ,
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.Hi Felisia ,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you