Hi Hari, is it possible t9 do incremental refresh in power bi with monthly snapshot data ? I have history table that has snapshot data of each month, form this table , I want yo make the earliest month as a base table and the remaining month to be incremental refreshed? How can I achieve this ?
top of page
bottom of page
To set up incremental refresh for monthly snapshot data:
Parameters:
In Power Query Editor, set up two date parameters: RangeStart and RangeEnd.
These parameters will be used to filter the data that gets loaded into the model.
Filtering:
Still in Power Query Editor, filter your history table using these parameters. For example, you might use a filter like: Date >= RangeStart and Date < RangeEnd.
Incremental Refresh Configuration:
Close the Power Query Editor and go to the table's properties in the Fields pane.
Configure the incremental refresh:
Set the "Store rows in the last" option to the number of months of data you want to keep in the model. For example, if you want to keep 12 months of data, set it to 12 months.
Set the "Refresh rows in the last" option to 1 month, since you're adding data monthly.
Use the RangeStart and RangeEnd parameters appropriately.
Publish to Power BI Service:
Incremental refresh is a feature of the Power BI Service, so you'll need to publish your .pbix file to the service.
Once published, configure the dataset's scheduled refresh. The Power BI Service will use the incremental refresh settings to only load the new month's data on each refresh.
Handling the Base Table:
For your requirement of making the earliest month a base table and the remaining months as incremental, you can initially set the RangeStart and RangeEnd to cover just the earliest month when you first publish the dataset. This will load only that month's data.
Subsequently, when you adjust the parameters for scheduled refresh in the Power BI Service, set them to cover all months from the earliest month to the current month. This way, the earliest month's data will remain as the base, and only new months' data will be added incrementally.