TUTORIAL: Connect Power Query (Power BI) to Orchestrator API

TUTORIAL: Connect Power Query (Power BI) to Orchestrator API

This tutorial will demonstrate how to connect Power Query to the Orchestrator API. This will allow you 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. Note that if you can connect directly to the SQL Server database, then you don’t need to use the API. The method detailed here is useful in situations where you cannot access the SQL Server database directly either due to firewall constraints or other access issues.

Setup the API account

You may want to consider creating a specific account for the API access. Because the password will be hard-coded in the query in a later step, it would be a good idea to have an account specifically for the api access. For example, I created a role called API that has read-only access:

Then I created a local account (non-AD account) and assigned it the API role.

Of course, you can use any local account that has a password (you cannot use an AD authenticated account). But creating a special account for the API access with just the permissions you need would be best practices.

Once you create the account, remember to login at least once to change the password, as you are required to change the password after the first login. Once you change the password, take note of the new password for the next steps.

Power Query

Once you have a local account and have the password, it is time to use Power Query to authenticate and connect to the data source. For the screenshots, I will be using Power Query 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 Reference guide. We will need to craft a query that retrieves the bearer token and then passes the bearer token to the API request.

To do that, first we will create two queries: one to store our base URL and one to store our credential information. Click on New Source -> Other Sources -> Blank Query. Rename this query as BaseUrl (note, Power Query is case-sensitive) then open the Advanced Editor. The query here will be simple:

let
    Source = "https://platform.uipath.com"
in
    Source

Replace the URL with the URL for your Orchestrator instance. At the end, you should have a query that looks something like this:

Click Done.

Next, we will create a record to store our credential information. Create a new blank query and name it Credentials. In the Advanced Editor type in:

let
    Source = [
        tenancyName = "Default",
        usernameOrEmailAddress = "api",
        password = "password"
    ]
in
    Source

You will change the values to match your tenant, username, and password.

Create API request

To make an API request, create a new blank query and use this basic template:

let
    Path = "/odata/Assets",

    Auth = Json.Document(Web.Contents(BaseUrl, [Headers=[#"Content-Type"="application/json"], Content=Json.FromValue(Credentials), RelativePath="/api/account/authenticate"])),
    Token = Auth[result],

    Source = Json.Document(Web.Contents(BaseUrl, [Headers=[Accept="application/json", #"Authorization"="Bearer " & Token], RelativePath=Path]))
in
    Source

You can replace the Path variable with any API call as documented in the Orchestrator Reference guide.The other lines you do not need to modify as long as you setup the BaseUrl and Credentials queries as specified above.

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

If you did everything correctly, you should see the results as something like this:

At this point, you can expand the list, convert it to a table, and perform any other Power BI data transformation to get the data you want. For example:

Conclusion

I hope you found this Power Query tutorial helpful. Please let me know if you have issues or questions!

6 Likes

Nice Job, I am sure this is going to help more people like us getting that live data. Hopefully from here we can build good dashborads for our analytics.

Thanks!

1 Like

This is awesome, thank you for sharing! Saving it for future reference, I know it will be needed soon :grinning:

1 Like

Thank you for this tutorial, this is really helpful!
The only issue I have at the moment is that when I publish the report to my workspace, it is not possible to update/refresh the data. The reason is that the baseURL and credentials query are both hidden in the report view. Do you know a workaround for this to be able to refresh it in the workspace without showing the BaseURL and credentials query?

Thanks!

The credentials have to be accessible in order to authenticate. Which is why I recommend setting up a separate read-only api user in Orchestrator.

Awesome job ! Thanks a lot !:grinning:

Really great tutorial! I am having an small isue in the last part, I get the following message:
Error: The underlying connection was closed. Could not establish trust relationship for SSL/TSL
Do you know can I solve this error ? It would help a lot! Apparently, it’s because the program sees the orchestrator as an unsecure connection.

My guess is your Orchestrator is using a self-signed certificate? If so, then you’ll need to make sure your computer (the computer trying to establish the API connection) trusts the SSL certificate.

Hope that helps!

Hi @oddrationale

Thank you for a great tutorial.
I followed your tutorial and come across a DataSourceError


Any tips?

There is a new way of authenticating to the Cloud API. The tutorial was built for a local installation of Orchestrator. I will need to update it with separate instructions for the Orchestrator CE Cloud API. But in the meantime, you can see the link and replicate the steps.

Thank you for replay.
I managed to connect to Orchestrator. My account was moved to the cloud. I used an old password to Orchestrator.
I really encourage you to publish new tutorials. There are really helpful. Thanks a lot.