How to Flatten Nested JSON for Excel (Without 'Expand Record' Hell)
If you use Power BI or Excel to analyze data from APIs (like Stripe, Shopify, or Twitter), you know the pain of Nested JSON.
You import your file, and instead of data, you see a column of [Record], [List], [Record].
The "Power Query" Struggle
To get your data, you have to:
- Click the "Expand" icon.
- Uncheck "Use original column name as prefix".
- Click OK.
- Realize that one of those new columns is also a
[Record]. - Repeat steps 1-3 for every single nested object.
If your JSON is 10 levels deep, you are clicking "Expand" for 20 minutes. And if the API schema changes? Your query breaks.
The Better Way: One-Click Pre-Flattening
Why do the heavy lifting in Excel? The smarter workflow is to flatten the data before you import it.
jsonExport.com was built specifically to solve this "Power Query Fatigue".
How it Works
- Drop your JSON file.
- We automatically detect nested structures (objects inside objects).
- Our engine "flattens" them into Dot Notation (
customer.address.city) automatically. - Download as Excel.
When you open our file in Excel, there are no Records or Lists. Just clean, flat columns like:
orders.idorders.customer.emailorders.items.0.price
Comparison: Time to Analysis
| Task | Power Query Method | jsonExport Method |
|---|---|---|
| Setup Time | 10-20 mins (Manual Expansion) | 10 Seconds (Drag & Drop) |
| Schema Changes | Breaks Query steps | Auto-adapts |
| Mixed Arrays | Complex "Expand to New Rows" logic | Smart Flattening |
| Result | Frustration | Instant Analysis |
Stop Clicking "Expand"
You are a Data Analyst, not a "Data Expander". Let our tool handle the structural cleanup so you can focus on the actual insights.