2.3.3. Converting Semi-Structured Data to a Table

💡 First Principle: Semi-structured formats like JSON and XML nest data inside data: an order contains a customer object and a list of line items. A semantic model needs flat rows and columns. Converting means walking down the nesting levels and deciding, at each level, whether to spread a nested item into columns or into additional rows.

Power Query represents nested structures with three value types you'll see in cells:

• Record: a single set of named fields, like one customer object {name, city, tier}. Expanding a record column turns its fields into new columns on the same row.
• List: an ordered sequence of values or records, like an order's line items. Expand to New Rows creates one row per list item (duplicating the parent's other columns), while Extract Values joins list items into a single delimited text value.
• Table: a nested table, often produced by a merge or by Group By with All Rows. You can expand selected columns (one row per nested row) or aggregate them (sum, count) into the parent row.

A typical JSON workflow: connect with the JSON or Web connector; if the top level is a list, choose To Table (List Tools > Convert to Table); expand the resulting record column into fields; expand nested lists into new rows; then expand any records within those rows. Each expansion dialog lets you pick fields and toggle Use original column name as prefix, which you'll usually clear for readable names. XML files follow the same pattern, typically with nested Table values at each element level.

Two design decisions follow. First, decide the grain: expanding a line-items list to new rows changes the table's grain from one row per order to one row per order line, so order-level amounts (such as shipping charge) are now repeated and will over-count if summed. Second, consider splitting the result into two tables, Orders and OrderLines, each at its own grain, rather than forcing everything into one.

⚠️ Exam Trap: Expanding a list to new rows multiplies parent rows. If an order-level value like OrderTotal sits beside expanded line items, summing it double-counts. The fix is to separate order-level and line-level data into different tables (or aggregate lines back up), not to divide by a count in DAX.

Reflection Question: A REST API returns each customer as a record containing a nested list of addresses. When should you expand the list to new rows, and when would Extract Values (one delimited text field) be the better choice?