Unpacking Nested JSON and XML Structures in Power Query: Expanding Lists, Records, and Tables

Introduction

You've connected to an API endpoint, loaded the response into Power Query, and you're staring at a column full of cells that say [Record] or [List]. Or maybe you're pulling XML from a vendor's SFTP drop and every row contains a nested tree of elements that somehow needs to become flat, queryable rows. Either way, you know the data is in there — you just can't get to it.

Nested structures are the rule in modern data exchange, not the exception. REST APIs return JSON objects where order details live inside customer objects, which live inside invoice objects. XML configuration files wrap attributes inside elements inside parent elements. Power Query can handle all of this natively, but the expand-and-drill workflow isn't always obvious, and making a wrong assumption about structure can silently drop rows or create a Cartesian explosion that multiplies your row count by hundreds.

By the end of this lesson, you'll be able to open any nested JSON or XML payload in Power Query, understand what you're looking at, expand it correctly, and write clean M code that doesn't break when the structure changes slightly. You'll also know when not to expand — and when to use structured navigation instead.

What you'll learn:

• How Power Query represents JSON Lists, Records, and Tables — and how those concepts map to XML elements and attributes
• How to expand nested Records into columns without losing rows
• How to expand Lists (one-to-many relationships) and understand the row multiplication that follows
• How to handle multi-level nesting by chaining expand operations
• How to use Table.TransformColumns, List.Transform, and custom functions to reshape nested data without fully expanding it
• How to navigate and flatten XML structures, including attributes, repeated elements, and mixed content

---

Prerequisites

This lesson assumes you're comfortable with:

• Opening Power Query Editor and working with the Applied Steps pane
• Basic M syntax — you've written or at least read a let...in expression
• The concept of data types in Power Query (text, number, list, record, table)
• Loading data from at least one source (Excel, API, file)

If you've never expanded a column in Power Query before, spend 15 minutes on the basics first. If you've done it a few times but it still feels like guesswork, you're in the right place.

---

How Power Query Thinks About Nested Data

Before touching a button, you need a mental model. Power Query represents structured data using three container types, and understanding the difference between them determines everything about how you expand.

A Record is a named collection of key-value pairs — analogous to a JSON object {} or a single XML element with child elements. It has a fixed set of fields. When a cell shows [Record], it means that cell contains an entire structured object. Expanding a Record column adds new columns to your table — one column per field. Your row count stays the same.

A List is an ordered collection of values — analogous to a JSON array [] or a repeated XML element. It has no named keys, just positions. When a cell shows [List], it means that cell contains zero or more values. Expanding a List column multiplies your rows — each value in the list gets its own row. This is intentional; it's how you model one-to-many relationships.

A Table is what Power Query creates when it encounters a List of Records — the most common case in API responses. When a cell shows [Table], it already has rows and columns. Expanding it merges those rows into your outer table, which again multiplies row count.

This distinction matters because the UI presents all three with similar-looking expand buttons, but the outcomes are fundamentally different.

Key insight: JSON arrays become Lists. JSON objects become Records. A JSON array of objects becomes a Table. XML elements become Records; repeated sibling elements become Lists or Tables. Hold that model in your head as you work.

---

Working with JSON: A Real-World API Payload

Let's use a realistic example. Suppose you're pulling order data from an e-commerce API. The JSON response looks like this:

[
  {
    "order_id": "ORD-1001",
    "customer": {
      "id": "CUST-445",
      "name": "Hartwell Industries",
      "tier": "enterprise"
    },
    "order_date": "2024-03-15",
    "status": "shipped",
    "line_items": [
      {"sku": "PRD-88", "description": "Thermal Relay Module", "qty": 4, "unit_price": 129.99},
      {"sku": "PRD-92", "description": "Mounting Bracket Kit", "qty": 10, "unit_price": 18.50}
    ],
    "shipping": {
      "carrier": "FedEx",
      "tracking": "794644792798",
      "address": {
        "street": "1200 Commerce Blvd",
        "city": "Columbus",
        "state": "OH",
        "zip": "43215"
      }
    }
  },
  {
    "order_id": "ORD-1002",
    "customer": {
      "id": "CUST-217",
      "name": "Meridian Group",
      "tier": "standard"
    },
    "order_date": "2024-03-16",
    "status": "processing",
    "line_items": [
      {"sku": "PRD-55", "description": "Control Panel Assembly", "qty": 1, "unit_price": 849.00}
    ],
    "shipping": {
      "carrier": "UPS",
      "tracking": null,
      "address": {
        "street": "88 Pine Street",
        "city": "Portland",
        "state": "OR",
        "zip": "97201"
      }
    }
  }
]

Load this into Power Query using Get Data > From Web (pointing to your API) or From Text/CSV with a JSON file. Power Query will recognize the structure and load it as a table. But what you'll actually see at first is likely a single column called Column1 full of [Record] values.

Step 1: Understanding What You Have

Before clicking Expand, always inspect the structure. Click on one of the [Record] cells — not a column header, the actual cell. The Preview pane at the bottom of the editor shows the fields of that specific record. You'll see order_id, customer, order_date, status, line_items, and shipping.

This tells you the outer layer is a List of Records, which Power Query has already converted to a table. Now you know the shape before committing to an expand.

In the M code, your initial query probably looks something like:

let
    Source = Json.Document(File.Contents("C:\data\orders.json")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

Table.FromList has wrapped each top-level object into a row in Column1. Each cell value is a Record.

Step 2: Expanding the Outer Record Layer

Click the expand icon in the Column1 header — it looks like two arrows pointing away from each other. You'll get a dialog listing the fields Power Query detected: order_id, customer, order_date, status, line_items, shipping.

Select all of them. Uncheck "Use original column name as prefix" unless you actually want Column1.order_id everywhere (you don't). Click OK.

Your table now has six columns. Four of them — order_id, order_date, status — contain plain scalar values. But customer, line_items, and shipping still show structured types: [Record], [List], and [Record] respectively.

The M step generated looks like:

#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", 
    {"order_id", "customer", "order_date", "status", "line_items", "shipping"}, 
    {"order_id", "customer", "order_date", "status", "line_items", "shipping"})

Table.ExpandRecordColumn takes the source table, the column name to expand, the list of fields to include, and the list of output column names. This is the function to reach for when writing expand logic programmatically.

Warning: If you're expanding records from an API that might add new fields over time, the UI-generated step hardcodes only the fields visible at the time you clicked. New fields in the API response will be silently dropped. We'll deal with this in the dynamic expansion section later.

Step 3: Expanding Nested Records (customer and shipping)

customer is a Record with three fields: id, name, tier. Expand it the same way — click its expand icon, select the fields you want. The M function is identical: Table.ExpandRecordColumn.

shipping is a Record with carrier, tracking, and address. But address is itself a nested Record. Expand shipping first to get carrier, tracking, and address. You'll see that address is still [Record]. Expand that too, selecting street, city, state, zip.

This chaining — expand, get another nested type, expand again — is completely normal. Two or three levels deep is common. Five levels is not unheard of with deeply nested APIs. Each expand operation adds one Applied Step.

After expanding both customer and shipping fully, your table has plain scalar values in every customer and shipping column. Row count is still 2 — no multiplication has happened yet because Records always expand horizontally.

Step 4: Expanding the line_items List — Where Rows Multiply

Now for the interesting part. line_items shows [List] in each cell. Each order has multiple line items, and you need a row per line item. This is the one-to-many relationship.

Click the expand icon on line_items. Because this is a List of Records (not a List of scalars), Power Query converts it to a Table first. The expand dialog shows columns: sku, description, qty, unit_price. Select all four.

After expanding, ORD-1001 produces 2 rows (one per line item), and ORD-1002 produces 1 row. Your table now has 3 rows instead of 2. Every other column — order_id, customer_name, shipping_carrier, etc. — is repeated for each line item row. This is correct and expected.

The generated M code uses Table.ExpandTableColumn:

#"Expanded line_items" = Table.ExpandTableColumn(#"Expanded shipping_address", "line_items", 
    {"sku", "description", "qty", "unit_price"}, 
    {"line_item_sku", "line_item_description", "line_item_qty", "line_item_unit_price"})

Notice the fourth argument — you can rename columns as you expand. This is the right moment to prefix them meaningfully (e.g., line_item_sku) so they're readable in the final model.

Performance note: Expanding a List column triggers a row-level operation. For large datasets, this can be slow. If you have 100,000 orders with an average of 8 line items each, you're creating 800,000 rows. That's fine — it's the right structure — but be aware of the scale before you build downstream joins on this table.

---

Handling Lists of Scalars (Not Records)

Not every List contains Records. Sometimes an API returns a JSON array of simple values:

{
  "product_id": "PRD-88",
  "name": "Thermal Relay Module",
  "tags": ["electrical", "relay", "industrial", "UL-listed"]
}

When you expand tags, you don't get a Table — you get individual scalar values. The expand icon on a List-of-scalars column shows "Expand to New Rows" instead of a column selector dialog. Clicking it turns ["electrical", "relay", "industrial", "UL-listed"] into four rows, one per tag.

If you instead want to keep one row and just concatenate the tags, use Extract Values from the expand dropdown, which calls Text.Combine on the list. Or write it directly:

#"Tags as Text" = Table.TransformColumns(Source, {
    {"tags", each Text.Combine(List.Transform(_, Text.From), ", "), type text}
})

This keeps your row count flat and produces "electrical, relay, industrial, UL-listed" as a single text value. Use this approach when the list is a property of the entity (like tags) rather than a child entity (like line items).

---

Dynamic Expansion: Not Breaking When the API Changes

The UI-generated expand steps hardcode column names. When the API adds a field — say, customer.loyalty_points — your query ignores it silently. Here's how to make expansion dynamic.

Dynamic Record Expansion

let
    Source = Json.Document(Web.Contents("https://api.example.com/orders")),
    ToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    
    // Dynamically discover all customer fields
    CustomerFields = List.Union(List.Transform(ToTable[Column1], 
        each if _ is record then Record.FieldNames([customer]) else {}
    )),
    
    ExpandedOuter = Table.ExpandRecordColumn(ToTable, "Column1",
        Record.FieldNames(ToTable{0}[Column1]),
        Record.FieldNames(ToTable{0}[Column1])
    ),
    
    ExpandedCustomer = Table.ExpandRecordColumn(ExpandedOuter, "customer",
        CustomerFields, 
        List.Transform(CustomerFields, each "customer_" & _)
    )
in
    ExpandedCustomer

The key technique: Record.FieldNames(ToTable{0}[Column1]) reads the field names from the first record at runtime rather than hardcoding them. This makes the query adaptive to schema changes.

Caution: Dynamic expansion using the first row assumes all records have the same schema. If field presence varies row by row (common in real APIs with optional fields), use List.Union across all records to build a complete field list. Table.ExpandRecordColumn will fill missing fields with null.

Expanding with Missing Fields (Null Safety)

When some records are missing a field entirely, Table.ExpandRecordColumn handles this correctly — missing fields become null. But if you're navigating into nested records directly with the record[field] syntax and the field doesn't exist, you'll get an error. Use Record.FieldOrDefault instead:

// Unsafe — crashes if "tier" doesn't exist in some customer records
CustomerTier = Source[customer][tier]

// Safe — returns null if field is absent
CustomerTier = Record.FieldOrDefault(Source[customer], "tier", null)

---

Custom Functions for Repeating Expand Patterns

When your JSON has the same nested structure repeated across multiple queries (common when you have a consistent API schema), build a custom function rather than repeating the expand steps.

Here's a function that takes any table with a customer Record column and fully expands it:

let
    ExpandCustomer = (InputTable as table) as table =>
    let
        Expanded = Table.ExpandRecordColumn(InputTable, "customer",
            {"id", "name", "tier"},
            {"customer_id", "customer_name", "customer_tier"}
        )
    in
        Expanded
in
    ExpandCustomer

Store this as a query named fnExpandCustomer. Then in any query that has a customer column:

#"Customer Expanded" = fnExpandCustomer(#"Previous Step")

For more complex scenarios, write a recursive function that drills into arbitrarily deep nested Records:

let
    FlattenRecord = (r as record, prefix as text) as record =>
    let
        Fields = Record.FieldNames(r),
        Transformed = List.Transform(Fields, each 
            let 
                FieldValue = Record.Field(r, _),
                FieldName = if prefix = "" then _ else prefix & "_" & _
            in
                if FieldValue is record 
                then Record.ToList(FlattenRecord(FieldValue, FieldName))
                else {FieldName, FieldValue}
        )
    in
        Record.FromList(List.Transform(Transformed, each _{1}), 
                        List.Transform(Transformed, each _{0}))
in
    FlattenRecord

This is a sketch of recursive flattening — production implementations need more careful handling of lists within nested records. But the pattern is useful for configuration data and other deeply nested but predictable structures.

---

Working with XML: Elements, Attributes, and Namespaces

XML parsing in Power Query works differently from JSON — not harder, just different. Load an XML file with Xml.Document or Xml.Tables. The difference matters:

• Xml.Tables is the easy mode: Power Query tries to interpret the XML as tabular data automatically. It works well for simple structures.
• Xml.Document gives you the raw XML hierarchy as nested Records and Lists. Use this when Xml.Tables produces confusing results or when you need attributes.

A Realistic XML Example

Suppose you're receiving purchase order XML from an ERP system:

<?xml version="1.0" encoding="UTF-8"?>
<PurchaseOrders>
  <Order id="PO-5501" status="approved">
    <Vendor>
      <VendorID>V-112</VendorID>
      <Name>Strathmore Fabrication</Name>
      <Country>US</Country>
    </Vendor>
    <OrderDate>2024-03-10</OrderDate>
    <Lines>
      <Line lineNum="1">
        <PartNumber>MFG-4400</PartNumber>
        <Description>Stainless Flange 4-inch</Description>
        <Quantity>50</Quantity>
        <UnitCost>22.75</UnitCost>
      </Line>
      <Line lineNum="2">
        <PartNumber>MFG-4401</PartNumber>
        <Description>Gasket Set</Description>
        <Quantity>100</Quantity>
        <UnitCost>4.20</UnitCost>
      </Line>
    </Lines>
  </Order>
</PurchaseOrders>

Load this with Xml.Document:

let
    Source = Xml.Document(File.Contents("C:\data\purchase_orders.xml"))
in
    Source

What you get back is a Record representing the root element. In the formula bar you can navigate it. The structure Power Query creates uses specific field names:

• Name — the element name
• Value — the element's text content or a table of child elements
• Attributes — a Record containing XML attributes
• NamespaceUri — the namespace, if present

Navigating the XML Record Structure

Click into the Source record. You'll see PurchaseOrders as the root. Its Value field contains a table of Order elements. Let's navigate step by step in M:

let
    Source = Xml.Document(File.Contents("C:\data\purchase_orders.xml")),
    
    // Root element value is a table of child elements
    RootValue = Source[Value],
    
    // Filter to just Order elements (ignores whitespace text nodes)
    Orders = Table.SelectRows(RootValue, each [Name] = "Order"),
    
    // The Attributes column holds id and status
    ExpandAttributes = Table.ExpandRecordColumn(Orders, "Attributes", 
        {"id", "status"}, {"order_id", "order_status"}),
    
    // Value contains the child elements table for each Order
    ExpandValue = Table.ExpandTableColumn(ExpandAttributes, "Value",
        {"Name", "Value", "Attributes"})
in
    ExpandValue

This gives you rows for each child element of each Order (Vendor, OrderDate, Lines), with the parent's order_id and order_status repeated.

The XML gotcha everyone hits: XML attributes and element text content are in different places. Attributes live in the Attributes Record. Text content lives in Value. If your element has both attributes and text content, the Value field is the text, not a child table. Power Query is consistent about this, but it trips up anyone coming from an XPath mindset.

Extracting XML Attributes

The lineNum attribute on <Line> is in the Attributes Record. After navigating to the Line-level rows:

// Expand Attributes to get lineNum
ExpandLineAttributes = Table.ExpandRecordColumn(LineRows, "Attributes",
    {"lineNum"}, {"line_number"})

Handling XML Namespaces

If your XML uses namespaces (common with enterprise XML like SOAP responses, UBL invoices, or HL7 segments), element names include the namespace prefix or URI. Power Query stores the namespace URI in the NamespaceUri field of each element row.

Filter elements using both the Name and NamespaceUri fields:

// For XML with namespace like xmlns:po="http://schemas.example.com/po/2024"
OrderRows = Table.SelectRows(RootValue, each 
    [Name] = "Order" and 
    [NamespaceUri] = "http://schemas.example.com/po/2024"
)

If the namespace prefix changes but the URI stays the same (which is standards-compliant), this approach is robust.

---

Building a Full XML Flattening Query

Let's put the XML example together end-to-end, producing a flat table of order line items with all parent context:

let
    Source = Xml.Document(File.Contents("C:\data\purchase_orders.xml")),
    
    // Get root element children
    RootChildren = Source[Value],
    OrderElements = Table.SelectRows(RootChildren, each [Name] = "Order"),
    
    // Extract order-level attributes
    WithOrderAttribs = Table.ExpandRecordColumn(OrderElements, "Attributes",
        {"id", "status"}, {"order_id", "order_status"}),
    
    // Expand the Value column to get child elements of Order
    OrderChildren = Table.ExpandTableColumn(WithOrderAttribs, "Value",
        {"Name", "Value", "Attributes"}, {"child_name", "child_value", "child_attribs"}),
    
    // Keep only the Lines element rows — we'll handle Vendor separately
    LinesRows = Table.SelectRows(OrderChildren, each [child_name] = "Lines"),
    
    // child_value for Lines is a table of Line elements
    ExpandLines = Table.ExpandTableColumn(LinesRows, "child_value",
        {"Name", "Value", "Attributes"}, {"line_name", "line_value", "line_attribs"}),
    
    LineItemRows = Table.SelectRows(ExpandLines, each [line_name] = "Line"),
    
    // Get lineNum from Line attributes
    WithLineNum = Table.ExpandRecordColumn(LineItemRows, "line_attribs",
        {"lineNum"}, {"line_number"}),
    
    // line_value is a table of PartNumber, Description, Quantity, UnitCost elements
    ExpandLineFields = Table.ExpandTableColumn(WithLineNum, "line_value",
        {"Name", "Value"}, {"field_name", "field_value"}),
    
    // Pivot the field_name / field_value pairs into columns
    Pivoted = Table.Pivot(ExpandLineFields, 
        List.Distinct(ExpandLineFields[field_name]), 
        "field_name", "field_value"),
    
    // Clean up intermediate columns
    FinalTable = Table.SelectColumns(Pivoted,
        {"order_id", "order_status", "line_number", 
         "PartNumber", "Description", "Quantity", "UnitCost"}),
    
    // Rename to conventions
    Renamed = Table.RenameColumns(FinalTable, {
        {"PartNumber", "part_number"},
        {"Description", "description"},
        {"Quantity", "quantity"},
        {"UnitCost", "unit_cost"}
    }),
    
    // Fix types
    TypedTable = Table.TransformColumnTypes(Renamed, {
        {"quantity", Int64.Type},
        {"unit_cost", type number}
    })
in
    TypedTable

This produces a clean, flat table with one row per line item, order context on every row, and proper types. The Table.Pivot step in the middle is the key to converting the name/value structure that XML naturally produces into proper columns.

---

Hands-On Exercise

Scenario: You've been given a JSON file from your company's project management system. Each project has multiple team members assigned, and each team member has a role and a list of completed tasks with hours logged. You need to build a flat table showing: project_id, project_name, member_name, role, task_title, hours_logged.

Setup: Create a file called projects.json with this content:

[
  {
    "project_id": "PROJ-201",
    "project_name": "ERP Integration Phase 2",
    "team": [
      {
        "member_id": "EMP-44",
        "name": "Sandra Kowalski",
        "role": "Lead Analyst",
        "completed_tasks": [
          {"task_title": "Requirements mapping", "hours": 12},
          {"task_title": "Vendor coordination", "hours": 8}
        ]
      },
      {
        "member_id": "EMP-67",
        "name": "Devin Okafor",
        "role": "Developer",
        "completed_tasks": [
          {"task_title": "API connector build", "hours": 24},
          {"task_title": "Unit testing", "hours": 6},
          {"task_title": "Documentation", "hours": 4}
        ]
      }
    ]
  },
  {
    "project_id": "PROJ-202",
    "project_name": "Dashboard Refresh",
    "team": [
      {
        "member_id": "EMP-12",
        "name": "Priya Nair",
        "role": "BI Developer",
        "completed_tasks": [
          {"task_title": "Mockup design", "hours": 5},
          {"task_title": "Power BI build", "hours": 18}
        ]
      }
    ]
  }
]

Your tasks:

1. Load the file into Power Query. Identify what type each cell in the initial table represents before expanding anything.

2. Expand the outer Records to get project_id, project_name, and team as columns. Note your row count.

3. Expand the team List. Observe how row count changes. Explain why project_id and project_name are repeated.

4. Expand the team member Records to get member_id, name, role, and completed_tasks. Keep the prefix team_ for member fields to distinguish them from project fields.

5. Expand the completed_tasks List. Note the final row count and verify it makes sense (5 tasks for PROJ-201 + 2 tasks for PROJ-202 = 7 rows).

6. Remove team_member_id from the final output and rename columns to match the target schema: project_id, project_name, member_name, role, task_title, hours_logged.

7. Bonus: Write the complete M query from scratch without using the UI expand buttons. Use Table.ExpandRecordColumn and Table.ExpandTableColumn directly.

Expected final state: 7 rows, 6 columns. If you have more rows, check whether you accidentally expanded a List twice. If you have fewer, check whether a filter step dropped nulls.

---

Common Mistakes & Troubleshooting

Mistake 1: Expanding When You Should Navigate

If you only need one field from a nested Record, expanding creates a lot of unnecessary columns that you then delete. Navigate directly instead:

// Creates 3 new columns, you delete 2
ExpandedCustomer = Table.ExpandRecordColumn(Source, "customer", 
    {"id", "name", "tier"})
// Then remove id and tier...

// Better: just extract the one field you need
CustomerName = Table.AddColumn(Source, "customer_name", 
    each [customer][name], type text)

Direct navigation with [field] syntax is clean and self-documenting when you want one or two values from a nested Record.

Mistake 2: Losing Rows on List Expansion

If you expand a List column and your row count doesn't go up when you expect it to, check whether Power Query silently filtered out null or empty lists. It doesn't by default — but if a previous step accidentally typed the column as something other than List, the expand may have failed silently.

Check with: Table.SelectRows(Source, each [list_column] = null or [list_column] = {}) to find the empty cases.

If you expand a List and your row count drops — meaning orders with no line items disappear — that's correct behavior. An empty list produces no rows. If you need to keep those parent records (orders with zero line items), add a row before expanding:

// Replace empty lists with a single null-valued record list
SafeLineItems = Table.TransformColumns(Source, {
    {"line_items", each if List.IsEmpty(_) then {[sku=null, description=null, qty=null, unit_price=null]} else _}
})

Mistake 3: Hardcoded Column Lists Breaking on Schema Changes

As discussed — use Record.FieldNames to dynamically read schema at runtime. For production queries against APIs you don't control, this is non-negotiable.

Mistake 4: XML Text Nodes and Whitespace

Xml.Document returns every node, including whitespace text nodes between elements. These show up as rows where Name is "#text" and Value is whitespace. Always filter them out:

NoWhitespace = Table.SelectRows(XmlTable, each [Name] <> "#text")

Forgetting this is the #1 cause of confusing row counts and null columns in XML processing.

Mistake 5: Pivoting Before All Expansions Are Complete

The Table.Pivot step that converts name/value XML pairs into columns requires that the column you're pivoting on contains only the values you expect. If you run pivot with leftover #text nodes or unexpected child elements still in the Name column, you'll get extra columns full of nulls, or the pivot may error with "there were duplicate values" if the same element name appears twice at the same level.

Always inspect List.Distinct(Table[field_name]) before pivoting to confirm the values are clean.

Mistake 6: Confusing List and Table Expansion

When a column contains [List] and you click Expand, Power Query first checks whether it's a List of Records (becomes a Table expand with column selection) or a List of scalars (becomes "expand to new rows" with no column dialog). If you see a column picker dialog, you're in Table territory. If you see just "Expand to New Rows," it's scalar values.

If you expected a Table but got "Expand to New Rows," your List contains something unexpected — maybe strings that should be objects, meaning the JSON source may have a schema issue.

---

Summary & Next Steps

Nested JSON and XML aren't obstacles — they're just structured data that needs a few deliberate steps to flatten. The core workflow is always the same: identify what type each nested column is (Record, List, or Table), choose the right expansion approach for the relationship it represents, and chain expansions level by level until you have a flat structure.

Records expand horizontally — more columns, same rows. Lists expand vertically — more rows, same width. That's the rule that governs everything.

For production use, the most important habits are: inspect structure before expanding, use dynamic field name discovery when schemas might change, handle missing fields with null-safe access patterns, and clean XML text nodes before pivoting.

Where to go next:

• Custom connectors and parameterized APIs: Once you can handle any JSON structure, the next step is building reusable queries that accept parameters (page numbers, date ranges, filter IDs) and call APIs in loops using List.Generate or recursive functions.
• Error handling in nested expansions: Production pipelines need try...otherwise wrappers around record navigation to handle malformed records gracefully without failing the entire refresh.
• Combining multiple nested sources: Real-world models often join the flat line items table you just built to a separate customers table, products table, and exchange rates table — all from different sources with different nesting depths. The query folding and performance optimization considerations for these joins are a full topic on their own.
• Power Query's Table.FromRecords and Table.ToRecords: These round-trip functions are essential when you need to manipulate data at the record level before converting back to a table — useful for complex reshaping that's hard to express column-by-column.

The ability to reliably unpack nested structures is what separates Power Query practitioners who work with real-world data from those who only process clean spreadsheets. You now have the foundation and the vocabulary to handle whatever the API sends you.