How to connect Power Query (Power BI) to Orchestrator API ?
This KB article 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. As 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 create a role called API that has read-only access as following :
- Then create a local account (Non-AD account) and assign it the API role.
Of course, any local account that has a password can be used( Do not use an AD authenticated account). But creating a special account for the API access, with just the permissions required would be best practices.
Once the account is created, remember to login at least once to change the password, as it is required to change the password after the first login. Post the password change, take note of the new password for the next steps.
Power Query: Post the local account creation and having reset the password, 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 Reference guide. A query needs to be crafted that retrieves the bearer token and then passes the bearer token to the API request.
- To do that, first 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:
- Click Done
- Next, create a record to store the credential information. Create a new blank query and name it Credentials. In the Advanced Editor type in:
- Obtain the client Id and refresh them from the cloud Orchestrator instance. 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/yoursiteURL). 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.
- Copy the values clicking the button next to each of these fields.
Authenticate URL - https://account.uipath.com
- Replace the Path variable with any API call as documented in the Orchestrator Reference guide. There is no need to modify other lines, as long as you setup the BaseUrl and Credentials queries as specified above.
- 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, you should 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.