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 email@example.com!
This document is designed to help our partners visualize their data in Excel by converting our API's JSON output into an Excel table. This should ease the adoption of using our API to gather diverse datasets for your specific data analytics and business informatics needs. We will provide Step-by-step illustrations of the process to support you in importing JSON files from our API into Excel via Power Query. Power Query can also be used to easily import our API's JSON files into datasets for Power BI and similar data visualization tools.
What is a JSON file?
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 2: 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 3: Adjust Power Query Settings and Create Excel Table from JSON Data
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 JSON file, 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