Modern data analysis often requires ingesting information from web-based APIs, which predominantly deliver data in JavaScript Object Notation (JSON) format. Unlike traditional flat CSV files, JSON structures are hierarchical, frequently containing deeply nested arrays and complex objects. When you import raw JSON into Microsoft Excel, the initial view is often a single column containing inaccessible “Record” or “List” objects. To perform meaningful analysis, you must leverage Excel’s built-in Power Query engine to parse, expand, and flatten this nested architecture into a standard two-dimensional tabular format.
Connecting to the JSON API Source
The first step is establishing a connection between Excel and the external API. Open a new Excel workbook, navigate to the Data tab, click Get Data, select From Other Sources, and choose From Web. Input the full URL endpoint of the API you wish to query. If the API requires authentication (such as a Bearer token or API key), you can configure these credentials by selecting the Advanced option and manually defining the HTTP request headers.
Once connected, Excel will attempt to interpret the response. If the response is a standard JSON payload, Power Query will automatically launch and display the top-level structure, which usually appears as a single row containing a “Record” or a “List”.
Converting Lists to Tables
The most common JSON API structure returns a root object containing metadata (such as pagination details) alongside a nested array (a “List” in Power Query terminology) containing the actual data records. You must isolate this array and convert it into a table format that Power Query can manipulate.
If the root is a Record, click the hyperlinked “List” word next to the specific data array you wish to extract. Power Query will drill down into that specific list. Next, look at the Transform tab on the ribbon and click the To Table button. A prompt will appear asking how to handle delimiters; leave the default settings and click OK. The data will now appear as a single column containing “Record” values, representing individual JSON objects within the array.
Expanding Nested Records
With the list converted to a table of records, you can now flatten the data. Look at the column header containing the “Record” values. You will see a small icon with two diverging arrows. Clicking this icon opens the Expand menu.
This menu displays all the individual JSON keys contained within the record. Select the fields you wish to extract into separate columns. By default, Power Query will suggest using the original column name as a prefix (e.g., Column1.FirstName). It is usually best practice to uncheck the “Use original column name as prefix” option to keep your final column headers clean and readable. Click OK, and the nested fields will expand horizontally across the table.
Handling Deeply Nested Arrays
The complexity increases when a JSON object contains a nested array within itself (for example, a customer record containing an array of multiple order IDs). When you perform the first expansion, the column representing the orders will appear as a “List” rather than a text value.
To flatten this structure, click the diverging arrows icon on the List column header. You will be presented with two options: “Expand to New Rows” or “Extract Values”.
- Expand to New Rows: This is the most common requirement. It duplicates the parent record for every item in the nested array, effectively creating a one-to-many relationship within the flat table. If a customer has three orders, the customer’s details will now appear on three separate rows, each containing a different order ID.
- Extract Values: This option concatenates the array items into a single text string within the same cell, separated by a delimiter (such as a comma). This is useful if you simply want a summary list without altering the row count.
Finalising the Data Types
After all nested records and lists are fully expanded, review your columns. Because JSON does not strictly enforce data types in a way that Excel immediately understands, all columns will likely default to the “Any” data type. Select your columns, navigate to the Transform tab, and click Detect Data Type, or manually assign Date, Currency, and Whole Number types to ensure your data behaves correctly when loaded into pivot tables.
Finally, click Close & Load on the Home tab. The flattened, fully parsed JSON data will be injected into a standard Excel worksheet, ready for analysis.