Incremental Refresh with SIP table that only returns last 7 days

[unknown917]

If my data source only returns the last 7 days, can I create an incremental refresh in PBI that will store and archive data for as long as I need to?  

[FarhanJeelani · Accepted solution]

Hi @unknown917 ,

Yes, you can set up incremental refresh in Power BI to store historical data even if your data source only provides the last 7 days of records. Incremental refresh allows Power BI to archive and maintain historical data while only refreshing recent data (e.g., the last 7 days) each time.

Here's a general approach to set this up:

1. Define Date Range Parameters: Create two parameters (`RangeStart` and `RangeEnd`) for incremental refresh in Power Query.

2. Filter Data: Apply a filter on the date column in Power Query to only load data within the `RangeStart` and `RangeEnd` range.

3. Configure Incremental Refresh: In Power BI, go to Modeling > Incremental Refresh for the table. Configure it to archive data beyond the last 7 days and only refresh the recent period (e.g., last 7 days).

This setup allows Power BI to retain data beyond what your data source provides, preserving historical records while minimizing the refresh load. Note that this feature requires Power BI Pro or Premium.

Please mark this as solution, if its really help you. Appreciate Kudos

[Poojara_D12 · Accepted solution]

Hi @unknown917 ,

Yes, you can set up incremental refresh in Power BI to store and archive data beyond the last 7 days, even if your data source only provides data for the most recent week. The incremental refresh feature in Power BI allows you to store historical data in your Power BI dataset and only refresh recent data, reducing load times and optimizing performance.

What you can do is:

1. Prepare Your Data Source for Incremental Refresh

1. Ensure a Date Field: First, ensure that your data source has a Date or DateTime field, which Power BI will use to filter and refresh the data incrementally.

2. Add Date Filtering Logic: Since your data source only returns the last 7 days, ensure that your query or logic in Power Query loads and transforms this last 7 days’ worth of data.

2. Configure Incremental Refresh in Power BI

1. Enable Incremental Refresh: In Power BI Desktop, open the Power Query editor, and go to the table you want to configure for incremental refresh.

2. Define Parameters for Date Filtering:

   • Create two parameters in Power Query:
     • RangeStart: A date parameter that represents the start date for incremental refresh.
     • RangeEnd: A date parameter that represents the end date for incremental refresh.
   • Set the parameters to a range that covers the historical period you want to maintain (e.g., RangeStart could be the date of the oldest record you want, and RangeEnd could be today’s date or a week after RangeStart).

3. Filter the Table Using RangeStart and RangeEnd:

   • Apply a date filter on the Date or DateTime column to only load data where the date is between RangeStart and RangeEnd.
   • This filter is essential for incremental refresh to work correctly.

4. Set Up the Incremental Refresh Policy:

   • Right-click on the table in Power BI Desktop and select Incremental Refresh.
   • Configure the refresh policy to specify how much historical data you want to keep and how frequently to refresh recent data.
     • Store data for X months or years: Define the time period you want Power BI to retain.
     • Refresh data for the last N days or weeks: Set this to 7 days to match the data your source provides.

5. Publish the Dataset: After setting up incremental refresh, publish your dataset to the Power BI Service. The incremental refresh will only take effect in the Power BI Service, not in Power BI Desktop.

3. Power BI Service: Manage Scheduled Refresh

1. Enable Scheduled Refresh: In the Power BI Service, set up a scheduled refresh (e.g., daily) to ensure the dataset is refreshed regularly and recent data is appended incrementally.

2. Verify Incremental Data Storage: The incremental refresh policy ensures that data older than the last 7 days remains stored in the Power BI dataset, even though the data source only provides data for the latest 7 days.

I hope you find this helpful. Happy Learning! 🙂

[v-tangjie-msft]

Hi @unknown917 ,

Whether the advice given by FarhanJeelani  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly.

Best Regards,

Neeko Tang

[FarhanJeelani · Accepted solution]

Hi @unknown917 ,

Yes, you can set up incremental refresh in Power BI to store historical data even if your data source only provides the last 7 days of records. Incremental refresh allows Power BI to archive and maintain historical data while only refreshing recent data (e.g., the last 7 days) each time.

Here's a general approach to set this up:

1. Define Date Range Parameters: Create two parameters (`RangeStart` and `RangeEnd`) for incremental refresh in Power Query.

2. Filter Data: Apply a filter on the date column in Power Query to only load data within the `RangeStart` and `RangeEnd` range.

3. Configure Incremental Refresh: In Power BI, go to Modeling > Incremental Refresh for the table. Configure it to archive data beyond the last 7 days and only refresh the recent period (e.g., last 7 days).

This setup allows Power BI to retain data beyond what your data source provides, preserving historical records while minimizing the refresh load. Note that this feature requires Power BI Pro or Premium.

Please mark this as solution, if its really help you. Appreciate Kudos