How to Open a JSON File in Excel With Power Query

Why Double-Clicking a JSON File Doesn't Work

Excel opens spreadsheets, CSV and text files directly, but JSON is hierarchical: objects inside objects, arrays inside arrays. Excel needs instructions on which part becomes rows and how nested values become columns. Power Query is the feature that provides those instructions.

Step-by-Step: Import JSON With Power Query

These steps are for Excel for Microsoft 365 and Excel 2016 or later on Windows. Menu names are similar in recent Mac versions where Power Query is available.

1. Start the Import

Go to Data → Get Data → From File → From JSON, select your .json file and click Import. The Power Query editor opens.

2. Convert the Top Level to a Table

What you see depends on the JSON's outer shape:

• A list (the file starts with [): click To Table on the ribbon, accept the defaults, and you get one column named Column1, with one row per record.
• A record (the file starts with {): you see field names and values. Click the value of the field that holds your data (for example List next to data or results) to drill into it, then click To Table.

3. Expand the Records Into Columns

The column header now has an expand icon (two arrows pointing apart). Click it, choose which fields to include, and untick Use original column name as prefix if you want shorter headers. Click OK, and each field becomes a column.

4. Expand Nested Values

Nested objects show up as Record in a column; nested arrays as List.

• For a Record column, click the expand icon again to add its fields as columns.
• For a List column, the expand icon offers Expand to New Rows (one row per array item) or Extract Values (join items into one cell with a delimiter you choose).

Expanding a list to new rows duplicates the parent's other columns for each item, which affects totals. How to convert nested JSON to Excel explains when that's appropriate and when a separate sheet is better.

5. Set Data Types

Power Query guesses types, but check each column's icon in the header. Set IDs and codes with leading zeros to Text. Set dates explicitly; ISO strings like 2026-10-07T09:30:00Z may need Date/Time/Timezone.

6. Load the Data

Click Close & Load. The table appears on a new sheet. When the JSON file changes, right-click the table and choose Refresh.

Common Errors and Fixes

The "Load more" point catches many people: the expand dialog builds its field list from a sample, so fields that only appear in later records can be missed.

When Power Query Isn't the Best Option

Power Query is ideal for files you'll refresh. It's less convenient when:

• You only need a one-off spreadsheet and the JSON is deeply nested, so expanding level by level takes many clicks.
• You're on a version of Excel without Power Query JSON support, or using Google Sheets or Numbers.
• You're sending the spreadsheet to someone else, who doesn't need the query.

In those cases, convert the JSON to .xlsx first. An online JSON to Excel converter detects the record arrays, flattens nested objects, lets you choose how arrays are handled and writes a standard workbook that opens directly in Excel, Google Sheets, LibreOffice or Numbers. It runs in your browser, so the JSON isn't uploaded. For a full comparison of methods, see how to convert JSON to Excel.

Opening JSON From an API Instead of a File

Power Query's From Web connector can call an API URL and parse the JSON response with the same expand steps, giving you a table you can refresh on demand. How to export API JSON data to Excel covers authentication headers and pagination.

Summary

Use Get Data → From JSON, convert to a table, expand records and lists, set types, and load. For one-off or deeply nested files, converting to XLSX first is usually quicker.