Power BI Data Refresh: Scheduled and Incremental

An imported Power BI report shows data as of its last refresh. Get refresh wrong and reports quietly show yesterday's, or last month's, numbers; get it right and nobody thinks about it. This guide covers scheduled refresh and its limits, what connections refresh needs, how Power BI tells you when it fails, and incremental refresh, which refreshes only recent data, with one filter mistake shown on real data.

Scheduled refresh and its limits

After publishing, set a schedule in the semantic model's settings in the Power BI service. Microsoft documents the limits (Microsoft Learn: data refresh):

On shared capacity, the eight-a-day limit also counts refreshes triggered through the REST API, but not manual Refresh now in the settings page. Schedules use the time zone chosen in the model's settings. If a model takes more than two hours to refresh on shared capacity, it may not be refreshable there at all; incremental refresh or Premium capacity are the usual answers.

Connections and gateways

Cloud sources can often be refreshed directly from the service. Sources on a private network need an on-premises data gateway, or a virtual network gateway for sources inside a virtual network. A semantic model can use only one gateway connection, and if a single query combines an on-premises source with a cloud source, the gateway is used for the cloud source too. The Power BI gateway explained covers installation and clusters, and connecting Power BI to SQL Server the most common on-premises case.

Knowing when refresh fails

By default, Power BI emails refresh failures to the semantic model's owner, and you can add other contacts. It also emails when it pauses a schedule for inactivity: after two months in which nobody has visited any report or dashboard built on the model, the schedule is paused. Microsoft's first best practice is simply to check refresh history regularly, and to make sure spam filters are not hiding the notifications.

Incremental refresh: only refresh what changed

For large fact tables, reloading every row on every refresh wastes time and capacity. Incremental refresh partitions the table by date and refreshes only the recent partitions (Microsoft Learn: incremental refresh). It is supported on Pro, Premium per user, Premium and Embedded models.

The difference on real data is large. For a UK online retailer's 541,909 transaction lines (UCI: Online Retail), a policy that refreshes the last three days touches under 2% of the rows:

import io
import urllib.request
import zipfile
import pandas as pd

url = "https://archive.ics.uci.edu/static/public/352/online+retail.zip"
with zipfile.ZipFile(io.BytesIO(urllib.request.urlopen(url).read())) as archive:
    raw = pd.read_excel(archive.open("Online Retail.xlsx"), dtype={"InvoiceNo": str, "StockCode": str})
raw["OrderDate"] = raw["InvoiceDate"].dt.normalize()          # the date column the policy filters on
refresh_day = raw["OrderDate"].max() + pd.Timedelta(days=1)
last_3_days = raw[(raw["OrderDate"] >= refresh_day - pd.Timedelta(days=3)) & (raw["OrderDate"] < refresh_day)]
print(f"full refresh: {len(raw)} rows | refresh of the last 3 days: {len(last_3_days)} rows "
      f"({len(last_3_days) / len(raw):.1%})")

full refresh: 541909 rows | refresh of the last 3 days: 9010 rows (1.7%)

Setting it up: RangeStart and RangeEnd

1. In Power Query, create two date/time parameters with the reserved, case-sensitive names RangeStart and RangeEnd, set to a short period so the desktop file stays small.
2. Filter the fact table's date column with them, in steps you write yourself:

Filtered = Table.SelectRows(Source, each [OrderDate] >= RangeStart and [OrderDate] < RangeEnd)

1. In the table's incremental refresh settings, set how much history to archive and how recent a period to refresh, for example five years archived and the last three days refreshed.
2. Publish, then run the first refresh manually; it loads all history and can take a while. Later refreshes only process the refresh period.

After publishing, the service replaces the parameter values for each partition it creates.

The boundary mistake, in numbers

The filter must include RangeStart and exclude RangeEnd: greater than or equal to the start, strictly less than the end. With <= on both sides, a row dated exactly on a boundary belongs to two partitions and is loaded twice. With a date column, every row on the first day of each month sits on a boundary:

starts = pd.date_range("2011-01-01", "2011-11-01", freq="MS")
half_open = inclusive = 0
for start in starts:
    end = start + pd.offsets.MonthBegin(1)                    # RangeStart and RangeEnd for one partition
    half_open += ((raw["OrderDate"] >= start) & (raw["OrderDate"] < end)).sum()
    inclusive += ((raw["OrderDate"] >= start) & (raw["OrderDate"] <= end)).sum()
expected = ((raw["OrderDate"] >= "2011-01-01") & (raw["OrderDate"] < "2011-12-01")).sum()
print(f"rows January to November 2011: {expected}")
print(f"loaded with >= RangeStart and < RangeEnd:  {half_open}")
print(f"loaded with >= RangeStart and <= RangeEnd: {inclusive} ({inclusive - expected} extra)")

rows January to November 2011: 473903
loaded with >= RangeStart and < RangeEnd:  473903
loaded with >= RangeStart and <= RangeEnd: 488326 (14423 extra)

Eleven monthly partitions with an inclusive end load 14,423 extra rows: every sale dated on the first of a month is picked up by two partitions, and the last partition reaches into December. Every total built on them is overstated. Nothing errors; the numbers are simply wrong.

Options and constraints worth knowing

• Query folding matters. The filter should fold into the source query so only the partition's rows are read. If it cannot fold in an import-only policy, Power Query may read the whole table and filter locally, which defeats the purpose.
• Only refresh complete days avoids loading part of today, useful when data arrives during the day.
• Detect data changes uses a last-modified column to skip periods that have not changed.
• Real-time data with DirectQuery adds a partition for data newer than the last refresh, on Premium, PPU and Embedded only.
• Time zone: the current date defaults to UTC unless a time zone is configured in the refresh settings, which affects what "the last three days" means.
• No round trip: once published with incremental refresh, the model cannot be downloaded back as a .pbix, and republishing from Desktop replaces its partitions and data.

Practices for reliable refresh

• Check refresh history after changes and at regular intervals.
• Stagger schedules across the day rather than refreshing everything at the same hour, especially on Premium capacity.
• Load only what reports use: remove unused tables and columns.
• Reapply privacy settings for data sources after publishing, since Power BI Desktop does not publish them.
• Keep gateways healthy, and consider separate gateways for import refreshes and DirectQuery traffic.

For the alternative to importing at all, see DirectQuery and composite models, and for how refresh fits into publishing and workspaces, the Power BI service guide.

Scheduled refresh and publishing to the Power BI service are part of the Power BI service and deployment module of the Power BI course.

Sources

• Data refresh in Power BI (Microsoft Learn) — Microsoft Learn
• Configure incremental refresh and real-time data (Microsoft Learn) — Microsoft Learn
• Online Retail (UCI Machine Learning Repository) — UCI Machine Learning Repository