Excel Power Query: Turn a JSON List Into a Table

In Excel for Microsoft 365 on Windows, choose Data > Get Data > From File > From JSON, select the file, and in Power Query Editor choose Transform > To Table. If the resulting column contains Record values, use its expand button to select the fields you want as table columns.

What a JSON list looks like

JSON is a text format for representing data. A list, also called an array, is enclosed in square brackets. For example, a file containing [{"id":101,"product":"Mug","qty":2},{"id":102,"product":"Notebook","qty":4}] has a top-level list with two items. Each item is a record, or object, containing three named fields.

Power Query reads the JSON structure rather than treating the entire file as ordinary text. It may show a top-level value as a List, Record, or Table, depending on the JSON layout. A JSON list is not automatically a well-formed Excel table: first you convert the list to rows, and, when its items are records, expand those records into columns. Nested lists and records may need another expansion step.

That distinction explains a common point of confusion. Choosing a JSON file and seeing the word “List” in the editor does not mean the import failed. It means Power Query has recognized the array and is waiting for you to choose how to tabularize it.

Import a JSON file and convert its list

For a local file in Excel for Microsoft 365 on Windows, follow these steps:

Open the workbook and choose Data > Get Data > From File > From JSON.
Browse to the JSON file and select Import. Power Query Editor opens and displays the file’s top-level structure.
If the preview says List, choose Transform > To Table. In the conversion dialog, review the available options and select OK. For a standard array, the default treatment is usually a suitable starting point.
If the new column contains values labelled Record, select the expand icon in that column’s header. Choose the fields to include and confirm. Clear the option to use the original column name as a prefix if you prefer shorter headings.
Review the resulting columns and data types. Then choose Home > Close & Load, or Home > Close & Load To to select where to put the result.

For the sample array, the output should have two rows and columns for id, product, and qty. The words “List” and “Record” are structural labels in the preview; they are not values you normally want in the final worksheet.

If you want to inspect or reshape the query without immediately creating a worksheet table, use Close & Load To and choose a connection-only option when available. You can later load the query result or use it as the source for another query.

Expand records into useful columns

After converting a list of records to a table, Power Query commonly places each whole record in a single column. Expanding that column turns the record’s named fields into separate columns. The expand control is the icon with opposing arrows at the right side of the column header. Its field checklist lets you include only what matters, which is useful when an API returns many fields such as internal identifiers, URLs, or metadata.

Field names in an expanded result become column headings. If every item has the same fields, the operation is straightforward. If some records omit a field, Power Query can show a null value for that row. If fields appear only in later records, examine the field selection carefully: a query that selected a fixed set of fields may not automatically add every new field when the source changes.

Nested data needs additional work. For example, an order record might include a customer record and an items list. Expanding customer can expose customer fields as columns. Expanding items can create multiple rows for an order, potentially repeating the order-level values. That row multiplication is often appropriate for item-level analysis, but it changes the grain of the data. Decide whether one row should represent an order or an individual item before expanding.

Choose the right import route

The From JSON connector is a good choice for a local .json file because it creates a query that can be refreshed. If the JSON is provided by a web service, use an appropriate web connector, such as Data > Get Data > From Web, when you have a permitted URL and the service allows the request. Authentication, privacy settings, and service limits can affect whether a web query works.

For a small JSON example copied from documentation, you can instead create a text source in Power Query and parse it, but that is less convenient for a file you expect to update. Pasting JSON into a worksheet cell does not itself turn the content into structured rows; it leaves you with text unless you then use a parsing or transformation method. Avoid manually splitting JSON at commas: commas can occur inside nested arrays, objects, or quoted text, so a simple split can damage the structure.

Power Query Editor’s menus can vary slightly with Excel updates and platform. On Excel for Microsoft 365 for Mac, Power Query is available, but connector and editing capabilities may differ from Windows. If a menu or connector is missing, check the current Excel for Mac feature availability and use the Windows desktop editor for the steps described here. Excel for the web is not a full substitute for the desktop Power Query authoring experience.

Handle nested arrays and irregular data

Nested JSON is normal, not necessarily an error. A top-level list may contain records; a record may contain another record or a list; and that list may contain more records. In the editor, use the value shown in a cell or its expand control to continue into that level. Each expansion should be deliberate, because expanding a child list can change the number of output rows.

Consider this simplified structure: each order has an order number and a list of line items, and every line item has a product and quantity. If you expand line items, one order with three products becomes three rows. If you need an order summary with one row per order, aggregate the item data or keep it in a separate query instead of flattening it blindly. Keeping separate queries for different levels can preserve clear relationships between orders and items.

Data types also deserve attention. A number-like identifier such as “00125” may need to remain text so its leading zeros are preserved. Dates and times can be interpreted differently depending on their format and locale. Check the type icon beside each column heading and use Transform > Data Type to correct a type when necessary. Do not convert an identifier to a number merely because it contains digits.

Refresh, load, and maintain the result

A Power Query import is a repeatable set of steps, not just a one-time copy. After loading the result, use Data > Refresh All to rerun workbook queries, or right-click the query’s output table and choose Refresh. When the source file changes, Power Query rereads it and applies the saved transformations. The file must remain at the expected location, and the workbook needs access to it.

Refreshing does not guarantee that a changing JSON schema will fit the existing query. If the source adds, removes, or renames fields, a step that expands a fixed list of fields can produce missing columns or omit new ones. Review the query in Data > Queries & Connections, then edit it to update the expansion or later steps. A renamed field can also cause an error if a subsequent step refers to its old name.

For repeatable work, keep the source file separate from the output workbook where practical, use a stable file path, and give the query and output table clear names. If a process depends on a downloaded file, replace that file with an updated version at the same path before refreshing. Save a copy or retain the source data before making major transformation changes.

Common problems and practical fixes

The editor shows List but no columns: Select Transform > To Table first. Then expand the record column if the list items are objects.
The result has one column of Record values: Use the expand control in the column header, choose the fields, and confirm.
Some cells show null: Check whether those records actually contain the field. Missing values are different from a failed import.
There are more rows than expected: Look for an expanded nested list. Each item in that list can produce a separate row.
A refresh reports that a file cannot be found: Confirm the source path, filename, permissions, and whether the file was moved or renamed.
Columns or values look wrong: Inspect the Applied Steps pane. A type conversion, expansion choice, or changed source field may be responsible.

When an error appears, select the step that first produces it in Applied Steps rather than changing several later steps at once. This makes it easier to identify whether the problem is in the source, conversion, expansion, or type assignment.

When Power Query is a better fit than manual conversion

Power Query is particularly useful when you will import the same kind of JSON repeatedly, need to select fields, or want a documented sequence of transformations that can be refreshed. It also handles nested structures more reliably than manual text splitting. Its main trade-off is that you must understand the source structure and check how the query responds when that structure changes.

For a one-off, tiny array, manually entering a few values into an Excel table may take less time. For ongoing files, APIs, or larger data sets, a query is usually easier to repeat and audit. Power Query is not a JSON editor and does not automatically decide the right analytical shape: you still choose which fields to keep, how nested lists should affect rows, and whether values should be text, dates, or numbers.

Frequently Asked Questions

How do I turn a JSON list into rows in Excel?
Import the JSON through Data > Get Data > From File > From JSON. In Power Query Editor, select Transform > To Table. If the list contains records, expand the record column to create separate columns.

Why does Power Query show Record instead of the actual values?
A record is a structured JSON object with named fields. Convert the outer list to a table, then select the expand icon on the column containing Record values and choose the fields to display.

Can Power Query flatten nested JSON automatically?
It can expand nested records and lists, but you choose which levels to expand. Expanding a nested list can create multiple rows per parent record, so check whether that matches the layout you need.

Will my Excel table update when the JSON file changes?
Yes, if the query still points to an accessible source at the expected path and the JSON structure remains compatible with its saved steps. Use Data > Refresh All to rerun queries, and edit the query if fields have changed.

Can I import JSON directly from a URL?
Often, yes: try Data > Get Data > From Web with the service’s URL. Whether it works depends on the endpoint, authentication, privacy settings, and the service’s access rules; some APIs require additional setup.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.