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