trying incremental refresh, but keep failed to load data.

[Felisia]

My data source comes from Business Central on premise. I use Odata to load the table i need. and for 2 largest table (GLE & GLBudget Entries) i try to use incremental refresh using parameter. i also have few table that reference to those 2 tables. Before i use incremental the report works fine, but as the data grow bigger, it start to error when i used scheduled refresh. When i search the error, it said probably cause the large dataset. the problem is i need to show data from dec 2023- now. and til now i still can't load it properly. it keep error : Exception from HRESULT: 0x80040E4E, and error : count out of range. I've tried to get the range smaller, but still showing the error for different table each time i apply & load. What should i do to solve this? should i unload few table first? table that reference?

[v-sshirivolu]

Hi @Felisia  ,

I would also take a moment to thank @rohit1991 , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

In addition to your suggestions, a few extra things can help when working with very large datasets and incremental refresh. First, try to remove any columns you don’t really need for your visuals or calculations, as this reduces memory usage and makes refresh faster. Second, for any staging or intermediate tables that are only used for transformations, you can turn off “Enable load” so they don’t add unnecessary load to the model. Third, make sure the RangeStart and RangeEnd parameters are applied directly in Power Query as date filters before any other steps, this helps Power BI push the filtering to the source efficiently. Fourth, it’s a good idea to test the incremental refresh locally with a small date range first, to make sure everything loads correctly before you publish. Fifth, if possible, use staging tables in SQL or Business Central to pre-filter or aggregate your data at the source, which keeps the dataset smaller and refresh faster. Finally, for extremely large tables like GLEntries or GLBudgetEntries, consider splitting them by month or year so Power BI only processes a manageable chunk at a time.

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions

[Felisia]

if i load in smaller dataset example per 6 months? i should combine them in power query again right? so when i load it would it be the same result? sorry, i'm a newbie.

[v-sshirivolu]

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.

[Felisia]

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. 

[v-sshirivolu]

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.

[Felisia]

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?

[v-sshirivolu · Accepted solution]

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.

[v-sshirivolu]

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

[rohit1991]

Hi @Felisia 

1. The error usually happens because the dataset is too large or the incremental refresh setup isn’t correct.
2. First, check query folding, Power BI must be able to push date filters (RangeStart/RangeEnd) to the OData source.
3. Apply date filters early in Power Query, before merging or expanding any other tables.
4. Test with a smaller date range (like one month) to make sure incremental refresh is working properly.
5. Turn off refresh for tables that are only used for lookup or references.
6. If some tables are too heavy, filter them down or split them into smaller tables.
7. For large datasets, try using a Dataflow or Fabric Lakehouse for better performance.
8. After changes, refresh manually in Desktop first, then publish and schedule it again.
9. This step-by-step approach usually fixes the “failed to load data” issue