Excel structured-data mapping

Convert Nested JSON to CSV in Excel With Power Query

Import JSON with Power Query, choose a row meaning before expanding nested lists, preserve identifiers as text and verify the worksheet before CSV export.

By Uddhav Gopani, FounderPublished 27 September 2026Updated 29 September 2026

Convert Nested JSON to CSV in Excel With Power Query

To convert JSON to CSV in Excel, import the JSON with Power Query, transform its records into a flat table, load the result to a worksheet and export that worksheet as CSV. The main decision is how nested objects and arrays become rows and columns. A successful import alone does not establish that the table preserves the intended relationships.

Choose the supported import path

In Excel for Windows with the JSON connector available, use Data, Get Data, From File, From JSON and select the source file. Microsoft documents this path in its Power Query import guide. Menus and connector availability depend on the Excel platform and version.

For Mac, check the Get Data experience and supported connectors for the installed edition rather than assuming the Windows menu is identical. Microsoft’s connector availability reference is the relevant platform check. This guide’s table design applies independently of those interface differences.

Inspect the JSON hierarchy

[
  {"id":"0017","customer":{"name":"René"},"items":[{"sku":"A1","qty":2},{"sku":"B2","qty":1}]},
  {"id":"0028","customer":{"name":"Asha"},"items":[{"sku":"C3","qty":3}]}
]

The top-level value is a list of two orders. Each order has an identifier, one customer object and a list of items. Order 0017 contains two items, so a one-row-per-order table cannot represent each item in separate ordinary columns without an additional design choice.

For this export, define one row as one order item. Keep the parent order identifier and customer name on every item row. This intentional repetition allows the CSV to retain the relationship between an item and its order.

Expand records and lists deliberately

In Power Query, turn the top-level list into a table if it is presented as a List, then expand each order record into its fields. Expand the customer record to its name field. Expand the items list into rows, then expand each resulting item record into sku and qty columns.

The exact preview may already perform some of these steps. Inspect the Applied Steps and resulting values instead of blindly adding a duplicate expansion. Microsoft’s structured-column guidance explains selecting fields through the expansion control.

Compare the intended output table

OrderID,CustomerName,SKU,Quantity
0017,René,A1,2
0017,René,B2,1
0028,Asha,C3,3

The expected table has three data rows, two distinct OrderID values and a total quantity of six. These three checks catch different errors. A result with only two rows may have dropped an item, while a larger result may reflect an unintended multiplication during list expansion.

Rename the columns clearly and select them in the intended order. Do not retain an opaque Record or List display as though it were the actual scalar field value. CSV needs the chosen fields, not the query editor’s label for a nested structure.

Set identifier types before loading

Keep OrderID as text so that 0017 stays distinct from a numeric representation of 17. Review automatic type-conversion steps and correct them before they remove meaningful characters. Quantity can be numeric under this example’s contract, but a digit-only field is not automatically a quantity.

For long identifiers supplied as JSON numbers, precision may already be at risk during numeric parsing. A reliable producer should supply identifiers as strings when their exact digits are required. Changing the column to text after a number has already been rounded cannot recover the original digits.

Avoid accidental combinations of independent lists

If an order also contains several phone numbers, expanding both items and phones into rows can create every item-phone combination. That may be mathematically consistent with expansion but meaningless for the intended export. Use separate related tables or a documented aggregation when the lists describe independent relationships.

Also decide how empty item lists should behave. An order with no items may belong in a separate order-level export rather than an item table. Inspect such cases explicitly; preserving an order and preserving an order item are different requirements.

Load and save the selected worksheet

Load the reviewed query into a worksheet and save a workbook copy to retain the query steps. Export the intended sheet as CSV using the destination’s encoding and delimiter requirements. A CSV does not retain the query, nested JSON structure or multiple workbook sheets.

Reopen the exported text with a CSV-aware reader and compare the two identifiers, three item rows and quantity total against the fixture. Then test the receiving application’s column mapping. The example checks the intended mapping; it does not claim that every possible JSON document can be flattened without additional decisions.