Aggregating Nested JSON Data in Excel Using Power Query M Code

Wrangling deeply nested JSON payloads in Excel often leads to severe performance bottlenecks and broken queries. While securing standard funding sources for enterprise-grade database tools remains the traditional route for integration projects, departments must frequently deliver results using existing resources. Fortunately, leveraging customized Power Query M code grants analysts the unique ability to flatten complex data structures locally.

A key stipulation of this approach is that M code requires precise schema mapping to prevent data truncation. For example, applying Table.ExpandRecordColumn to extract nested customer_metadata keys ensures absolute data integrity. Below, we outline the exact step-by-step formulas to automate your nested JSON aggregation.

In modern data analytics, JSON (JavaScript Object Notation) has emerged as the de facto standard for transmitting structured data over the web. Whether you are consuming data from a REST API, pulling reports from a modern SaaS platform, or extracting logs from cloud databases, you will inevitably encounter nested JSON arrays.

While Excel is an incredibly powerful tool for tabular data, handling multi-layered, nested JSON structures within traditional grid formulas is highly inefficient, if not impossible. Standard cell formulas simply lack the recursion and structural awareness required to clean, flatten, and aggregate deeply nested hierarchical objects. Fortunately, Excel's built-in data transformation engine, Power Query, powered by the M Formula Language, provides an incredibly elegant, robust, and scalable way to parse and aggregate nested JSON files.

This guide will walk you through the process of writing an optimized Excel Power Query M Code solution to extract, flatten, and dynamically aggregate nested JSON data into clean, analysis-ready tables.

The Problem: Nested JSON Structuring

To understand the mechanics, let's consider a common scenario. Imagine you have an export of order transactions containing nested lists of items, prices, and quantities. Below is a sample representation of this JSON payload:

[
  {
    "order_id": "ORD-9901",
    "customer": "Aria Montgomery",
    "region": "North",
    "items": [
      {"product": "Wireless Mouse", "price": 25.00, "quantity": 2},
      {"product": "Mechanical Keyboard", "price": 85.00, "quantity": 1}
    ]
  },
  {
    "order_id": "ORD-9902",
    "customer": "Caleb Rivers",
    "region": "West",
    "items": [
      {"product": "USB-C Hub", "price": 45.00, "quantity": 1},
      {"product": "4K Monitor", "price": 320.00, "quantity": 2},
      {"product": "HDMI Cable", "price": 12.50, "quantity": 3}
    ]
  }
]

If you import this directly, Excel will see two primary rows (representing the two orders), but the items column will contain a nested List of Records. Our objective is to calculate the total order value and total items purchased per order, flattening the nested structural layers and aggregating the calculations natively inside Power Query before loading it to our spreadsheet grid.

Step-by-Step Implementation with Power Query

Step 1: Importing the JSON Source

To begin importing your JSON data into Power Query:

• Navigate to the Data tab in the Excel Ribbon.
• Select Get Data > From File > From JSON.
• Select your local JSON file and click Import.

Power Query will automatically load the JSON file and convert it into a list of record objects inside the Power Query Editor interface.

Step 2: Transforming the Source into a Query Table

Once imported, the initial query will display a list of records. To work with the nested records systematically, we must convert this structure into an operational table:

• In the Power Query Editor, click on the To Table button in the top-left corner of the Transform tab.
• In the pop-up dialog, leave the defaults (no delimiter, handle extra values as error) and click OK.

Step 3: Flattening the Parent Records

After converting to a table, your column (typically named Column1) contains the main objects. Click the Expand icon (two opposing arrows) in the column header to expose the top-level keys: order_id, customer, region, and items.

• Uncheck "Use original column name as prefix" to keep your headers clean.
• Click OK.

At this stage, you will see a clean tabular format for the order info, but the items column will still show nested [List] entries. This is where M code excels at programmatic manipulation.

Using M Code to Aggregate Nested Items Dynamically

To process and aggregate the data, we have two paths: manually parsing through the UI, or writing clean, performant, and maintainable M Code within the Advanced Editor. The manual GUI route creates highly verbose steps that can cause slow query refreshes with larger datasets. Writing customized M Code allows us to load, calculate, and roll up the nested objects efficiently.

Open the Advanced Editor under the Home tab to view and modify your Power Query script. Replace the existing script with the optimized script below:

let
    // Load the raw JSON file source
    Source = Json.Document(File.Contents("C:\YourPathToData\orders.json")),
    
    // Convert the raw JSON records array to a relational table
    TableFromSource = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    
    // Expand top-level record keys
    ExpandedOrderRecords = Table.ExpandRecordColumn(
        TableFromSource, 
        "Column1", 
        {"order_id", "customer", "region", "items"}, 
        {"order_id", "customer", "region", "items"}
    ),
    
    // Step 1: Expand the nested List structure into individual row records
    ExpandedItemsList = Table.ExpandListColumn(ExpandedOrderRecords, "items"),
    
    // Step 2: Expand the fields of the nested item record objects
    ExpandedItemDetails = Table.ExpandRecordColumn(
        ExpandedItemsList, 
        "items", 
        {"product", "price", "quantity"}, 
        {"Product Name", "Price", "Quantity"}
    ),
    
    // Step 3: Add a calculated column for Line Total (Price * Quantity)
    CalculatedLineTotal = Table.AddColumn(
        ExpandedItemDetails, 
        "Line Total", 
        each [Price] * [Quantity], 
        type number
    ),
    
    // Step 4: Aggregate the flattened items table back to order level
    AggregatedOrders = Table.Group(
        CalculatedLineTotal, 
        {"order_id", "customer", "region"}, 
        {
            {"Total Spend", each List.Sum([Line Total]), type nullable number},
            {"Total Items Ordered", each List.Sum([Quantity]), type nullable number},
            {"Unique Products", each List.Count(List.Distinct([Product Name])), type nullable number}
        }
    )
in
    AggregatedOrders

Deep Dive: Explaining the Key M Functions

To fully master nesting and data shape transformations in Excel, you must understand the key M functions executed in this script:

1. Table.ExpandListColumn

The items column contains list structures embedded inside table rows. Table.ExpandListColumn functions similarly to a relational JOIN or Cartesian product. It duplicates the parent rows (e.g., ORD-9901) for every single item entry stored inside the nested list, cleanly transitioning your data from 1-to-many into single, manageable relational rows.

2. Table.ExpandRecordColumn

Once the list is expanded, the column contains individual record objects. A record in M code is equivalent to a dictionary or single row of key-value pairs. Table.ExpandRecordColumn extracts selected keys (like product, price, and quantity) and turns them into structural columns, while assigning native data types.

3. Table.Group

After we dynamically calculate the line cost for each purchased item using a simple custom column multiplication, Table.Group performs our SQL-like GROUP BY aggregation. Rather than returning thousands of individual product item rows to Excel, we aggregate the values back to the order-level grain. It sums up the total financial spend, counts the total quantity of shipped items, and determines how many unique, distinct products were included in each transaction.

The Resulting Table in Excel

When you click Close & Apply in Power Query, Excel compiles the M Code steps, connects to the data source, processes the nested JSON hierarchy in memory, and writes a neat, aggregate table to your spreadsheet:

Best Practices for Processing Large JSON Datasets in Excel

When working with large, complex JSON schemas containing tens of thousands of nested rows, keep these best practices in mind to optimize execution speeds:

• Perform Filtering Early: If you only need to process specific regions or active orders, use Table.SelectRows before expanding the nested lists. Expanding arrays is computationally expensive; filtering out records beforehand reduces memory usage.
• Disable Privacy Settings if Appropriate: When connecting to both local JSON files and external REST APIs, cross-boundary data checks can slow processing down. Adjust your Power Query Privacy Levels to "Organizational" or "Public" if your internal data policies permit, which bypasses defensive parsing steps.
• Avoid Formula-Level Overhead: Do not rely on native Excel grid formulas (like SUMIFS or VLOOKUP) on top of the loaded output. Complete all your aggregation, calculations, and grouping inside Power Query using M Code so Excel only has to render the final results.

Conclusion

Manually translating nested JSON payloads is tedious, fragile, and prone to errors. Excel's Power Query engine transforms this workflow. By leveraging functions like Table.ExpandListColumn, Table.ExpandRecordColumn, and Table.Group inside customized M scripts, you can seamlessly connect to complex nested data sources and transform them into aggregated, clean reporting datasets. Master these M code methods to unlock data orchestration capabilities directly inside your everyday spreadsheets.