Forum Discussion
Incremental Refresh with reference and merged table for SharePoint-Files
Hi,
I have a question about my problem with incremental refresh in Power BI Service. I load XML files from a SharePoint folder, which I then prepare in a base table (Table A). I then have several tables (Tables B, C, and D) that refer to this base table. Table B itself has another reference (Table B.2) to filter out certain entries using a grouping.
Now I want to use incremental refresh and only import the XML files from the last x days to improve performance.
First, I tried setting my RangeStart and RangeEnd filters in the base table. However, after setting up and publishing the report in PowerBi Service, the problem is that only the data in the base table (Table A) is historized, and in my tables B, C, D, and B.2, the filter from PowerBi Desktop is applied. Other users in the forum also describe this problem.
So I tried setting my RangeStart and RangeEnd filters not only in Table A, but in all other tables as well, and setting up incremental loading there. After publishing my report, I then get an error message saying that a column dataset cannot be found. I cannot understand exactly what is causing this, as everything works fine in PowerBi Desktop. Can anyone tell me if I am doing something wrong when using incremental loading in combination with reference tables and grouped tables? Thanks for your help!
I found the problem. Due to a pivotization in my query, an error message appeared because a column name no longer matched. I changed the code so that the column names are enforced before pivoting and are statically defined if one of these values is missing in my data source and an error message is generated as a result. It is also important that RangeStart and RangeEnd are applied to the date filter in reference tables and grouped tables and that incremental updating is set up there. Setting this up for the base table alone is not enough!
7 Replies
As an addition: Only when I place incremental refresh at the end of my table B.2 does everything work properly. But then the base table A and the other reference tables B, C, and D are completely reloaded, which I want to avoid.
rohit1991Super User
Hii LaMachina1993
Incremental refresh only works on the original source table not on reference tables, merged tables, or grouped tables. When you apply the RangeStart/RangeEnd filters in the base table (Table A), only that table is historized; all referenced tables (B, C, D) still load the full dataset because Power BI breaks query folding after a reference/merge. When you add incremental filters to the child tables as well, the model fails because those tables no longer fold back to the XML source. The fix is to apply incremental refresh only on Table A, keep all other tables as references without additional filters, and ensure Table A maintains query folding. All dependent tables will then automatically benefit from the reduced data set.
Hi, if I only apply my filters to Table A, then I see in PowerBi Service that my reference tables have the filter defined in PowerBi Desktop active and do not display all data from my defined history.
- rohit1991
Hii LaMachina1993
Reference tables always inherit whatever filters exist in Desktop, because they are not part of the incremental-refresh pipeline. Only the source table (Table A) is historized; any reference/merged table will still apply the Desktop filters unless you remove them before publishing. The solution is to keep Table A as the only table with RangeStart/RangeEnd, and remove all manual filters from the reference tables so they can receive the full historized data that Power BI generates from Table A during refresh. Once those Desktop filters are removed, the Service will show all history correctly.
I found the problem. Due to a pivotization in my query, an error message appeared because a column name no longer matched. I changed the code so that the column names are enforced before pivoting and are statically defined if one of these values is missing in my data source and an error message is generated as a result. It is also important that RangeStart and RangeEnd are applied to the date filter in reference tables and grouped tables and that incremental updating is set up there. Setting this up for the base table alone is not enough!