Purpose: 

Data accessibility is an important facet of the aMES and we aim to increase this accessibility by providing easy access to all of your data via our open API. If you have an Authentise account, you can easily access your data via our API and you are no longer limited to hardcoded, CSV exports. If you are having trouble getting to any of your data or would like guidance with our API, please reach out to support@authentise.com!

This document is designed to help our partners visualize their data in Excel and/or PowerBI by converting our API's output into tables using PowerQuery. This should ease the adoption of using our API to gather diverse datasets for your specific data analytics and business informatics needs. This can be done by saving the API information as a JSON file (Section 2A) or extracting the data to Excel directly from the web (Section 2B).

We will provide Step-by-step illustrations of the process to support you in importing data from our API into Excel via PowerQuery. PowerQuery can also be used to easily import our API's JSON files into datasets for PowerBI and similar data visualization tools. 

What is a JSON file?

JSON is a file format in Javascript. JSON structures data in an organized and easy-to-read manner. One of the primary uses is transmitting web application data to servers. Our API returns data as requested as JSON files. 

Section 1: Accessing an API Endpoint and obtaining a JSON file:

• Log in to Authentise. 

• In a separate tab, navigate to the desired API endpoint to request your desired data. For example, our flattened Order data can be found at https://data.authentise.com/flat-order/ 

• Right-click on the text and click Save as... to save the JSON file to your device. Some browsers may offer a dedicated save button when viewing JSON files in-browser.

Section 2A: Importing JSON files into Excel:

• Open Microsoft Excel

• On the ribbon, go to Data

• Click on Get Data.

• From the drop-down menu select From File

• Then select From JSON

• Select the JSON file that was downloaded in Section 1 and click Import

Section 2B: Importing data into Excel from Web link:

• Open Microsoft Excel

• On the ribbon, go to Data

• Click on Get Data.

• From the drop-down menu select From Other Sources

• Then select From Web

• Under Advanced, paste the API URL you'd like to reach, and set a timeout of at least 15 minutes: the timeout is added as a precaution for longer running queries

• Please use the Basic tab and use your Authentise credentials 

Section 3: Adjust Power Query Settings and Create Excel Table

Excel offers a diverse set of data manipulation capabilities via Power Query. This can automate data transformations and is a very powerful tool for data analytics and manipulation. For this guide, we will be using a very basic set of these data manipulation capabilities to get the data into a familar tabular format.

• After importing the data, the Power Query editor window will open.

• From the displayed links, click on Resources > List

• The list of resources will expand. 

• In the ribbon, click on To Table

• In the To Table window, keep the default settings (no changes) and click Next

• Expand Column1 by clicking on the Extension icon in the column's header

• A list of all column headers will display (leave all selected or uncheck the columns that you do not need to customize your data set)

• Select use original name as prefix (this uses the name for columns as represented in the API/JSON data)

• Click OK to load the preview table

• Click Load & Close to upload the data into a table within an Excel worksheet