- Open API
- Introduction to Automation Hub API
- Generate your Token from Automation Hub
- Input for Automation Pipeline
- Input for Submission Type
- Input for Phase
- Input for Status
- Input for Phase and Status Update
- Input for Business Unit
- Input for Applications
- Input for Category
- Input for Cost Benefit Analysis
- Input for High Level Assessment
- Input for Detailed Assessment
- Input for Automation Idea Creation Date
- Input for Users
- Input for User Status
- User Status Table
- Input for Collaborators
- Output Dictionary
- Automation Hub Open API Power Query Data Parsing
- Automation Hub Custom Connector
Automation Hub Open API Power Query Data Parsing
Parsing the data from the Automation Hub API can sometimes prove to be challenging, especially if you are consolidating a very complex report.
In this page, we are presenting a couple of tips and tricks that can be used to improve the overall data parsing process. The page contains:
- The steps with references to the Open API and Power Query documentation.
- A sample file where you can exercise the steps directly in the Power Query.
Use the following steps to set up the main query:
- Go to Power BI or Excel and press Get Data - then select From Web.
- In the new window, press Advanced.
- Here, you need to add the correspondent Open API data for the connection.
- Start by adding the endpoint (for example, GET Automation Pipeline).
- Then the headers
- In the headers, add the Authentication token.
- Make sure to add the word Bearer before inserting the token's value.
- Press Connect.
You should now get the JSON response from the Open API in Power BI. Further, you need to dig into the data before saving the table by using the following steps:
- Expand the data object.
- Then expand the process's object.
- Now, you can convert the data to a table, which will result in a single column with all the records.
- Expand that column to get all the available records in the JSON file - you can choose to get all of them or just part of them.
- After you have done the expansion, be sure to check all the columns because some of them have nested records that you need to expand.
- Go through each column until you ensure that the data is ok. You can also format some of the columns as you wish.
- If your data looks good, press Save and Close and wait for the data to load.
- You can now start building your dashboard/report.
Start from a simple query where you do not use any limit parameter, and by default, you will get a response with the default number of items (20) and proceed with the steps below:
- Drill down into the totalPages field.
- Click on the Function button, and your formula should change to =totalPages.
- Insert the following formula
=List.Numbers(1,totalPages,1)
. - A list is generated with all the page numbers available in the API response.
This list is dynamic based on the number of pages it gets from the response. So if you will refresh the API query and you have new data (a new page), it will automatically get it in the list. Continue by using the following steps:
- Transform the list into a table.
- Rename the column to Page and change the data type to Text.
- Create a custom column.
- Add in the column formula the following query:
let
Source = Json.Document(Web.Contents(" https://cloud.uipath.com/{orgName}/{tenantName}/automationhub_/api/v1/openapi/automationpipeline?pre=all&page="&[Page], [Headers=[#"Content-Type"="application/json", #"x-ah-openapi-app-key"="test", #"x-ah-openapi-auth"="openapi-token", Authorization="Bearer xxxxxx"]])),
data = Source[data],
processes = data[processes]
in
processes
let
Source = Json.Document(Web.Contents(" https://cloud.uipath.com/{orgName}/{tenantName}/automationhub_/api/v1/openapi/automationpipeline?pre=all&page="&[Page], [Headers=[#"Content-Type"="application/json", #"x-ah-openapi-app-key"="test", #"x-ah-openapi-auth"="openapi-token", Authorization="Bearer xxxxxx"]])),
data = Source[data],
processes = data[processes]
in
processes
The above query gets the data from the Automation Pipeline and uses the Page parameter (which we get dynamically in the list) to get the content for all available pages. 5. Click ok and expand the column. You now have all the available items from the response, regardless of how many pages the API returns.
Application parsing (delimited by comma) is done using the following steps:
- In the query editor, go to the column where you have your applications and press expand to new rows.
- Press again on expand and select the application metadata you want to extract, such as the application name.
- In the Home Tab/Ribbon, click on Group by.
-
In the group by window, select:
- Grouping to be done by the Process ID column, as the ID is unique.
- New column name should be something like "applications aggregated".
- Operation: Sum
- Column: The column that you have expanded for the applications (in the above example, it is Column.1applications.applications_name).
- After pressing ok, you will get an error as the Sum function cannot work with text entries.
- Go to the formula bar and replace List.Sum with Text.Combine as functions
-
Also, in the formula, after the applications column name, add a delimiter for your text entries – for example ", "
This is how the formula looks before and after the change:
Before change
After change
= Table.Group(#"Expanded Column1.applications1", {"Column1.process_id
= Table.Group(#"Expanded Column1.applications1", {"Column1.process_id
- Press save and have a look at the column entries:
Same steps as for description, but since the description is a text field where we need to separate the different paragraphs, we will use a different separator in the formula “#(lf)”.
Before change |
After change |
---|---|
|
|
Parsing a Different Query (GET Automations) to Extract Custom Questions/High Level Assessment Questions
Start from a new query for GET Automations (you can use the one with the pagination showcased above) and follow the steps below:
-
Expand the following columns:
- Process ID
- Advanced_Infromation or High Level Assessment (both work the same way, but for this tutorial, we will work with the advanced_information)
- Expand again to get the questions fields.
-
Expand again and get the following fields.
- Question_text
- Answer_text
- Question_response_answers
- Expand Question_response_answers and extract response_choice_text .
- Combine the Answer Text and Response_choice_text columns into one single column using the formula
if [AnswerText] is null then [AnswerChoice.response_choice_text] else [AnswerText]
.
This is always applicable as you can have either a text answer or a response choice (never both). Continue the process by using the steps below:
- Check and remove any null entries from the Question column.
- Use a pivot column to transpose all the current data from the Question and Answer columns into rows.
- Select the Question column and click on Pivot column from the Transform menu.
- Select the answer column as your values column
- Select Don’t aggregate as an Aggregate Value Function and click OK.
All ideas now have a corresponding column representing the custom questions and their corresponding values.
To merge two different queries into Power BI, look at how this is generally done in Power Query. Afterward, you can start with the Automation Pipeline query and merge it with the Get Automations query.
- This way, you will expand the available columns from Get Automation Pipeline with the ones from Get Automations.
- You need to use the Process ID to merge the queries, as it is both the unique identifier and common key between all the queries.
Below, you can see a video demonstration of the steps described in this article.
In this video:
07:18 - Parsing a different query (GET Automations) to extract the Description and Application fields
The sample file used in the video can be downloaded from here.
- Setting up the Main Query (for GET Automation Pipeline)
- Using Pagination to Dynamically Get All Items From Any Open API Request
- Parsing a Different Query (GET Automations) to Extract the Description and Application Fields
- Description Parsing (delimited by Paragraph)
- Parsing a Different Query (GET Automations) to Extract Custom Questions/High Level Assessment Questions
- Merging Queries (from GET Automations) to Your Main Query (GET Automation Pipeline)
- Automation Hub API Power Query Parsing Demo