How to Call APIs in Alteryx

In this guide, I will walk through how to make a GET request in Alteryx using the Game of Thrones API. This is a free API that does not require authentication and has no usage limitations. Making it a perfect API for practice and learning.

Step 1: Text Input Tool

The Text Input Tool will allow you to add the API URL into the workflow, which will later be called upon by the Download Tool to make the request.

1. Create a text Input tool to build your API URL
2. Create a new column and name it ‘URL’
3. In the first row, enter the API endpoint

Step 2: Download Tool

The Download Tool is where the API call will be made. This will need to be configured to use the URL provided in the Text Input Tool.

1. Add the Download tool to your workflow
2. Connect it to the output of the Text Input Tool
3. Uncheck Data Connection Manager if it’s selected
4. Ensure that the URL field is set to URL (The created column within the Text Input)
5. Run the workflow

Step 3: Check if the API call was successful

After running the workflow, two new columns will be created: ‘DownloadData’ and ‘DownloadHeaders’. To verify if the API call was successful, check the DownloadHeaders column. A successful call will respond with a status code of 200 OK.

Here are some common HTTP response status codes you might encounter:

• 200 OK – The request was successful
• 204 No Content – The request was successful but there is no content to send in the response
• 400 Bad Request – The server could not understand the request due to invalid syntax
• 401 Unauthorized – Authentication is required or has failed
• 404 Not Found – Request resource or endpoint does not exist
• 429 Too Many Requests – You have exceeded the rate limit for API calls

If the call was successful, you should see JSON data within the DownloadData Column. The next step is to parse this data using the JSON Parse tool.

Step 4: JSON Parse

1. Add the JSON Parse tool to your workflow
2. Connect it to the output of the Download tool
3. In the Configuration pane, set the JSON field to ‘DownloadData’
4. Check the option ‘Output values into single string field’
5. Run the workflow

Once the workflow runs, you should see two new fields in your results:

• JSON_Name – This column contains the path or key hierarchy of each value in the JSON structure
• JSON_ValueString – This column contains the actual value associated with each JSON key

Now that you have the JSON paths in JSON_Name, you can use the Text to Columns tool to split them into separate fields.

Step 5: Text to Columns

1. Add the Text to Columns tool onto the canvas
2. Connect it to the output of the JSON Parse tool
3. Within the Configuration Pane:
   • Column to split: JSON_Name
   • Delimiters: . (Full Stop)
   • Split to Columns
   • Number of columns: Depends how deeply nested the JSON is. For this example, there are only two so the number of columns is set to two
4. Run the workflow

There will now be multiple fields that represent each level of the JSON structure (JSON_Name1, JSON_Name2). We now want to reshape the data so that each key becomes a column through the use of the Cross Tab Tool.

Step 6: Pivot Data with the Cross Tab Tool

1. Add the Cross Tab tool onto the canvas
2. Connect it to the output of the Text to Columns tool
3. Within the Configuration Pane:
   • Group data by: Choose a unique identifier. In this example, JSON_Name1 has been selected as it contains the character ID
   • Change Column Headers: JSON_Name2
   • Values for new columns: Select JSON_ValueString
   • Method for aggregating values: Select Concatenate
4. Run the workflow

At this stage, you should now have a structured table where each JSON key is a column, and each row represents a complete record from the API. The final steps involve general data cleaning such as removing, renaming and restructuring columns

Step 7: Select Tool 

1. Add the Select Tool onto the canvas
2. Connect it to the output of the Cross Tab Tool
3. Within the Configuration Pane:
   • Uncheck any columns that are no longer needed
   • Rename columns for clarity
   • Reorder the columns to match your preferred structure

You should now have clean, structured data from the API call, ready for analysis or further transformation.

Optional:

Add a Sort Tool after the select tool to sort the data by an appropriate field such as ID.