Power Query functions and example queries

Copy and adapt the queries and Power Query functions on this page to fit your business needs.

Power BI template

To get started quickly, you can use a Power BI template file (.pbit) that includes all the utility functions and helper functions documented on this page. It also includes additional functions for incremental refresh. The template saves you from manually copying and pasting each function into your Power BI report.

Download Power BI template

Download the template file (.pbit) to get started with all utility functions pre-configured.

The template requires Power BI Desktop version 2.149.1429.0 (November 2025) or later. Update Power BI Desktop to the latest version to ensure compatibility.

What’s included

The template contains the following components:

Power BI template showing the included utility functions, data source connection, and helper functions

Folder	Description
Utils	Utility functions: timestamp conversion (`ConvertDateTimeZoneToMs`, `ConvertMsToDateTimeZone`, `ConvertDateTimeZoneToIso`, `ConvertDateTimeToMs`, `ConvertDateTimeToIso`, `ConvertDateTimeToDateTimeZone`, `ConvertMsToUtcDateTime`), schema enforcement (`EnforceSchema`), and column name conversion (`ColumnNamesToText`). For detailed information, see utility functions and incremental refresh.
CogniteDataSource	Pre-configured connection to the Cognite REST API Power BI connector with `CogniteProject` and `CogniteOrganization` parameters ready to update.
CogniteHelperFunctions	Helper functions: `RetrieveSequenceRows`, `ListInstancesDMS`, `QueryDMS`, `RetrieveDataPoints`, `RetrieveDataPointsMultipleTs`, and `RetrieveRecords`.
Other Queries	Pre-configured `RangeStart` and `RangeEnd` parameters for incremental refresh, along with derived variables (`RangeStartMs`, `RangeEndMs`, `RangeStartDtz`, `RangeEndDtz`, `RangeStartIso`, `RangeEndIso`) ready to use with any connector function.

Getting started with the template

Download and open the template

Download the template file and open it in Power BI Desktop. Opening a template creates a new report and doesn’t modify the template file.

Enter connection parameters

When prompted, enter your CDF project name in CogniteProject and your organization in CogniteOrganization. You can also set RangeStart and RangeEnd, or keep the default values. To change the values later, select Transform Data > Edit parameters.

When prompted, sign in with your organizational account to authenticate with CDF.

Start building

Select Transform Data to open the Power Query editor and view the functions. Create your queries using the pre-configured functions, or use them as a reference alongside the documentation on this page.

Save the report

Save your work as a Power BI report file (.pbix).

Use the template as a companion file and refer to the connector functions, this page, and the incremental refresh documentation for detailed explanations, parameters, and usage examples.

The template file and Power Query functions are provided as reference implementations and are not covered by Cognite Support. You can modify them to fit your specific needs. If you encounter issues or have suggestions, report them on Cognite Hub or open a support ticket for evaluation.

Utility functions

Convert timestamps from/to epoch

CDF resource types expect and return timestamps using milliseconds since the Unix epoch. Power Query doesn’t have methods to automatically parse this format to a datetimezone type to represent timezone-aware timestamps. CDF data models represent timestamps using the ISO 8601 format for primitive fields. Use the functions below to convert between a datetimezone variable and milliseconds since the Unix epoch and from a datetimezone variable to text in ISO 8601 format. ConvertMsToUtcDateTime returns a plain datetime that represents UTC, rather than a zone-aware value. Use it when the result is compared against the RangeStart and RangeEnd parameters used for incremental refresh. For the opposite direction, use ConvertDateTimeToMs.

ConvertDateTimeZoneToMs

Convert datetimezone to milliseconds since epoch

(dtz as nullable datetimezone) as nullable number =>
if dtz = null then null else
let
    // Convert the input DateTimeZone to UTC
    UtcDateTime = DateTimeZone.RemoveZone(DateTimeZone.SwitchZone(dtz, 0)),
    // Define the Unix epoch start
    UnixEpochStart = #datetime(1970, 1, 1, 0, 0, 0),
    // Calculate the duration between the input date and Unix epoch start
    Delta = UtcDateTime - UnixEpochStart,
    // Round to millisecond precision to avoid floating-point truncation.
    TotalMilliseconds = Number.Round(Duration.TotalSeconds(Delta) * 1000)
in
    TotalMilliseconds

ConvertMsToDateTimeZone

Convert milliseconds since epoch to datetimezone

(ms as nullable number) as nullable datetimezone =>
if ms = null then null else
let
    // Convert ms to seconds
    SecondsSinceEpoch = ms / 1000,
    // Create a duration
    DurationSinceEpoch = #duration(0, 0, 0, SecondsSinceEpoch),
    // Add duration to Unix epoch start to get UTC datetime
    UnixEpochStart = #datetime(1970, 1, 1, 0, 0, 0),
    UtcDateTime = UnixEpochStart + DurationSinceEpoch,
    // Mark the value as UTC, then convert it to the local time zone
    LocalDateTimeZone = DateTimeZone.ToLocal(DateTime.AddZone(UtcDateTime, 0))
in
    LocalDateTimeZone

ConvertMsToUtcDateTime

Convert milliseconds since epoch to a UTC datetime

(ms as nullable number) as nullable datetime =>
if ms = null then null else
let
    // Define the Unix epoch start
    UnixEpochStart = #datetime(1970, 1, 1, 0, 0, 0),
    // Convert ms to seconds and add the duration to the epoch start
    SecondsSinceEpoch = ms / 1000,
    UtcDateTime = UnixEpochStart + #duration(0, 0, 0, SecondsSinceEpoch)
in
    UtcDateTime

ConvertDateTimeZoneToIso

Convert DateTimeZone to ISO 8601 text representation

(dtz as nullable datetimezone) as nullable text =>
if dtz = null then null else
let
    // Use DateTimeZone.ToText with ISO 8601 format
    Result = DateTimeZone.ToText(dtz, [Format="yyyy-MM-ddTHH:mm:ss.fffzzz", Culture="en-US"])
in
    Result

Add function

To add a new function in Power Query, select Get Data > Blank Query and write your function or copy one of the functions below.

Time deltas

It’s common to define start and end timestamps based on time deltas. The values are updated when the semantic model is refreshed. In the example below, EndTime is the current time, and StartTime is 7 days before EndTime. The query returns a record with StartTime and EndTime in UTC. You can adapt it to use different time deltas.

Time deltas

let
    EndTime = DateTimeZone.FixedUtcNow(),
    StartTime = EndTime - #duration(7, 0, 0, 0)
in
    [StartTime = StartTime, EndTime = EndTime]

Common GET request

When you fetch data from CDF using the GetCDF function from the CDF REST API connector for Power BI, you must use query parameters to pass on filters to select which data to fetch. The example below shows how you can add the externalIdPrefix and limit query parameters from the /timeseries endpoint to the URL to filter data on the server side.

List all time series instances with an externalId starting with a specific string

let
    Source = GetCDF("/timeseries?externalIdPrefix=EVE&limit=1000")
in
    Source

Common POST request

When you fetch data from CDF with the PostCDF function, you must write a request body to select which data to fetch. The function accepts a text representation of the JSON body, but you can also write the body using a Power Query record data type and then convert it to a JSON text data type before passing the value to the PostCDF function.

List all data modeling instances for a view using the DMS API

let
    SpaceExternalId = "Geography",
    ViewExternalId = "City",
    ViewVersion = "1",
    Body = [
        sources = {
            [
                source = [
                    type = "view",
                    space = SpaceExternalId,
                    externalId = ViewExternalId,
                    version = ViewVersion
                ]
            ]
        },
        limit = 1000
    ],
    BodyText = Text.FromBinary(Json.FromValue(Body)),
    Source = PostCDF("/models/instances/list", BodyText)
in
    Source

Alternatively, you can manually write the POST body as text, but you need to escape double quotes (") with another set of double-quote characters:

let
    BodyText = "{""sources"": [{""source"": {""type"": ""view"", ""space"": ""Geography"", ""externalId"": ""City"", ""version"": ""1""}}], ""limit"": 1000}",
    Source = PostCDF("/models/instances/list", BodyText)
in
    Source

If you need to reuse a POST request, you can transform it into a Power Query function. For example:

(SpaceExternalId as text, ViewExternalId as text, ViewVersion as text) as table =>
let
    Body = [
        sources = {
            [
                source = [
                    type = "view",
                    space = SpaceExternalId,
                    externalId = ViewExternalId,
                    version = ViewVersion
                ]
            ]
        },
        limit = 1000
    ],
    BodyText = Text.FromBinary(Json.FromValue(Body)),
    Source = PostCDF("/models/instances/list", BodyText)
in
    Source

You can define the function name by right-clicking on the entry in the query list in the Power Query editor and selecting Rename. If the function above is named ListInstancesDMS, you can use it in a new query by entering the field values in Power Query or by writing a new query:

let
    Source = ListInstancesDMS("Geography", "City", "1")
in
    Source

GraphQL requests

When you fetch data from CDF using the GraphQL function, you must write a GraphQL request to select which data to fetch from a specific data model. The function expects you to specify the external ID of the space, the external ID of the data model, the version of the data model, the GraphQL query to run, and optionally a set of variables to be used in the query. The query below uses the GraphQL syntax and passes the variables as JSON texts. Using variables in the query makes it easier to parameterize and use with external values.

List all work orders with an end date greater than a specific date

let
    Source = GraphQL(
        "cdf_idm",
        "CogniteProcessIndustries",
        "v1",
        "query MyQuery($cursor: String, $endTime: Timestamp) {#(lf)  listCogniteMaintenanceOrder(#(lf)    first: 1000#(lf)    after: $cursor#(lf)    filter: {endTime: {gte: $endTime}}#(lf)  ) {#(lf)    items {#(lf)      name#(lf)      type#(lf)      startTime#(lf)      endTime#(lf)      priority#(lf)    }#(lf)    pageInfo {#(lf)      endCursor#(lf)      hasNextPage#(lf)    }#(lf)  }#(lf)}",
        "{""endTime"": ""2024-10-01T00:00:00+02:00""}"
    )
in
    Source

The #(lf) represents a line-break character. In the example above, the query was pasted into the text area field in Power BI and the variables were passed as JSON text. Notice how Power BI added the line breaks to the original query and expressed it as a single-line text variable. Alternatively, you can write the query directly as a multi-line text variable. You can define the variables as Power Query records and convert them to JSON text before passing them to the GraphQL function. For example, see how the ConvertDateTimeZoneToIso function converts a datetimezone variable to a text representation of the ISO 8601 format and then passes it as a variable to the query.

let
    // This could be a parameter or referenced from another query
    EndTime = #datetimezone(2024, 10, 1, 0, 0, 0, 2, 0),
    VariablesRecord = [
        endTime = ConvertDateTimeZoneToIso(EndTime)
    ],
    VariablesText = Text.FromBinary(Json.FromValue(VariablesRecord)),
    Query = "query MyQuery($cursor: String, $endTime: Timestamp) {
        listCogniteMaintenanceOrder(
            first: 1000
            after: $cursor
            filter: {endTime: {gte: $endTime}}
        ) {
            items {
                name
                type
                startTime
                endTime
                priority
            }
            pageInfo {
                endCursor
                hasNextPage
            }
        }
    }",
    Data = GraphQL(
        "cdf_idm",
        "CogniteProcessIndustries",
        "v1",
        Query,
        VariablesText
    )
in
    Data

Advanced examples

Depending on the shape of the Cognite API response, you may need additional Power Query transformations to fetch and transform the data. Copy and adapt the examples below to fit your business needs.

Fetch sequence rows with the `PostCDF` function

The Power Query function below fetches and processes sequence rows data for a sequence external ID from CDF. It sends a POST request, extracts column information, expands nested response data, and reorganizes it into tabular format. The function handles data type conversion, removes unnecessary fields, and groups data by row. The final output is a well-structured table with correctly typed columns.

RetrieveSequenceRows function

Fetch sequence rows with PostCDF

(externalId as text) as table =>
let
    RequestBody = Text.FromBinary(Json.FromValue([externalId = externalId, limit = 10000])),
    Response = PostCDF("/sequences/data/list", RequestBody),
    Columns = Response{0}[columns],
    ColumnNames = List.Transform(Columns, each [externalId]),
    ColumnTypes = List.Transform(Columns, each
        if [valueType] = "STRING" then type nullable text else
        if [valueType] = "DOUBLE" then type nullable number else
        if [valueType] = "LONG" then Int64.Type else type any),
    // PostCDF follows the top-level cursor. Preserve each row's value order.
    Rows = List.Combine(Response[rows]),
    Values = List.Transform(Rows, each {[rowNumber]} & [values]),
    Result = Table.FromRows(Values, {"rows.rowNumber"} & ColumnNames),
    Typed = Table.TransformColumnTypes(Result,
        {{"rows.rowNumber", Int64.Type}} & List.Zip({ColumnNames, ColumnTypes}))
in
    Typed

To use the function:

let
    Source = RetrieveSequenceRows("sequence-externalId")
in
    Source

Fetch instances from the DMS query endpoint with the `PostCDF` function

The Power Query function below retrieves and processes data modeling instances for a DMS query. It paginates through the response, extracts the instances, and expands the nested data.

QueryDMS function

Fetch instances from the DMS query endpoint with PostCDF

(query as text) as table =>
let
    Query = Json.Document(query),
    FetchPage = (cursors as nullable record) as table =>
        let
            // Keep all with expressions: active results can depend on completed ones.
            Body = if cursors = null then Query else Query & [
                select = Record.SelectFields(Query[select], Record.FieldNames(cursors)),
                cursors = cursors
            ]
        in
            PostCDF("/models/instances/query", Text.FromBinary(Json.FromValue(Body))),
    NextCursors = (page as table) as record =>
        Record.Combine(List.Transform(
            Table.ToRecords(Table.SelectRows(page, each [nextCursor] <> null)),
            each Record.FromList({[nextCursor]}, {[resultExpression]})
        )),
    // Emit every fetched page, including the page with no continuation cursors.
    Pages = List.Generate(
        () => FetchPage(null),
        each _ <> null,
        each let cursors = NextCursors(_)
            in if Record.FieldCount(cursors) = 0 then null else FetchPage(cursors)
    ),
    Combined = Table.Combine(Pages),
    Results = Table.Group(Combined, {"resultExpression"}, {{
        "items", each Table.FromRecords(List.Combine([items]), null, MissingField.UseNull), type table
    }})
in
    Results

To use the function:

let
    Query = [
        with = [
            cities = [
                nodes = [
                    filter = [
                        hasData = {
                            [
                                space = "Geography",
                                externalId = "City",
                                version = "1",
                                #"type" = "view"
                            ]
                        }
                    ],
                    chainTo = "destination",
                    direction = "outwards"
                ]
            ],
            countries = [
                nodes = [
                    filter = [
                        hasData = {
                            [
                                space = "Geography",
                                externalId = "Country",
                                version = "1",
                                #"type" = "view"
                            ]
                        }
                    ],
                    chainTo = "destination",
                    direction = "outwards"
                ]
            ]
        ],
        select = [
            cities = [
                sources = {
                    [
                        source = [
                            space = "Geography",
                            externalId = "City",
                            version = "1",
                            #"type" = "view"
                        ],
                        properties = {
                            "name"
                        }
                    ]
                }
            ],
            countries = [
                sources = {
                    [
                        source = [
                            space = "Geography",
                            externalId = "Country",
                            version = "1",
                            #"type" = "view"
                        ],
                        properties = {
                            "name"
                        }
                    ]
                }
            ]
        ]
    ],
    QueryText = Text.FromBinary(Json.FromValue(Query)),
    Source = QueryDMS(QueryText)
in
    Source

Add filters like the hasData filter in the example above to avoid fetching all instances from CDF.

Fetch time series datapoints with the `PostCDF` function

The Power Query function below retrieves and processes aggregated time series datapoints for a time series within a time range. It converts local timezone inputs to UTC for the CDF API requests, supports multiple aggregates and custom granularity, and handles data pagination. The function then converts the returned UTC timestamps back to the local timezone, expands the nested API response, and outputs a well-formatted table with properly typed columns. It also includes local timestamps and decimal aggregate values. Use the optional fillLimit parameter to enable server-side gap filling for aggregate queries. The function sends fill = [limit = fillLimit] on the query item in every request, including subsequent pages.

`fillLimit`	Behavior
Omitted or `null`	Omits `fill` from the request and preserves the default CDF behavior.
Duration, for example `"4h"`	Fills gaps up to this duration. Requires `aggregates` and `granularity`. The duration must be at least the granularity and cannot use month-based units.

RetrieveDataPoints function

Fetch time series datapoints with PostCDF

(
    item as record,
    start as datetimezone,
    optional end as nullable datetimezone,
    optional aggregates as nullable text,
    optional granularity as nullable text,
    optional targetUnit as nullable text,
    optional targetUnitSystem as nullable text,
    optional timeZone as nullable text,
    optional fillLimit as nullable text
) =>
    let
        // Function to detect query type based on item record structure
        DetectQueryType = (item as record) =>
            let
                Fields = Record.FieldNames(item),
                HasId = List.Contains(Fields, "id"),
                HasExternalId = List.Contains(Fields, "externalId"),
                HasSpace = List.Contains(Fields, "space"),
                FieldCount = List.Count(Fields),
                QueryType =
                    if HasId and not HasExternalId and not HasSpace and FieldCount = 1 then "id"
                    else if HasExternalId and not HasId and not HasSpace and FieldCount = 1 then "externalId"
                    else if HasExternalId and HasSpace and not HasId and FieldCount = 2 then "instanceId"
                    else error Error.Record("Invalid item content", "The item record does not match any supported query type", item)
            in QueryType,

        // Detect query type
        queryType = DetectQueryType(item),
        // Determine limit based on presence of aggregates
        limit = if aggregates <> null then 10000 else 100000,
        // Convert aggregates from comma-separated string to list format accepted by the API
        AggregatesList = Text.Split(aggregates, ","),
        AggregatesTrimmedList = List.Transform(AggregatesList, each Text.Trim(_)),
        StartMs = Number.Round(ConvertDateTimeZoneToMs(start)),
        EndMs = Number.Round(ConvertDateTimeZoneToMs(end)),
        // Function to fetch a single page of data
        FetchPage = (cursor as nullable text) =>
            let
                // Build body item
                bodyItem =
                    if queryType = "id" then
                        [id = Record.Field(item, "id")]
                            & (if targetUnit <> null then [targetUnit = targetUnit] else [])
                            & (if targetUnitSystem <> null then [targetUnitSystem = targetUnitSystem] else [])
                            & (if cursor <> null then [cursor = cursor] else [])
                    else if queryType = "externalId" then
                        [externalId = Record.Field(item, "externalId")]
                            & (if targetUnit <> null then [targetUnit = targetUnit] else [])
                            & (if targetUnitSystem <> null then [targetUnitSystem = targetUnitSystem] else [])
                            & (if cursor <> null then [cursor = cursor] else [])
                    else if queryType = "instanceId" then
                        [ instanceId = [ space = Record.Field(item, "space"), externalId = Record.Field(item, "externalId") ] ]
                            & (if targetUnit <> null then [targetUnit = targetUnit] else [])
                            & (if targetUnitSystem <> null then [targetUnitSystem = targetUnitSystem] else [])
                            & (if cursor <> null then [cursor = cursor] else [])
                    else error "Invalid query type",

                // Build request body
                body = [items = {bodyItem & (if fillLimit <> null then [fill = [limit = fillLimit]] else [])}, limit = limit, ignoreUnknownIds = true, start = Text.From(StartMs)]
                    & (if end <> null then [end = Text.From(EndMs)] else [])
                    & (if aggregates <> null then [aggregates = AggregatesTrimmedList] else [])
                    & (if granularity <> null then [granularity = granularity] else [])
                    & (if timeZone <> null then [timeZone = timeZone] else []),

                Response = PostCDF("/timeseries/data/list", Text.FromBinary(Json.FromValue(body))),

                // Try to fetch the cursor from the first item in the response
                FirstItem = if Type.Is(Value.Type(Response), type table) and Table.RowCount(Response) > 0
                    then Table.First(Response) else null,
                NextCursor = if FirstItem <> null then Record.FieldOrDefault(FirstItem, "nextCursor", null) else null,

                // Handles empty response and extracts data points when present
                FinalItemsList =
                    if Table.HasColumns(Response, "datapoints") then
                        let
                            // Clean up the response table
                            ColumnsToRemove = {"nextCursor", "isStep", "unit"},
                            ColumnsPresent = List.Intersect({Table.ColumnNames(Response), ColumnsToRemove}),
                            CleanedTable = Table.RemoveColumns(Response, ColumnsPresent),
                            // Expand the "datapoints" column
                            ExpandedDatapointsList = Table.ExpandListColumn(CleanedTable, "datapoints"),
                            // Handles the case where the list of "datapoints" is empty
                            FinalDataPointsList =
                                if List.NonNullCount(ExpandedDatapointsList[datapoints]) > 0 then
                                    let
                                        // Filled buckets can omit sample-based aggregates.
                                        AvailableFields = {"timestamp"} & (if aggregates <> null then AggregatesTrimmedList else {"value"}),
                                        // Expand the "datapoints" records using the available fields
                                        ExpandedDatapointsRecords = Table.ExpandRecordColumn(
                                            ExpandedDatapointsList, "datapoints", AvailableFields, AvailableFields
                                        )
                                    in Table.ToRecords(ExpandedDatapointsRecords)
                                else {}
                        in FinalDataPointsList
                    else Table.ToRecords(Response)
            in {FinalItemsList, NextCursor},

        // Recursive function to accumulate all pages of data
        AccumulateData = (cursor as nullable text, accumulatedItems as list) =>
            let
                CurrentPage = FetchPage(cursor),
                NewItems = CurrentPage{0},
                NextCursor = CurrentPage{1},
                UpdatedAccumulatedItems = accumulatedItems & NewItems,
                Result = if NextCursor <> null
                    then @AccumulateData(NextCursor, UpdatedAccumulatedItems)
                    else UpdatedAccumulatedItems
            in Result,

        // Fetch all data
        AllItems = AccumulateData(null, {}),

        // Convert the accumulated items to a table
        ConvertToTable = Table.FromList(AllItems, Splitter.SplitByNothing(), {"Column1"}, null, ExtraValues.Error),

        // Expand the table column and convert timestamps
        ExpandedTable =
            if not Table.IsEmpty(ConvertToTable) and Table.HasColumns(ConvertToTable, "Column1") then
                let
                    TmpTable = Table.ExpandRecordColumn(ConvertToTable, "Column1", Record.FieldNames(ConvertToTable{0}[Column1])),
                    // timestamp should be always present when there are datapoints
                    FixType = Table.TransformColumnTypes(TmpTable, {{"timestamp", Int64.Type}}),
                    ParseTimestamp = Table.TransformColumns(FixType, {"timestamp", each ConvertMsToDateTimeZone(_)}),
                    ParsedWithType = Table.TransformColumnTypes(ParseTimestamp, {{"timestamp", type datetimezone}}),
                    // check if the timeseries is of type string
                    FirstEntry = ParsedWithType{0},
                    IsString = FirstEntry[isString],
                    CleanedTable = Table.RemoveColumns(ParsedWithType, {"isString"}),
                    // Convert aggregate/value columns to decimal number
                    ValuesAsDecimal =
                        if aggregates <> null then
                            Table.TransformColumnTypes(CleanedTable, List.Transform(AggregatesTrimmedList, each {_, type number}))
                        else if IsString then Table.TransformColumnTypes(CleanedTable, {{"value", type text}})
                        else Table.TransformColumnTypes(CleanedTable, List.Transform({"value"}, each {_, type number})),
                    // Check if "id" column is present and convert to integer
                    IdAsInteger = if Table.HasColumns(ValuesAsDecimal, "id")
                        then Table.TransformColumnTypes(ValuesAsDecimal, {{"id", Int64.Type}})
                        else ValuesAsDecimal
                in IdAsInteger
            else ConvertToTable
    in
        if fillLimit <> null and (aggregates = null or granularity = null) then
            error "fillLimit requires aggregates and granularity."
        else ExpandedTable

RetrieveDataPointsMultipleTs function

Fetch multiple time series datapoints with PostCDF

(
    externalIds as list,
    start as datetimezone,
    end as datetimezone,
    aggregates as text,
    granularity as text,
    optional targetUnitSystem as nullable text,
    optional timeZone as nullable text,
    optional fillLimit as nullable text
) =>
let
    TablesList = List.Transform(
        externalIds,
        each RetrieveDataPoints(
            [ externalId = _ ],
            start,
            end,
            aggregates,
            granularity,
            null,
            targetUnitSystem,
            timeZone,
            fillLimit
        )
    ),
    CombinedTable = Table.Combine(TablesList)
in
    CombinedTable

The functions handle complex scenarios, including pagination, data type conversion, and nested data expansion. To use the function:

let
    Source = RetrieveDataPoints(
        [ externalId = "EVE-TI-FORNEBU-01-3" ],
        #datetimezone(2024, 10, 1, 0, 0, 0, 2, 0),
        #datetimezone(2024, 10, 13, 10, 0, 0, 2, 0),
        "average,max,min",
        "1d",
        null,
        "SI",
        "Europe/Oslo",
        null
    )
in
    Source

To fill gaps of up to four hours with hourly aggregates, pass "4h" as the last parameter:

let
    Source = RetrieveDataPoints(
        [ externalId = "EVE-TI-FORNEBU-01-3" ],
        #datetimezone(2024, 10, 1, 0, 0, 0, 2, 0),
        #datetimezone(2024, 10, 13, 10, 0, 0, 2, 0),
        "average,max,min",
        "1h",
        null,
        "SI",
        "Europe/Oslo",
        "4h"
    )
in
    Source

To use RetrieveDataPointsMultipleTs to iterate over a list of time series external IDs with daily aggregates and a two-day fill limit:

let
    Source = RetrieveDataPointsMultipleTs(
        {"EVE-TI-FORNEBU-01-2", "EVE-TI-FORNEBU-01-3"},
        #datetimezone(2024, 10, 1, 0, 0, 0, 2, 0),
        #datetimezone(2024, 10, 13, 10, 0, 0, 2, 0),
        "average,max,min",
        "1d",
        "SI",
        "Europe/Oslo",
        "2d"
    )
in
    Source

Fetch records from a stream with the `PostCDF` function

The Power Query function below fetches records through a record view. It reads the stream ID, property names, and data types from the view, then returns a table with a column for each selected property. You can use this function with all stream types, including for incremental refresh. It selects records by lastUpdatedTime, the time CDF last created, updated, or deleted the record. For mutable streams, you also need to select the latest row for each record and keep deletion rows long enough to prevent older rows from reappearing. See Immutable and mutable streams before setting up refresh for a mutable stream.

Parameter	Description
`recordViewSpace`, `recordViewExtId`, `recordViewVer`	The record view to read through
`startUtc`, `endUtc`	Window on `lastUpdatedTime`, in UTC, at millisecond precision. Records with `startUtc <= lastUpdatedTime < endUtc` are returned
`columnPrefix`	Optional prefix for the view property columns, for example `"alarm."`. Keeps them apart from the record’s own fields
`properties`	Optional list of view properties to fetch, for example `{"Time", "Severity", "Message"}`. Defaults to `{"*"}`, which fetches all of them. Fewer properties means a smaller payload

Create the PostCDF query from the connector and add ConvertMsToUtcDateTime from this page, plus ConvertDateTimeToMs and EnforceSchema from Utility functions.

RetrieveRecords function

Fetch records from a stream with PostCDF

(
    recordViewSpace as text,
    recordViewExtId as text,
    recordViewVer as text,
    startUtc as datetime,
    endUtc as datetime,
    optional columnPrefix as nullable text,
    optional properties as nullable list
) as table =>
let
    Views = PostCDF("/models/views/byids", Text.FromBinary(Json.FromValue([
        items = {[space = recordViewSpace, externalId = recordViewExtId, version = recordViewVer]},
        includeInheritedProperties = true
    ]))),
    ViewRecord = if Table.RowCount(Views) <> 1 then
        error Error.Record("RetrieveRecords", "Expected exactly one view. Check the space, external ID, and version.")
        else Views{0},
    Streams = Record.FieldOrDefault(ViewRecord, "streamId", null),
    View = if Record.FieldOrDefault(ViewRecord, "usedFor", null) <> "record" then
        error Error.Record("RetrieveRecords", "The view must have usedFor = record.")
        else if not Value.Is(Streams, type list) then
            error Error.Record("RetrieveRecords", "The record view must reference exactly one stream.")
        else if List.Count(Streams) <> 1 then
            error Error.Record("RetrieveRecords", "The record view must reference exactly one stream.")
        else if not Value.Is(Streams{0}, type text) then
            error Error.Record("RetrieveRecords", "The stream ID must be text.")
        else ViewRecord,
    StreamId = View[streamId]{0},
    ViewDef = View[properties],
    Prefix = if columnPrefix = null then "" else columnPrefix,
    Properties = if properties = null or List.IsEmpty(properties) then {"*"} else properties,
    // The sync cursor must start at or before startUtc, or the window is never
    // reached. Derive it from startUtc, rounded up to whole days.
    LookbackDays = Number.RoundUp(List.Max({
        1,
        Duration.TotalDays(DateTimeZone.RemoveZone(DateTimeZone.UtcNow()) - startUtc) + 1
    })),
    InitialCursor = Number.ToText(LookbackDays, "0", "en-US") & "d-ago",

    SyncBody = Text.FromBinary(Json.FromValue([
        sources = {[
            source = [
                type = "view",
                space = recordViewSpace,
                externalId = recordViewExtId,
                version = recordViewVer
            ],
            properties = Properties
        ]},
        filter = [range = [
            property = {"lastUpdatedTime"},
            gte = ConvertDateTimeToMs(startUtc),
            lt = ConvertDateTimeToMs(endUtc)
        ]],
        initializeCursor = InitialCursor,
        limit = 1000
    ])),

    BaseColumns = {"space", "externalId", "createdTime", "lastUpdatedTime", "status", "properties"},
    RawTable = if startUtc = endUtc then #table(BaseColumns, {})
        else PostCDF("/streams/" & Uri.EscapeDataString(StreamId) & "/records/sync", SyncBody),
    CleanTable = EnforceSchema(
        Table.SelectRows(RawTable, each List.NonNullCount(Record.ToList(_)) > 0),
        BaseColumns),

    AllPropertyNames = List.Sort(Record.FieldNames(ViewDef)),
    PropertyNames = if Properties = {"*"} then AllPropertyNames
        else List.Select(AllPropertyNames, each List.Contains(Properties, _)),
    PropertyColumns = List.Transform(PropertyNames, each Prefix & _),
    ViewKey = recordViewExtId & "/" & recordViewVer,

    // Tombstones can omit properties. Use status to identify deletions,
    // even if a response still includes the previous property values.
    Unpacked = Table.AddColumn(CleanTable, "RecordProps", each
        if [properties] = null then []
        else Record.FieldOrDefault(Record.FieldOrDefault([properties], recordViewSpace, []), ViewKey, []),
        type record),
    Expanded = Table.ExpandRecordColumn(
        Table.RemoveColumns(Unpacked, {"properties"}),
        "RecordProps", PropertyNames, PropertyColumns),

    Kind = (name as text) as text => Record.Field(ViewDef, name)[type][type],
    IsList = (name as text) as logical =>
        Record.FieldOrDefault(Record.Field(ViewDef, name)[type], "list", false),

    WithDates = Table.TransformColumns(Expanded, {
        {"createdTime", ConvertMsToUtcDateTime, type nullable datetime},
        {"lastUpdatedTime", ConvertMsToUtcDateTime, type nullable datetime}
    }),
    Convert = (name as text) => (v as any) =>
        if v = null then null
        else if IsList(name) or List.Contains({"json", "direct"}, Kind(name))
            then Text.FromBinary(Json.FromValue(v), TextEncoding.Utf8)
        else if Kind(name) = "timestamp"
            then DateTimeZone.RemoveZone(DateTimeZone.SwitchZone(DateTimeZone.FromText(v), 0))
        else if Kind(name) = "date" then Date.FromText(v, [Culture = "en-US"])
        else v,
    Converted = Table.TransformColumns(WithDates,
        List.Transform(PropertyNames, each {Prefix & _, Convert(_)})),

    PqType = (name as text) as type =>
        if IsList(name) or List.Contains({"text", "enum", "json", "direct"}, Kind(name)) then type nullable text
        else if Kind(name) = "timestamp" then type nullable datetime
        else if Kind(name) = "date" then type nullable date
        else if List.Contains({"int32", "int64"}, Kind(name)) then Int64.Type
        else if List.Contains({"float32", "float64"}, Kind(name)) then type nullable number
        else if Kind(name) = "boolean" then type nullable logical
        else type nullable text,
    Typed = Table.TransformColumnTypes(Converted,
        {{"space", type text}, {"externalId", type text}, {"status", type text}}
        & List.Transform(PropertyNames, each {Prefix & _, PqType(_)}), "en-US")
in
    if startUtc > endUtc then
        error Error.Record("RetrieveRecords", "startUtc must be at or before endUtc.")
    else if Properties <> {"*"} and not List.IsEmpty(List.Difference(Properties, AllPropertyNames)) then
        error Error.Record("RetrieveRecords", "Unknown view properties.", List.Difference(Properties, AllPropertyNames))
    else if not List.IsEmpty(List.Intersect({PropertyColumns, BaseColumns})) then
        error Error.Record("RetrieveRecords", "View properties overlap record fields. Set columnPrefix.")
    else Typed

The function reads the stream ID from the view and rejects views whose usedFor is not record or that do not reference exactly one stream. An empty window (startUtc = endUtc) validates the view and returns the typed schema without reading records. A reversed window is an error. To use the function:

let
    Source = RetrieveRecords(
        "opcua_alarms",
        "OpcUaAlarm",
        "v1",
        #datetime(2026, 9, 8, 10, 0, 0),
        #datetime(2026, 9, 8, 11, 0, 0),
        "alarm."
    )
in
    Source

For incremental refresh, Power BI divides the table into date ranges called partitions. Pass RangeStart and RangeEnd to fetch each range, and include the row filter shown below so Power BI recognizes the date limits. This query works with either stream type. For mutable streams, also apply the latest-row and deletion handling described below.

let
    Source = RetrieveRecords(
        "opcua_alarms", "OpcUaAlarm", "v1",
        RangeStart, RangeEnd, "alarm."
    ),
    Partition = Table.SelectRows(Source,
        each [lastUpdatedTime] >= RangeStart and [lastUpdatedTime] < RangeEnd
    )
in
    Partition

Immutable and mutable streams

Both stream types can use incremental refresh. The difference is how you handle the rows after loading them:

Stream type	What the query returns	What to keep in the report
Immutable	Stored versions, with `status = "created"`	Keep each version you need to report on.
Mutable	The latest available row for each matching record, with `status` set to `created`, `updated`, or `deleted`	Across all loaded rows, select the latest row for each record, then exclude records whose latest row is deleted.

A record is identified by its space and externalId. In an immutable stream, keep all stored versions you need. Several versions can share the same space, externalId, and lastUpdatedTime, so deduplicating by those fields can discard valid records. In a mutable stream, the same record identified by space and externalId can have several updates between refreshes with status created, updated, or deleted. A row with status = "deleted" is a deletion notice, also called a tombstone. It may still contain the record’s previous property values, so always check status to identify deleted records. For smaller mutable streams, the simplest setup is a full refresh: reload all records still stored in CDF, then remove deleted rows:

Exclude deleted records

Table.SelectRows(Records, each [status] <> "deleted")

Keep deleted records out of incremental refresh results

With incremental refresh, Power BI reloads recent date ranges and keeps older ranges without reloading them. These ranges are called partitions. For example, you might keep one year of data but reload only the last seven days on each refresh. CDF returns deletion rows for at least three days after a record is deleted. Once loaded into Power BI, a deletion row stays there until Power BI replaces or removes its partition. If Power BI reloads that date range after CDF stops returning the deletion, the replacement partition no longer contains the deletion row. To use incremental refresh with a mutable stream, configure frequent refreshes so deletions load while CDF still returns them. Allow time for retries if a refresh fails. Because partitions can hold several rows for the same record, select the latest row for each record and exclude the deleted ones in the report. Add a calculated table that keeps only the row with the highest lastUpdatedTime per space and externalId, and drops records whose latest row is a deletion:

Keep the latest row for each record and exclude deletions

LatestRecords =
FILTER (
    Records,
    Records[lastUpdatedTime]
        = CALCULATE (
            MAX ( Records[lastUpdatedTime] ),
            ALLEXCEPT ( Records, Records[space], Records[externalId] )
        )
        && Records[status] <> "deleted"
)

Size the initial load

The first refresh after publishing loads the entire period you chose to keep. Later refreshes reload only the recent period you configured. Each date range includes all records whose lastUpdatedTime falls within it. Check these two limits when planning the first load:

Limit	Value	Reference
Read throughput per stream	Depends on the stream template used to create the stream	Stream template limits
Refresh duration	2 hours on shared capacity, 5 hours on Premium or Fabric	Data refresh in Power BI

All reads from a stream share its download limit, even when Power BI loads several date ranges at once. To reduce download time, use the properties parameter to select only the properties your report needs. Estimate the payload as records × bytes per record. Measure representative responses with the properties you select. For an illustrative estimate, assume 900 bytes per record for a full view or 360 bytes for a subset. For a stream that ingests 7 million records a day:

At 900 bytes per record, a day is about 6.3 GB. At an assumed effective throughput of 0.83 MB/s, that is about 2.1 hours per day, before model processing. Seven days takes about 15 hours and exceeds a standard refresh window.
At 360 bytes per record, a day is about 2.5 GB and roughly 50 minutes at the same assumed throughput, before model processing.

Measure actual refresh duration: request latency, connector pacing, retries, transformations, and model processing add to transfer time. Premium XMLA refreshes provide additional options for bootstrapping large initial loads. In Power BI Desktop, keep RangeStart and RangeEnd a few minutes apart so the file stays small. The service builds the full history on the first refresh after you publish.

Backfill large histories outside Power BI

For streams with a large history, you can download older records to files first. Export a data dump of the records using the API, then set up incremental refresh only with recent data, combining all records into a single table in Power BI:

Table	What it contains	How it is refreshed
`HistoricalRecords`	Older records loaded from the exported files	Load once, then exclude from automatic refresh.
`RecentRecords`	Records from CDF starting where the files end	Use incremental refresh.
`AllRecords`	Rows from both tables, combined with DAX	Power BI recalculates it when its input tables refresh. Use this table in your report.

Microsoft requires one data source for all date ranges in an incrementally refreshed table. This is why the files and API use separate tables, and only RecentRecords uses incremental refresh. Power BI stores the combined AllRecords table as well as its two input tables, so this approach needs extra storage and processing time. If the combined data is too large for your Power BI environment, store the history and new records together in an external database or other supported source, then load from that source.

Learn more: Microsoft Power Query documentation

​Power BI template

Download Power BI template

​What’s included

​Getting started with the template

​Utility functions

​Convert timestamps from/to epoch

​Add function

​Time deltas

​Common GET request

​Common POST request

​GraphQL requests

​Advanced examples

​Fetch sequence rows with the PostCDF function

​Fetch instances from the DMS query endpoint with the PostCDF function

​Fetch time series datapoints with the PostCDF function

​Fetch records from a stream with the PostCDF function

​Immutable and mutable streams

​Keep deleted records out of incremental refresh results

​Size the initial load

​Backfill large histories outside Power BI

Power BI template

What’s included

Getting started with the template

Utility functions

Convert timestamps from/to epoch

Add function

Time deltas

Common GET request

Common POST request

GraphQL requests

Advanced examples

Fetch sequence rows with the `PostCDF` function

Fetch instances from the DMS query endpoint with the `PostCDF` function

Fetch time series datapoints with the `PostCDF` function

Fetch records from a stream with the `PostCDF` function

Immutable and mutable streams

Keep deleted records out of incremental refresh results

Size the initial load

Backfill large histories outside Power BI