Incremental refresh in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a data analyst at a retail company.

📋 Request: Your manager wants you to set up an efficient data refresh process for the large sales dataset that updates only new data daily instead of refreshing the entire dataset.

📊 Data: You have a sales table with columns: Date, Region, Product, SalesAmount. The data grows daily with new sales records.

🎯 Deliverable: Configure incremental refresh in Power BI for the sales data to refresh only the last 5 days of data daily.

Sample Data

Date	Region	Product	SalesAmount
2024-04-20	North	Widget A	100
2024-04-21	South	Widget B	150
2024-04-22	East	Widget A	200
2024-04-23	West	Widget C	130
2024-04-24	North	Widget B	170
2024-04-25	South	Widget A	160
2024-04-26	East	Widget C	180
2024-04-27	West	Widget B	140

1

Step 1: Open Power BI Desktop and load the sales data table.

Use Get Data > Excel/CSV/Database to import the sales data.

Expected Result

Sales data table loaded with columns Date, Region, Product, SalesAmount.

2

Step 2: Create two parameters to define the incremental refresh range: RangeStart and RangeEnd.

In Power Query Editor, create parameters RangeStart and RangeEnd of type DateTime.

Expected Result

Parameters RangeStart and RangeEnd created to filter data by date.

3

Step 3: Filter the sales data table by Date column using RangeStart and RangeEnd parameters.

Apply filter: Date >= RangeStart and Date < RangeEnd in Power Query.

Expected Result

Sales data filtered to only include rows within the date range defined by parameters.

4

Step 4: Close and apply changes to load filtered data into Power BI model.

Click Close & Apply in Power Query Editor.

Expected Result

Filtered sales data loaded into Power BI model.

5

Step 5: Configure incremental refresh policy on the sales table.

In Power BI Desktop, right-click sales table > Incremental refresh > Set policy: Store data for 5 years, refresh data for last 5 days.

Expected Result

Incremental refresh policy set to refresh only last 5 days of data daily.

6

Step 6: Publish the Power BI report to Power BI Service.

Use Publish button and select workspace.

Expected Result

Report published to Power BI Service with incremental refresh enabled.

7

Step 7: Schedule daily data refresh in Power BI Service.

In Power BI Service, set up scheduled refresh to run daily.

Expected Result

Data refresh runs daily, updating only the last 5 days of sales data.

Final Result

-----------------------------------------
| Sales Dashboard                       |
|---------------------------------------|
| Date Range: Last 5 days               |
| Total Sales: $780                    |
|---------------------------------------|
| Region | Product | SalesAmount       |
|---------------------------------------|
| North  | Widget B | 170              |
| South  | Widget A | 160              |
| East   | Widget C | 180              |
| West   | Widget B | 140              |
|---------------------------------------|
| Data refresh updates only recent data|
-----------------------------------------

✓Incremental refresh reduces data load time by updating only recent data.

✓Daily refresh keeps sales data up to date without full dataset reload.

✓Sales in last 5 days total $780 across regions and products.

Bonus Challenge

Extend the incremental refresh to partition data by Region to optimize refresh further.

Show Hint

Use Power BI's partitioning feature with incremental refresh and add Region as a filter parameter.