Power Query functions and example queries

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

This page assumes you know how to:

Edit queries in the Power Query editor and read and adapt M code, including custom functions.
Work with REST APIs in general: HTTP methods, JSON request and response bodies, and cursor-based pagination.
Use the CDF API: find the endpoint you need, build its request body and filters, and follow its pagination rules. See the Cognite API introduction.

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 a zip file that contains the template file (TemplateCDF.pbit), 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: `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 TemplateCDF.zip, extract TemplateCDF.pbit, 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 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 adds the space, includeGlobal, and limit query parameters of the /models/views endpoint to the URL to filter data on the server side.

List the views in the cdf_cdm space

let
    Source = GetCDF("/models/views?space=cdf_cdm&includeGlobal=true&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 = "cdf_cdm",
    ViewExternalId = "CogniteAsset",
    ViewVersion = "v1",
    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"": ""cdf_cdm"", ""externalId"": ""CogniteAsset"", ""version"": ""v1""}}], ""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("cdf_cdm", "CogniteAsset", "v1")
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 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
    AssetView = [
        space = "cdf_cdm",
        externalId = "CogniteAsset",
        version = "v1",
        #"type" = "view"
    ],
    EquipmentView = [
        space = "cdf_cdm",
        externalId = "CogniteEquipment",
        version = "v1",
        #"type" = "view"
    ],
    Query = [
        with = [
            assets = [
                nodes = [
                    filter = [
                        hasData = { AssetView }
                    ]
                ],
                limit = 1000
            ],
            // Follow the asset direct relation on each equipment back to its asset
            equipment = [
                nodes = [
                    from = "assets",
                    through = [
                        source = EquipmentView,
                        identifier = "asset"
                    ],
                    direction = "inwards",
                    filter = [
                        hasData = { EquipmentView }
                    ]
                ],
                limit = 1000
            ]
        ],
        select = [
            assets = [
                sources = {
                    [
                        source = AssetView,
                        properties = { "name", "description" }
                    ]
                }
            ],
            equipment = [
                sources = {
                    [
                        source = EquipmentView,
                        properties = { "name", "manufacturer", "asset" }
                    ]
                }
            ]
        ]
    ],
    QueryText = Text.FromBinary(Json.FromValue(Query)),
    Source = QueryDMS(QueryText)
in
    Source

The equipment result follows the asset direct relation on CogniteEquipment back to the selected assets. You can’t traverse a list of direct relations inwards, such as assets on CogniteActivity. To go from activities to their assets, query the activities and use direction = "outwards".

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 raw or aggregated datapoints for a numeric, string, or state 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. Identify a time series by its instance ID, which is its space and externalId, written as [ space = "my_space", externalId = "my_time_series" ]. The output includes the instanceId.space and instanceId.externalId columns, so you can tell the time series apart in a combined table. 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.

Output columns by time series type

The function reads the time series type from the response and returns these value columns. Each row also has timestamp, type, instanceId.space, and instanceId.externalId, plus unitExternalId if the time series has a unit.

Request	Numeric	String	State
Raw datapoints (no `aggregates`)	`value` (number)	`value` (text)	`numericValue` (whole number), `stringValue` (text)
`interpolation`, `stepInterpolation`	Column named after the aggregate (number)	Column named after the aggregate (text)	`interpolation.numericValue`, `interpolation.stringValue`, and the same for `stepInterpolation`
`count`, `countGood`, `countUncertain`, `countBad`, `durationGood`, `durationUncertain`, `durationBad`	Column named after the aggregate (number)	Column named after the aggregate (number)	Column named after the aggregate (number)
`stateCount`, `stateDuration`, `stateTransitions`	Not available	Not available	One row per state in each time range: `numericValue`, `stringValue`, and a column named after the aggregate (number)
Other aggregates, such as `average`	Column named after the aggregate (number)	Not available	Not available

CDF returns an error for an aggregate marked as not available, and for targetUnit or targetUnitSystem on a string or state time series. CDF accepts stateCount, stateDuration, and stateTransitions together with other aggregates, but this function can’t combine them in one table, because it returns one row per state for them. Request them in a separate call from other aggregates. A state with nothing to report in a time range has no row for that time range. For how CDF calculates these aggregates, see state aggregates. A time range with no datapoints returns the same columns with no rows, so you can use the function with incremental refresh. If a time series doesn’t exist, the function fails with a Time series not found error from CDF. RetrieveDataPointsMultipleTs fails the same way if any time series in the list doesn’t exist. The error doesn’t name the time series, so check the space and externalId of each one.

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
        // The item identifies the time series by its instance ID
        InstanceId =
            if Record.HasFields(item, {"space", "externalId"}) and Record.FieldCount(item) = 2 then
                [space = item[space], externalId = item[externalId]]
            else error Error.Record("Invalid item content", "The item must contain only space and externalId", 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(_)),
        // Aggregates that return the time series' own values: numbers, text, or states
        SeriesValueAggregates = {"interpolation", "stepInterpolation"},
        // Aggregates that state time series return once per state in each time bucket
        PerStateAggregates = {"stateCount", "stateDuration", "stateTransitions"},
        RequestedPerState = if aggregates <> null then List.Intersect({AggregatesTrimmedList, PerStateAggregates}) else {},
        StartMs = Number.Round(ConvertDateTimeZoneToMs(start)),
        EndMs = Number.Round(ConvertDateTimeZoneToMs(end)),
        // Response fields that aren't useful in a report
        RemovedFields = {"id", "nextCursor", "isStep", "unit"},
        // Function to fetch a single page of data
        FetchPage = (cursor as nullable text) =>
            let
                // Build body item
                bodyItem = [instanceId = InstanceId]
                    & (if targetUnit <> null then [targetUnit = targetUnit] else [])
                    & (if targetUnitSystem <> null then [targetUnitSystem = targetUnitSystem] else [])
                    & (if cursor <> null then [cursor = cursor] else []),

                // Build request body
                body = [items = {bodyItem & (if fillLimit <> null then [fill = [limit = fillLimit]] else [])}, limit = limit, 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))),

                // The response has one item, or CDF returns an error if the time series doesn't exist
                FirstItem = Table.First(Response),
                NextCursor = Record.FieldOrDefault(FirstItem, "nextCursor", null),

                // Handles empty response and extracts data points when present
                FinalItemsList =
                    if Table.HasColumns(Response, "datapoints") then
                        let
                            // Clean up the response table
                            ColumnsPresent = List.Intersect({Table.ColumnNames(Response), RemovedFields}),
                            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 not List.IsEmpty(RequestedPerState) then {"stateAggregates"}
                                            else if aggregates <> null then AggregatesTrimmedList
                                            else if FirstItem[type] = "state" then {"numericValue", "stringValue"}
                                            else {"value"}),
                                        // Expand each data point 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, FirstItem},

        // 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 the first page, which also describes the time series, then any remaining pages
        FirstPage = FetchPage(null),
        AllItems = if FirstPage{1} <> null then AccumulateData(FirstPage{1}, FirstPage{0}) else FirstPage{0},

        // The response describes the time series even without data points
        SeriesItem = FirstPage{2},
        SeriesType = SeriesItem[type],
        SeriesFields = List.Difference(Record.FieldNames(SeriesItem), RemovedFields & {"instanceId", "isString", "datapoints"}),

        // The time series type decides the shape and type of its values
        StateTypes = (prefix as text) => {{prefix & "numericValue", Int64.Type}, {prefix & "stringValue", type text}},
        ValueTypes =
            if aggregates = null then
                (if SeriesType = "state" then StateTypes("")
                else {{"value", if SeriesType = "string" then type text else type number}})
            else if not List.IsEmpty(RequestedPerState) then
                StateTypes("") & List.Transform(RequestedPerState, each {_, type number})
            else
                List.Combine(List.Transform(AggregatesTrimmedList, (name) =>
                    if SeriesType = "numeric" or not List.Contains(SeriesValueAggregates, name) then {{name, type number}}
                    else if SeriesType = "state" then StateTypes(name & ".")
                    else {{name, type text}})),

        // Every result has these columns, including a time range with no data points
        TextColumns = {"instanceId.space", "instanceId.externalId"} & SeriesFields,
        OutputColumns = TextColumns & {"timestamp"} & List.Transform(ValueTypes, each _{0}),

        // 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) 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(_)}),
                    // Give each state its own row, without the buckets that report no states
                    WithStateRows =
                        if List.IsEmpty(RequestedPerState) then ParseTimestamp
                        else
                            let
                                Rows = Table.ExpandListColumn(ParseTimestamp, "stateAggregates"),
                                Expanded = Table.ExpandRecordColumn(Rows, "stateAggregates", {"numericValue", "stringValue"} & RequestedPerState)
                            in Table.SelectRows(Expanded, each [numericValue] <> null),
                    // Split interpolated states into their code and label
                    SeriesValueColumns = if aggregates <> null then List.Intersect({AggregatesTrimmedList, SeriesValueAggregates}) else {},
                    WithStateValues =
                        if SeriesType = "state" then
                            List.Accumulate(SeriesValueColumns, WithStateRows, (table, name) =>
                                Table.ExpandRecordColumn(table, name, {"numericValue", "stringValue"}, {name & ".numericValue", name & ".stringValue"}))
                        else WithStateRows,
                    // Expand the instance ID so each row identifies its time series
                    WithInstanceId = Table.ExpandRecordColumn(WithStateValues, "instanceId", {"space", "externalId"}, {"instanceId.space", "instanceId.externalId"})
                in Table.SelectColumns(WithInstanceId, OutputColumns)
            else #table(OutputColumns, {}),

        // Type the columns the same way, with or without data points
        TypedTable = Table.TransformColumnTypes(ExpandedTable,
            List.Transform(TextColumns, each {_, type text}) & {{"timestamp", type datetimezone}} & ValueTypes)
    in
        if fillLimit <> null and (aggregates = null or granularity = null) then
            error "fillLimit requires aggregates and granularity."
        else if not List.IsEmpty(RequestedPerState) and List.Count(RequestedPerState) < List.Count(AggregatesTrimmedList) then
            error "RetrieveDataPoints returns one row per state for stateCount, stateDuration, and stateTransitions, so it can't combine them with other aggregates. Request them in a separate call."
        else TypedTable

RetrieveDataPointsMultipleTs function

Fetch multiple time series datapoints with PostCDF

(
    items 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(
        items,
        each RetrieveDataPoints(
            _,
            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(
        [ space = "my_space", externalId = "my_time_series" ],
        #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(
        [ space = "my_space", externalId = "my_time_series" ],
        #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 see how long a state time series spends in each state per day:

let
    Source = RetrieveDataPoints(
        [ space = "my_space", externalId = "my_state_time_series" ],
        #datetimezone(2024, 10, 1, 0, 0, 0, 2, 0),
        #datetimezone(2024, 10, 13, 10, 0, 0, 2, 0),
        "stateDuration",
        "1d"
    )
in
    Source

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

let
    Source = RetrieveDataPointsMultipleTs(
        {
            [ space = "my_space", externalId = "my_time_series_1" ],
            [ space = "my_space", externalId = "my_time_series_2" ]
        },
        #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, see Immutable and mutable streams before you set up refresh.

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, pass RangeStart and RangeEnd, and keep the row filter so Power BI recognizes the date limits:

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

All reads from a stream share its read throughput limit, even when Power BI loads several partitions at once. Use the properties parameter to fetch only the properties your report needs. To check whether the first refresh fits, see Plan the first refresh.

Immutable and mutable streams

A record is identified by its space and externalId. How you handle the loaded rows depends on the stream type:

Stream type	What the query returns	What to keep in the report
Immutable	Stored versions, with `status = "created"`	Every version you need to report on. Several versions can share the same `space`, `externalId`, and `lastUpdatedTime`, so don’t deduplicate them.
Mutable	The latest row for each matching record, with `status` set to `created`, `updated`, or `deleted`	The latest row for each record, excluding records whose latest row has `status = "deleted"`.

A row with status = "deleted" is a tombstone. It can still contain the record’s previous property values, so always check status. For a mutable stream with a full refresh, each record appears once, so removing the tombstones in Power Query is enough:

Exclude deleted records (full refresh)

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

For a mutable stream with incremental refresh, older partitions can still hold earlier versions of a record. Use the DAX in Handle updates and deletions with space and externalId as the key and status as the deletion field. RetrieveRecords reads from the sync endpoint, which returns tombstones for at least three days after a record is deleted. Other record endpoints usually stop returning the deleted record within a few seconds. See Delete records in the Records API reference. Keep the reload period in the policy shorter than three days, with time for failed refreshes to retry.

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 instances from the DMS query endpoint with the PostCDF function

​Fetch time series datapoints with the PostCDF function

​Output columns by time series type

​Fetch records from a stream with the PostCDF function

​Immutable and mutable streams

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 instances from the DMS query endpoint with the `PostCDF` function

Fetch time series datapoints with the `PostCDF` function

Output columns by time series type

Fetch records from a stream with the `PostCDF` function

Immutable and mutable streams