How To Connect Power Query (Power BI) To Cloud Orchestrator API

News Knowledge Base
1

How to connect Power Query (Power BI) to Cloud Orchestrator API ?

To connect Power Query to the Cloud Orchestrator API, it is possible to use data analytics tools such as Power BI to create reports and dashboards, using data from Orchestrator.

This tutorial will show how to connect to the Orchestrator API data source.

Setup the External application to access API:

Create an external application to obtain the App ID and App Secret. Ensure the correct scope is provided for the API to authorize access.

Store the client id and client credentials for further use.


Power Query: Post registering the external application, it is now time to use Power Query to authenticate and connect to the data source. For the screenshots, Power Query will be used that is built into Excel 2016. However, the queries are exactly same as in Power BI Desktop.

Authenticating: Authenticating to the API requires the use of a bearer token as documented in the Orchestrator API guide. A query needs to be crafted that retrieves the bearer token and then passes the bearer token to the API request.

  1. Click on New Source -> Other Sources -> Blank Query. The query can go like:-
    let
    // Base URL

    // URL-encoded token request parameters
    TokenContent = Text.ToBinary(
    "client_id=" &
    "&client_secret=" &
    "&grant_type=client_credentials" &
    "&scope="
    ),
    // Requesting the authentication token
    AuthResponse = Json.Document(Web.Contents(BaseUrl & "/identity_/connect/token", [
    Headers = [#"Content-Type" = "application/x-www-form-urlencoded"],
    Content = TokenContent
    ])),
    // Extract token from the response
    Token = AuthResponse[access_token],

    // Set the path for the job data
    Path = "///orchestrator_/odata/Jobs",

    // Make the final API call using the Bearer token for authorization
    Source = Json.Document(Web.Contents(BaseUrl, [
    Headers = [Accept = "application/json", #"Authorization" = "Bearer " & Token],
    RelativePath = Path
    ]))
    in
    Source

(Replace client_id, client_secret and scope from UiPath Automation Cloud -> External application)

2. At this point, a pop-up message asking you to specify how to connect may be encountered. Click on Edit Credentials and then select Anonymous_ access. Then another popup might be obtained about data privacy. Depending on your use case, you may select Public or Organizational.


If above suggested steps are performed correctly, see the results as something like this:

At this point, expand the list, convert it to a table, and perform any other Power BI data transformation to get the data required.

For example:

image
image1072×799 32 KB

2 Likes
2

The images for setting the Credentials are not loading. Can you reupload those?

1 Like
3

The images for setting the credentials are not visible, can someone send them to us?

1 Like
4

A post was split to a new topic: How do I connect to Cloud Orchestrator?

5

The missing images are visible in this post: https://forum.uipath.com/t/tutorial-connect-power-query-power-bi-to-orchestrator-api/88654

6

2 posts were split to a new topic: Connect Power Query (Power BI) to Orchestrator API - authorization issue

7

I can’t seem to get this working. Would someone be able to provide a sample .pbix files to help get me rolling quicker?

1 Like