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.

Setting up the Main Query (for GET Automation Pipeline)

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.

Using Pagination to Dynamically Get All Items From Any Open API Request

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://{yourDomain}/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 
       processeslet 
       Source = Json.Document(Web.Contents(" https://{yourDomain}/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.

Parsing a Different Query (GET Automations) to Extract the Description and Application Fields

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:

Description Parsing (delimited by Paragraph)

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
`= Table.ExpandRecordColumn(#"Expanded Column1.process_description.blocks", "Column1.process_description.blocks", {"text`	`= Table.Group(#"Expanded Column1.process_description.blocks1", {"Column1.process_id`

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.

Merging Queries (from GET Automations) to Your Main Query (GET Automation Pipeline)

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.