How to 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.
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 to the API requires the use of a bearer token as documented in the Orchestrator Reference guide 44. 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:
Source = "https://cloud.uipath.com/Account Name"
Replace the URL with the URL for your Orchestrator instance. At the end, you should have a query that looks something like this:
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:
You can get the client Id and refresh toen from your cloud orchestrator instance. Please follow the below steps.
- Login to your Automation Cloud account.
- Navigate to Admin > Tenants. The Tenants page is displayed with a list of all existing tenants.
- Expand the desired tenant to display the available services.
- Click API Access () for the corresponding Orchestrator service. The API Access window is displayed with the following service-specific information:
- User Key - allows you to generate unique login keys to be used with APIs or with 3rd party applications in order to log in and perform actions on your behalf. This was previously known as your refresh token.
- Account Logical Name - your unique site URL, (for example cloud.uipath.com/
- ). Read more about it here.
- Tenant Name - the display name of the tenant.
- Client Id - specific to the Orchestrator application itself, is the same for all users and tenants on a specific platform. For example, all the tenants on cloud.uipath.com have the same Client Id value.
- Do not close this page. You need the information within it to make the authentication call.
- You can easily copy the values clicking the !(upload://oFgA47hiVSjEIje87wpPxfPIcyn.png)!(upload://oFgA47hiVSjEIje87wpPxfPIcyn.png) button next to each of these fields.
Authenticate URL - https://account.uipath.com
You can replace the Path variable with any API call as documented in the Orchestrator Reference 65 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.