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!
