Power BI incremental refresh

This feature lets you refresh only the most recent data, reducing refresh times and improving reliability.

About incremental refresh

Incremental refresh in Power BI divides a table into date ranges called partitions. You choose how much history to keep and how much recent data to reload on each refresh. For example, you can keep one year of data and reload only the last seven days. The first refresh loads the full year; later refreshes reload the recent days and keep the older data. This reduces download time and the risk of timeouts.

Before you start

Make sure the endpoint you want to fetch data from supports timestamp-based filtering.
Your queries must return the same column names and data types, even when a date range contains no rows.
All date ranges in an incrementally refreshed table must read from the same data source.

Set up incremental refresh

Create parameters

Create two Power Query parameters named RangeStart and RangeEnd of type datetime.

Apply parameter filters

Use these parameters to filter data. The parameters can be set to any date range.

Configure incremental refresh policy

Configure incremental refresh policy in Power BI Desktop.

Publish the model

Publish the model to Power BI Service.

Set RangeStart and RangeEnd to a small time period while developing to limit the amount of data loaded into Power BI Desktop. Once the model is published, the Power BI Service will automatically adjust these parameters to load the archive partition and the most recent data based on the incremental refresh policy you defined.

For more information about incremental refresh concepts, see Microsoft’s Incremental refresh and real-time data for semantic models.

Utility functions

Power BI supplies RangeStart and RangeEnd as plain datetime values that represent UTC. Since CDF endpoints expect timestamps in specific formats, convert them to the format each request type expects:

GetCDF/PostCDF: Normal GET/POST requests to the Cognite APIs expect timestamps in milliseconds since epoch (UTC).
GraphQL: Data modeling GraphQL requests expect timestamps in ISO 8601 string format.
ODataCDF: OData requests expect timestamps in datetimezone format for column filtering.

By default, refresh operations in the Power BI service determine the refresh window in UTC. If you configure a non-UTC time zone under Refresh, RangeStart and RangeEnd follow that time zone instead, and these functions no longer match CDF. Keep the default to avoid the mismatch. See Configure incremental refresh.

ConvertDateTimeToMs

Convert DateTime to milliseconds since epoch

(dt as nullable datetime) as nullable number =>
if dt = null then null else
let
    // Define the Unix epoch start in UTC
    UnixEpochStart = #datetime(1970, 1, 1, 0, 0, 0),
    // Calculate the duration between the input datetime and Unix epoch start
    Delta = dt - UnixEpochStart,
    // Round to millisecond precision to avoid floating-point values such as
    // 1000.999999999 losing one millisecond when rounded down.
    TotalMilliseconds = Number.Round(Duration.TotalSeconds(Delta) * 1000)
in
    TotalMilliseconds

ConvertDateTimeToIso

Convert DateTime to ISO 8601 text representation

(dt as nullable datetime) as nullable text =>
if dt = null then null else
let
    // Mark the input as UTC without shifting the clock
    UTCDateTimeZone = DateTime.AddZone(dt, 0),
    // Use DateTimeZone.ToText with ISO 8601 format
    Result = DateTimeZone.ToText(UTCDateTimeZone, [Format="yyyy-MM-ddTHH:mm:ss.fffzzz", Culture="en-US"])
in
    Result

ConvertDateTimeToDateTimeZone

Convert DateTime to DateTimeZone

(dt as nullable datetime) as nullable datetimezone =>
if dt = null then null else
let
    // Mark the input as UTC without shifting the clock
    UTCDateTimeZone = DateTime.AddZone(dt, 0)
in
    UTCDateTimeZone

EnforceSchema

The EnforceSchema function ensures that the output table has a consistent schema, even when the API response is empty. This function takes an input table and a list of expected column names, and returns a table with the specified columns. If any of the expected columns are missing in the input table, they’re added with null values.

Enforce schema for a table

(InputTable as table, Columns as list) as table =>
    Table.SelectColumns(InputTable, Columns, MissingField.UseNull)

ColumnNamesToText

The ColumnNamesToText function generates a list of column names in a format that can be used in the EnforceSchema function. You can use it once with the response from the GetCDF, PostCDF, or GraphQL functions (when the current filter returns at least one entry) to create a list of column names that are returned by the API. This list can be used in the EnforceSchema function to ensure that the schema is consistent even when no data is returned.

Convert column names to text

(InputTable as table) as text =>
let
    // Get the column names
    columnNames = Table.ColumnNames(InputTable),
    // Escape M escape sequences and quotes before wrapping each name in quotes
    quotedColumnNames = List.Transform(columnNames, each
        """" & Text.Replace(Text.Replace(_, "#", "#(#)(#)"), """", """""") & """"),
    // Combine them into a single text with comma separation
    combinedText = Text.Combine(quotedColumnNames, ", "),
    // Add curly braces around the combined text
    ColumnNamesAsText = "{ " & combinedText & " }"
in
    ColumnNamesAsText

EnforceSchema preserves existing column types and adds missing columns with null values. Apply explicit types after it, as shown below, so empty and populated partitions have the same schema.

Incremental refresh examples

Add ConvertMsToUtcDateTime for the GET and POST examples. Use an inclusive start and exclusive end (RangeStart <= timestamp < RangeEnd) so adjacent partitions neither overlap nor omit boundary records. Nested fields such as metadata and properties are returned as JSON text for a stable, loadable schema. Expand them into explicitly typed columns instead if you need to report on individual properties. These examples partition by createdTime; updates and deletions of older objects are not captured after their partitions stop refreshing.

GetCDF example

This example shows how to configure an incremental refresh when fetching CDF events using the GetCDF function with a URL parameter filter:

Incremental refresh with GetCDF

let
    // Convert parameters to milliseconds since epoch
    RangeStartMs = ConvertDateTimeToMs(RangeStart),
    RangeEndMs = ConvertDateTimeToMs(RangeEnd),
    // Events uses an inclusive maximum; subtract 1 ms for an exclusive end.
    QueryURL = Text.Format(
        "/events?includeMetadata=true&minCreatedTime=#{0}&maxCreatedTime=#{1}",
        { Number.ToText(RangeStartMs, "0", "en-US"), Number.ToText(RangeEndMs - 1, "0", "en-US") }
    ),
    // Fetch data using GetCDF
    EventsTable = GetCDF(QueryURL),
    // Define the expected schema
    Columns = {
        "externalId",
        "dataSetId",
        "startTime",
        "type",
        "subtype",
        "description",
        "metadata",
        "assetIds",
        "source",
        "id",
        "lastUpdatedTime",
        "createdTime"
    },
    // Enforce the schema to ensure a consistent table even if no data is returned
    FinalTable = EnforceSchema(EventsTable, Columns),
    WithDates = Table.TransformColumns(FinalTable, List.Transform(
        {"startTime", "createdTime", "lastUpdatedTime"},
        each {_, ConvertMsToUtcDateTime, type nullable datetime})),
    Typed = Table.TransformColumnTypes(WithDates, {
        {"id", Int64.Type}, {"dataSetId", Int64.Type}, {"externalId", type text},
        {"type", type text}, {"subtype", type text}, {"description", type text}, {"source", type text}
    }),
    WithNestedTypes = Table.TransformColumns(Typed, {
        {"metadata", each if _ = null then null else Text.FromBinary(Json.FromValue(_)), type nullable text},
        {"assetIds", each if _ = null then null else Text.FromBinary(Json.FromValue(_)), type nullable text}
    }),
    Partition = Table.SelectRows(WithNestedTypes, each [createdTime] >= RangeStart and [createdTime] < RangeEnd)
in
    Partition

PostCDF example

This example shows an incremental refresh when fetching data modeling nodes with the PostCDF function:

Incremental refresh with PostCDF

let
    // Convert parameters to milliseconds since epoch
    RangeStartMs = ConvertDateTimeToMs(RangeStart),
    RangeEndMs = ConvertDateTimeToMs(RangeEnd),
    // Create the request body with timestamp filters
    Body = [
        sources = {
            [
                source = [
                    type = "view",
                    space = "cdf_cdm",
                    externalId = "CogniteActivity",
                    version = "v1"
                ]
            ]
        },
        filter = [
            range = [
                property = { "node", "createdTime" },
                gte = RangeStartMs,
                lt = RangeEndMs
            ]
        ],
        limit = 1000
    ],
    // Convert the body to JSON text
    BodyText = Text.FromBinary(Json.FromValue(Body)),
    // Fetch data using PostCDF
    NodesTable = PostCDF("/models/instances/list", BodyText),
    // Remove null rows
    CleanedTable = Table.SelectRows(
        NodesTable,
        each List.NonNullCount(Record.ToList(_)) > 0
    ),
    // Define the expected schema
    Columns = {
        "instanceType",
        "version",
        "space",
        "externalId",
        "createdTime",
        "lastUpdatedTime",
        "properties"
    },
    // Enforce the schema to ensure a consistent table even if no data is returned
    FinalTable = EnforceSchema(CleanedTable, Columns),
    WithDates = Table.TransformColumns(FinalTable, {
        {"createdTime", ConvertMsToUtcDateTime, type nullable datetime},
        {"lastUpdatedTime", ConvertMsToUtcDateTime, type nullable datetime}
    }),
    Typed = Table.TransformColumnTypes(WithDates, {
        {"instanceType", type text}, {"version", Int64.Type}, {"space", type text},
        {"externalId", type text}
    }),
    WithNestedTypes = Table.TransformColumns(Typed, {{"properties",
        each if _ = null then null else Text.FromBinary(Json.FromValue(_)), type nullable text}}),
    Partition = Table.SelectRows(WithNestedTypes, each [createdTime] >= RangeStart and [createdTime] < RangeEnd)
in
    Partition

GraphQL example

This example shows how to implement an incremental refresh with a GraphQL query for data model instances:

Incremental refresh with GraphQL

let
    // Convert parameters to ISO 8601 format
    StartIso = ConvertDateTimeToIso(RangeStart),
    EndIso = ConvertDateTimeToIso(RangeEnd),
    // Create the GraphQL query with timestamp filters
    QueryText = "query MyQuery(
        $cursor: String,
        $rangeStart: Timestamp,
        $rangeEnd: Timestamp
        ) {
        listCogniteTimeSeries(
            first: 1000,
            filter: {
            and: [
                { createdTime: { gte: $rangeStart } },
                { createdTime: { lt: $rangeEnd } }
            ]
            },
            after: $cursor
        ) {
            items {
            type
            space
            sourceUnit
            name
            lastUpdatedTime
            isStep
            externalId
            description
            createdTime
            }
            pageInfo { hasNextPage endCursor }
        }
    }",
    // Define the variables for the GraphQL query
    QueryVariables = [
        rangeStart = StartIso,
        rangeEnd = EndIso
    ],
    // Convert the variables to JSON text
    QueryVariablesText = Text.FromBinary(Json.FromValue(QueryVariables)),
    // Fetch data using GraphQL
    NodesTable = GraphQL("cdf_cdm", "CogniteCore", "v1", QueryText, QueryVariablesText),
    // Define the expected schema
    Columns = {
        "type",
        "space",
        "sourceUnit",
        "name",
        "lastUpdatedTime",
        "isStep",
        "externalId",
        "description",
        "createdTime"
    },
    // Enforce the schema to ensure a consistent table even if no data is returned
    FinalTable = EnforceSchema(NodesTable, Columns),
    WithDates = Table.TransformColumns(FinalTable, List.Transform(
        {"createdTime", "lastUpdatedTime"}, each {_,
            (value) => if value = null then null else
                DateTimeZone.RemoveZone(DateTimeZone.SwitchZone(DateTimeZone.FromText(value), 0)),
            type nullable datetime})),
    Typed = Table.TransformColumnTypes(WithDates, {
        {"type", type text}, {"space", type text}, {"sourceUnit", type text}, {"name", type text},
        {"isStep", type logical}, {"externalId", type text}, {"description", type text}
    }),
    Partition = Table.SelectRows(Typed, each [createdTime] >= RangeStart and [createdTime] < RangeEnd)
in
    Partition

ODataCDF example

This example shows incremental refresh with ODataCDF using column-level filtering, which Power BI can push down to the server. Note that when using OData you don’t need to enforce any schema since the OData service always returns a table with the same schema, even if no data is returned.

Incremental refresh with ODataCDF (column filtering)

let
    // Convert parameters to datetimezone format
    RangeStartUTC = ConvertDateTimeToDateTimeZone(RangeStart),
    RangeEndUTC = ConvertDateTimeToDateTimeZone(RangeEnd),
    // Access the OData service
    Source = ODataCDF("publicdata"),
    // Access the Events table
    EventsTable = Source{[Name="Events",Signature="table"]}[Data],
    // Filter the Events table using RangeStart and RangeEnd parameters
    // This filter will be pushed down to the OData service
    EventsWithRangeFilter = Table.SelectRows(
        EventsTable,
        each [CreatedTime] >= RangeStartUTC and [CreatedTime] < RangeEndUTC
    )
in
    EventsWithRangeFilter

Configure an incremental refresh

When you’ve implemented the filtering using RangeStart and RangeEnd, follow these steps to configure an incremental refresh:

Define parameter values for testing

In Power BI Desktop, define RangeStart and RangeEnd with values that limit the data loaded to a small period for development and testing.

Apply filtering logic

Apply the filtering logic using one of the supported functions shown in the examples.

Load the data

Load the data by selecting Close & Apply in the Power Query editor.

Configure incremental refresh policy

Go to the main Power BI Desktop window, right-click the table you want to configure for incremental refresh in the Fields pane. Select Incremental refresh to open the incremental refresh policy configuration window.

Publish to Power BI Service

Publish your model to the Power BI Service when you’ve configured the policy.

Refresh in Power BI Service

When you’ve published your model with an incremental refresh policy, follow these steps to refresh data:

Perform initial manual refresh

Perform a manual refresh to load all historical data according to your policy. This initial refresh will take longer as it needs to build all partitions and load the historical data.

Configure scheduled refresh

Configure a scheduled refresh to update only the incremental data according to your policy. These subsequent refreshes will be much faster as they only process the most recent data.

Learn more:

Microsoft: Incremental refresh and real-time data for semantic models
Microsoft: Configure incremental refresh for semantic models
Microsoft: Troubleshoot incremental refresh

​About incremental refresh

​Before you start

​Set up incremental refresh

​Utility functions

​ConvertDateTimeToMs

​ConvertDateTimeToIso

​ConvertDateTimeToDateTimeZone

​EnforceSchema

​ColumnNamesToText

​Incremental refresh examples

​GetCDF example

​PostCDF example

​GraphQL example

​ODataCDF example

​Configure an incremental refresh

​Refresh in Power BI Service

About incremental refresh

Before you start

Set up incremental refresh

Utility functions

ConvertDateTimeToMs

ConvertDateTimeToIso

ConvertDateTimeToDateTimeZone

EnforceSchema

ColumnNamesToText

Incremental refresh examples

GetCDF example

PostCDF example

GraphQL example

ODataCDF example

Configure an incremental refresh

Refresh in Power BI Service