Skip to main content

Power BI connector (REST API)

Use the Power BI REST API connector to fetch data with Cognite's OData services or other Cognite APIs using GET, POST, and with GraphQL queries for Cognite data models.

Beta

The features described in this section are currently in beta and are subject to change.

Connect to Cognite Data Fusion from Power Query Desktop​​

Follow these steps to connect a Cognite Data Fusion (CDF) project as a data source in Power BI.

  1. In Power BI Desktop, sign in with your organizational account.

  2. On the Home tab, select Get Data and then More. In the Get Data dialog, search for Cognite and select the Cognite Data Fusion (REST API) connector.

    Locate the CDF REST API connector in Power BI

  3. In the dialog box, enter details to configure the connection to CDF:

    • CDF project: Enter the name of the CDF project you're connecting to.

    • CDF organization: Enter the organization to use to sign in to CDF.

    Configure the CDF REST API connector in Power BI

  4. When prompted, sign in with your account credentials.

  5. Select Connect to open the Navigator window.

  6. Select the function(s) you want to use, and then select Transform Data to open the Power Query editor and retrieve data from CDF.

    Selecting functions using the REST API connector in Power BI

Connect to Cognite Data Fusion from Power Query Online​​

Follow these step to connect to CDF from Power Query Online.

  1. Select Cognite Data Fusion (REST API) under Get data. See Where to get data for finding Get data in Power Query Online.

    Screenshot of the get data window with Cognite Data Fusion (REST API) emphasized.

  2. In Connection settings, enter the following information:

    • CDF project: Enter the name of the CDF project you're connecting to.
    • CDF organization: Enter the organization to use to sign in to CDF.
    Screenshot of the Cognite Data Fusion (REST API) online connection settings.

  3. In Connection credentials, enter the following information:

    • If you're connecting to CDF for the first time, select Create new connection under Connection.

    • If you've connected previously, select an existing connection under Connection.

    • For Data gateway: If your organization uses a gateway to access CDF, select the appropriate gateway from the menu. If Data gateway is set to (none), the connector uses the Power BI service to connect to CDF directly.

    • You can also select Privacy Level for the data connection.

    • For new connections, select Sign in and follow the authentication process.

    Screenshot of the Cognite Data Fusion (REST API) online connection credentials.

  4. Select Next when you've signed in.

  5. In Choose data, select the function you require, then select Create to open the Power Query editor and retrieve data from CDF.

    Screenshot of the Power Query Online Navigator showing the available functions.

Filter data​

Use the maximum supported page size if you make requests that support pagination via limit parameters in GET/POST requests, or the first parameter in GraphQL queries. Smaller page sizes increase API calls and can significantly impact load times. See the Cognite API documentation for the maximum supported page size for each endpoint. For GraphQL queries, the maximum limit is 1000 instances per page.

When using the REST API functions, you can apply server-side filters to reduce the initial data loaded into Power BI.

If you're using OData, Power BI can push down some filters to the OData service when using the Power Query editor.

For GraphQL queries, you can apply filters directly in the query to reduce the amount of data loaded into Power BI. You can also use GraphQL query variables to parameterize your queries and make them more reusable.

About schema and type handling​

This is how schema and type information is handled when retrieving data using the different functions in the connector.

OData functions​

When fetching data with the ODataCDF or ODataDataModel functions:

  • Tables always include type information as the OData protocol includes metadata for every property.

  • Even if the result contains no data, for instance due to filtering or an empty resource, the returned table will have a defined schema with properly typed columns.

REST and GraphQL functions​

When fetching data with the GetCDF, PostCDF, or GraphQL functions, the outcome can vary:

Successful with data: Returns a table with columns based on the unique properties found in all records present in the API response. Column types are initially set as any since the REST API doesn't provide type metadata.

Successful but empty: Returns an empty table with no columns, since the API response doesn't include schema information when no data is present.

Failed request: Throws an error, similar to OData functions.

Best practices for REST functions​

Due to the variable schema behavior with REST functions, it's recommended to:

  • Manually define an expected schema with column names and types when using REST functions.

  • Apply type transformations to ensure consistent data types across refreshes.

  • Consider using a sample query to establish a schema template, then applying that schema even when results might be empty.

You can find examples and more information about schema handling for REST and GraphQL functions in incremental refresh with Power BI.

Scheduled refresh​

After publishing your report to the Power BI service, you can schedule data refreshes to keep your data current.

Error handling​

When writing custom GET and POST requests, you may get errors if you provide an invalid URL or body. If you receive a CogniteAPIError error message, see the Cognite API reference documentation and adjust your request.

Last updated