JSON Explained Simply (for Excel People)

📎 This article includes 1 downloadable practice file ↓

Almost every web service and API sends data as JSON. It looks scary but follows only a few rules.

{
  "invoice": "INV-1042",
  "date": "2019-05-14",
  "paid": true,
  "customer": { "name": "Asha Traders", "city": "Pune" },
  "lines": [
    { "item": "Keyboard", "qty": 2, "price": 1299 },
    { "item": "Mouse", "qty": 5, "price": 599 }
  ]
}

• { } an object: named fields (like one row with column names).
• [ ] an array: a list (like several rows).
• Values are text in quotes, numbers, true/false, null, or more objects/arrays.

From JSON to a table

The lines array becomes two rows; the invoice fields repeat on each. That “flattening” is exactly what Power Query does.

Load JSON in Excel

Data → Get Data → From File → From JSON (or From Web for an API URL). In Power Query, click To Table, then the expand buttons (↔) on record and list columns until you have plain columns.

…or with Python

import json, pandas as pd
data = json.load(open("invoice.json"))
df = pd.json_normalize(data, record_path="lines", meta=["invoice", "date", ["customer", "name"]])
df.to_excel("invoice.xlsx", index=False)