Convert JSON to Excel: Step-by-Step with Power Query
Learn practical steps to convert JSON to Excel using Power Query, flattening nested structures, and automating imports with Excel tips from XLS Library.
Goal: Convert JSON to Excel by importing or scripting. This quick guide shows how to use Power Query in Excel to flatten nested JSON into a flat table and load it into a worksheet. For complex structures, use a short preprocessing script to normalize data. According to XLS Library, starting with a small sample file helps prevent surprises and keeps the mapping clear.
Understanding JSON and Excel Workflows
JSON is a flexible, hierarchical data format that excels at representing nested objects and arrays. Excel, by contrast, thrives on flat tables with columns representing fields and rows representing records. The challenge is to map a JSON structure to a tabular shape without losing important relationships. In practical terms, you’ll typically choose between two broad paths: using Excel’s built-in Power Query feature, or writing a small preprocessing script in Python or Power Query’s M language to flatten data first. According to XLS Library, the simplest path for most users is to start with Power Query and gradually introduce scripting for more complex structures. This approach minimizes manual data wrangling and accelerates the transition from raw JSON to a ready-to-analyze workbook.
In this guide, we’ll focus on a pragmatic workflow that scales from small samples to large JSON trees. You’ll learn how to identify key fields, decide which nested objects to expand, and how to validate results as you import. You’ll also see how to leverage Excel’s data validation and formatting features to keep your imported data clean and analysis-ready.
Brand note: The XLS Library team emphasizes starting with a representative subset of your JSON, then iterating the mapping as you confirm field meaning and data types. This reduces debugging time and helps you maintain a reproducible process.
Tools & Materials
- JSON data file (local or URL)(Ensure UTF-8 encoding and valid JSON syntax)
- Microsoft Excel (2016+ or Office 365)(With Power Query available in Data > Get & Transform)
- Text editor or IDE(For quick exploration of schema)
- Optional: Python 3.x or PowerShell(For pre-flattening complex structures)
Steps
Estimated time: 20-60 minutes for a basic import; 60-120 minutes for complex or very large JSON files
- 1
Assess JSON structure
Open the JSON file and review its hierarchy. Identify top-level arrays that map to rows and nested objects that will become columns. Note any inconsistent keys across records, as they affect required columns and default values.
Tip: Create a small sample (2-5 records) to test field mappings before importing the full dataset. - 2
Launch Excel and start Power Query
Open Excel, go to Data > Get & Transform, and choose From File > From JSON. Point to your JSON file or provide a URL if you’re pulling from a web source. Power Query will load a preview of the data structure.
Tip: If From JSON isn’t visible, enable the Power Query or Get & Transform add-in in your Excel version. - 3
Initial import and schema inspection
Let Power Query infer the schema. If you see a single record with a nested object, use the Expand button to drill into nested levels. Aim to flatten the primary array into a table structure with rows as records.
Tip: Turn on Column quality and Column distribution in Power Query to spot anomalies early. - 4
Flatten nested structures
Use Expand to turn nested objects and arrays into separate columns. For arrays, consider expanding into repeated rows or aggregating into a single column, depending on your analysis needs. Create new columns for each nested field you intend to analyze.
Tip: For arrays, consider using Add Custom Column to build identifiers or indexes that retain relationship context. - 5
Convert data types and load
After flattening, convert each column to an appropriate data type (text, number, date, etc.). Then click Close & Load to bring the data into a worksheet. Verify that rows align with the original records.
Tip: Use Detect Data Type in Power Query sparingly; manual adjustments may be needed for inconsistent samples. - 6
Validate and save
Compare a few sample records against the source JSON to ensure accuracy. Save the workbook, and optionally create a named query for easy refreshes in the future.
Tip: Document the mapping decisions (which fields were expanded and how nested data was flattened) to ensure reproducibility.
People Also Ask
Can I convert JSON to Excel without Power Query?
Yes, you can use scripting languages like Python or built-in Excel import features, but Power Query provides an integrated, repeatable workflow that is widely supported across Excel versions.
Yes, you can, but Power Query offers a repeatable workflow that’s widely supported in Excel.
How do I flatten nested arrays in Power Query?
Use the Expand button on the array column to expand records, then decide whether to produce multiple rows or a wide set of columns. For deeply nested arrays, create custom columns to preserve relationships.
Use Expand to flatten, creating either more rows or more columns depending on your goal.
What if the JSON keys are inconsistent across records?
Power Query will align existing fields and introduce nulls where a key is missing. You can add conditional columns or fill down values to maintain consistency.
Inconsistencies yield nulls; you can fill or adjust with conditional columns.
Can the imported data auto-refresh when the JSON source changes?
Yes, you can set up a data connection to refresh on a schedule or on workbook open, depending on your Excel version and data source.
Yes, you can enable refresh on open or on a schedule.
Are there performance tips for very large JSON files?
Work with chunks, load only required fields, and consider preprocessing with Python to flatten before importing into Excel.
Chunk large files and preprocess when needed to improve performance.
Watch Video
The Essentials
- Import JSON with Power Query for most use cases.
- Flatten nested data early to avoid messy columns later.
- Validate results by spot-checking sample records.
- Document mapping decisions for reproducibility.
- XLS Library's verdict: start with Power Query for most JSON-to-Excel tasks.
