Modifying Incremental Refresh Table Schemas
- ❌
Desktop Edition - ✔ Business Edition
- ✔ Enterprise Edition
Important
Setting up Incremental Refresh with Tabular Editor 3 is limited to dataset hosted in the Power BI Datasets service. For Analysis Services custom partitioning is required.
When adding or removing columns from a table configured with Incremental Refresh, you must update the table schema. Generally, this follows the same protocol as updating table schemas for single-partition tables. Tabular Editor can detect and update the schema for you, automatically:
- Detect schema changes: Right-click the table and select 'Update table schema...'.
- Apply detected schema changes: In the 'Apply Schema Changes' dialogue, confirm the desired schema changes.
- Apply changes: Deploy the model changes.
- Apply Refresh Policy: Right-click the table and select Apply Refresh Policy.
- Refresh all partitions: Shift-click to select all partitions. Right-click and select Refresh > Full refresh (partition). You can right-click the table and select 'Preview data' to see the result.
Schema Update Considerations with Incremental Refresh
For Incremental Refresh, the main consideration is that all partitions must be refreshed.
To do this, select and right-click all partitions. Select Refresh > Full refresh (partition).A second consideration is the Source Expression and Polling Expression may need to be updated to reflect schema changes. Not updating these M Expressions may result in refresh errors. Examples:
Table.TransformColumnTypesstep refers to a column that will be removed in the updated schema.Table.SelectColumnsstep lists columns to be kept; the new column is not added to this list.
CHECK M EXPRESSIONS BEFORE UPDATING THE TABLE SCHEMA
If schema changes arise from the Data Source, you may still need to apply changes to your Power Query Source Expression or Polling Expression. It is recommended that you carefully check these expressions before using 'Update table schema...'
Removing Columns
Depending on where the column is removed, you may follow a slightly different protocol:
For columns removed in the data source (i.e. removed from the view accessed by Power BI), follow the below steps:
- Detect schema changes: Right-click the table and select 'Update table schema...'.
- Apply detected schema changes: In the 'Apply Schema Changes' dialogue, confirm the desired schema changes.
- Apply changes: Deploy the model changes.
- Apply Refresh Policy: Right-click the table and select Apply Refresh Policy.
- Refresh all partitions: Shift-click to select all partitions. Right-click and select Refresh > Full refresh (partition). You can right-click the table and select 'Preview data' to see the result.
DELETED COLUMN OBJECTS MAY STILL BE QUERIED
Deleting column objects from the model does not prevent them from being queried if they still exist in the source and are not removed in the Native Query or Source Expression. Columns queried but not used can have a negative impact on refresh time and resource usage. It is recommended that you remove columns from both metadata and either data sources (i.e. views) or in the Source Expression.
Adding Columns
Depending on where the column is added, you may follow a slightly different protocol:
For columns removed in the data source (i.e. added to the view accessed by Power BI), follow the below steps:
- Detect schema changes: Right-click the table and select 'Update table schema...'.
- Apply detected schema changes: In the 'Apply Schema Changes' dialogue, confirm the desired schema changes.
- Apply changes: Deploy the model changes.
- Apply Refresh Policy: Right-click the table and select Apply Refresh Policy.
- Refresh all partitions: Shift-click to select all partitions. Right-click and select Refresh > Full refresh (partition). You can right-click the table and select 'Preview data' to see the result.