HowTo>JSON API TUTORIAL:EXCEL

JSON API Tutorial: Excel

Pull your data into Excel using XML API


Import Data

1. Get data from web

In “Data” tab, click on “Get Data” → “From Other Sources” → “From Web”

screenshot of excel

2. Paste the API address in URL

screenshot of excel

3. Transform JSON dataset to Table

3-1. After the dataset in JSON format is loaded in the Power Query Editor, click To Table under the Transform tab.

screenshot of excel

3-2. Click OK in the pop-up window.

screenshot of excel

3-3. Click the expand icon under the Home tab to expand the column to a table.

screenshot of excel

3-4. Select all columns or selected columns, and click OK.

screenshot of excel

3-5. When the column is expanded to a table, click Close & Load.

screenshot of excel

4. View data

Your dataset should be imported and ready for use.

screenshot of excel