Power QueryExercises

Nested ecommerce JSON flattening

Fernhill Ceramics (fictional)

Flatten paged order exports from a shop API (customers, line items, discount lists, optional shipping) into a line table and an order summary.

Level
advanced
Time
~70 min
Deliverables
2
Rubric
v1

Access

Pro. This brief is public. The data pack, workspace, hints, grading and solution explanations need Pro, Lifetime Learning or a seat in an organization. See plans.

Each deliverable's solution explanation unlocks when it passes. You can also unlock it early once you have used every hint for that deliverable (this needs an account so the help is recorded, and the deliverable then counts as completed with help).

Business goal

Give the owner a line-level sales table with net amounts after discounts and an order summary that matches what customers actually paid, for every page the shop API returns.

Fernhill sells handmade ceramics online. The shop platform exports orders through an API that returns pages of JSON; a small script saves each page as a file in one folder. Each order holds the customer (with an address only if one was given), a list of lines, each line's list of discount codes, and a shipping object that is null for click-and-collect. Refunded orders must not count.

Graded deliverables

  1. 1. Order Lines

    55 points

    One row per order line with customer country, product collection, discount and net amount.

    • Columns: OrderID, OrderDate, CustomerID, Country, SKU, Collection, Qty, UnitPrice, Discount, NetAmount
    • Every page file included; refunded orders excluded
    • Discount 0 when there are no discounts
    • Country null only when the customer has no address
    Expected output schema for Order Lines
    ColumnType
    OrderIDtext
    OrderDatedate
    CustomerIDtext
    Countrytext or null
    SKUtext
    Collectiontext
    Qtywhole number
    UnitPricedecimal
    Discountdecimal
    NetAmountdecimal
    Grain:
    One row per line of a paid or pending order.
    Order:
    Not graded.
    Nulls:
    Country is null when the customer has no address. Nothing else is null.
  2. 2. Order Summary

    45 points

    One row per non-refunded order with line count, net total, shipping and order total — including orders with no lines.

    Builds on: Order Lines

    • Columns: OrderID, OrderDate, Country, Lines, NetTotal, ShippingFee, OrderTotal
    • Every non-refunded order, including ones without lines
    • ShippingFee 0 for click-and-collect
    • Sorted by OrderID
    Expected output schema for Order Summary
    ColumnType
    OrderIDtext
    OrderDatedate
    Countrytext or null
    Lineswhole number
    NetTotaldecimal
    ShippingFeedecimal
    OrderTotaldecimal
    Grain:
    One row per paid or pending order.
    Order:
    Graded: OrderID ascending.
    Nulls:
    Country can be null; Lines, NetTotal and ShippingFee are 0 rather than null.
Checking your access…