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.

   

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.

   

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.

   

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.

   

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.
   

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.

   

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.

   

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.