How to Import JSON into Excel Using Power Query (Step-by-Step)

Power Query is Microsoft Excel's data import and transformation engine. Available in Excel 2016 and later (including Microsoft 365), it can connect to JSON files, web APIs, databases, and dozens of other data sources. For JSON imports, Power Query handles the parsing and provides a visual interface for expanding nested structures.

This tutorial covers the complete process using Excel for Windows. The steps are similar in Excel for Mac, though some menu locations may differ slightly.

Prerequisites

• Microsoft Excel 2016 or later (or Microsoft 365)
• A JSON file saved on your computer (with a .json extension)

Step 1: Open the JSON File in Power Query

1. Open Excel and create a new blank workbook (or open an existing one).
2. Go to the Data tab in the ribbon.
3. Click Get Data → From File → From JSON.
4. In the file browser, navigate to your JSON file and click Import.

Power Query opens in a new window, showing a preview of your data. What you see depends on your JSON structure:

• Array at root level → Power Query shows a list of records
• Object at root level → Power Query shows key-value pairs

Step 2: Convert to Table

If Power Query shows a List (which happens when your JSON root is an array), you need to convert it to a table:

1. Click the "To Table" button in the toolbar (or right-click → To Table).
2. In the dialog that appears, accept the default settings and click OK.
3. You should now see a single column called "Column1" with "Record" entries.

Step 3: Expand Record Columns

This is the most important step. Each "Record" in the table represents one JSON object. You need to expand it into individual columns:

1. Click the expand icon (two small arrows pointing outward) in the column header.
2. A dialog shows all available fields. Select the fields you want as columns.
3. Uncheck "Use original column name as prefix" if you want cleaner column names.
4. Click OK.

Your table now has one column per JSON field. If any of those fields contain nested objects, you will see "Record" again — repeat the expand process for those columns.

Step 4: Handle Nested Objects

For JSON data with multiple levels of nesting, you may need to expand several times. Each expansion drills one level deeper into the object hierarchy.

Example: Two-Level Nesting

Consider this JSON:

[
  {
    "name": "Sarah",
    "address": {
      "city": "New York",
      "zip": "10001"
    }
  }
]

First expansion creates columns: name, address (shows "Record").

Second expansion on the address column creates: name, address.city, address.zip.

Step 5: Handle Arrays

When a JSON field contains an array, Power Query shows "List" in the cell. You have two options:

Option A: Expand to Rows

Click the expand icon and choose "Expand to New Rows". This creates one row per array element, duplicating the parent data (similar to SQL's CROSS APPLY or LATERAL JOIN).

Option B: Extract Values

Right-click the column → Extract Values. Choose a delimiter (comma, semicolon, etc.) and Power Query joins all array elements into a single text value per cell.

Step 6: Set Data Types

Power Query auto-detects data types, but it is good practice to verify and correct them:

1. Click a column header to select the column.
2. In the Transform tab, click Data Type and choose the correct type.
3. Common corrections: dates detected as text, numbers detected as text (especially IDs with leading zeros).

Step 7: Load to Worksheet

1. Once your data looks correct in the preview, click Close & Load in the Home tab.
2. Power Query loads the transformed data into a new worksheet as an Excel Table.
3. The table automatically gets filtering arrows in the headers.

Refreshing Your Query

One of Power Query's biggest advantages is refreshable queries. If your source JSON file gets updated:

1. Go to the Data tab.
2. Click Refresh All (or right-click the table → Refresh).
3. Power Query re-reads the JSON file and applies all your transformations automatically.

This is invaluable for recurring reports — set up the query once, and refresh whenever you get new data.

Power Query vs Online Converters

Common Power Query Issues

"The input is not a valid JSON document"

This error means your JSON file has syntax errors. Common causes: trailing commas, single quotes instead of double quotes, or BOM (Byte Order Mark) characters at the beginning of the file. Validate your JSON before importing.

Columns show "Error"

Usually caused by data type mismatches. A column set to "Number" type but containing some text values will show errors for those rows. Change the column type to "Text" or handle the errors in Power Query's error handling step.

Performance with large files

Power Query loads the entire file into memory. For JSON files larger than 100MB, consider splitting the data into smaller files or using a scripting approach (Python with pandas) instead.