Power Query JSON to Table: A Practical Guide

How-to basics · Updated November 3, 2026

To convert JSON to an Excel table with Power Query, choose Data > Get Data > From File > From JSON, select the file, and use To Table and the expand buttons in Power Query Editor to shape the data. Then choose Home > Close & Load to put the result in the workbook; you can refresh the query when the source changes.

How Power Query reads JSON

JSON is a text format for representing data, not a spreadsheet grid. It organizes values into objects (collections of named properties) and arrays (ordered lists). A JSON document might describe one customer as an object, or contain an array of many customer objects. Objects and arrays can also be nested inside one another.

Power Query parses that structure and displays it as a list, record, or table, depending on the document’s top level. A record is similar to one item with named fields; a list is a sequence of items; a table has rows and columns. You often need to convert a list or record into a table and expand nested values before the data is ready for analysis.

For example, this JSON has an array of order objects:

[{"orderId":101,"customer":"Ava","total":24.5},{"orderId":102,"customer":"Noah","total":18.0}]

Each object can become a row, with orderId, customer, and total as columns. If an object contains another object or array, Power Query may show a cell containing the word Record or List rather than the nested values. That is expected: the data is present, but it has not yet been expanded into columns or rows.

Convert a JSON file in Excel for Windows

These steps are for Microsoft Excel for Microsoft 365 on Windows. The JSON connector reads a local JSON file and opens its parsed contents in Power Query Editor.

  1. Save the source as a JSON file, such as orders.json. If you receive the content in an email or a text editor, save the actual JSON text with a .json extension.
  2. In Excel, choose Data > Get Data > From File > From JSON.
  3. Browse to the file and select Import. Power Query Editor opens with the parsed content.
  4. If the preview shows a list, choose To Table. Review the delimiter and handling options if prompted, then confirm.
  5. If the resulting table has a column of Record or List values, use the expand icon in that column’s header to choose the fields to display.
  6. Rename columns, set data types, remove unwanted fields, and choose Home > Close & Load.

Excel loads the result as a worksheet table by default. If you want a different destination or only a connection, use Home > Close & Load’s dropdown and choose Close & Load To, then select the available destination options. The exact options can depend on the query and workbook.

Convert JSON from a web address

If the JSON is hosted online, use Data > Get Data > From Web and enter the address of the JSON resource. Power Query requests the content and parses it. A URL must return JSON that the connector can access; a web page that merely displays JSON, a sign-in screen, or an error message is not the same as a usable JSON response.

Some services require credentials or particular request headers, and their setup depends on the service’s authentication method. Follow the credential prompts and select an appropriate privacy level when Excel asks. Do not put passwords, access tokens, or other secrets into a query that will be shared as plain text. For an organization’s API, check its documentation and your organization’s data-access rules before connecting.

Connecting to an endpoint does not necessarily retrieve every record in a large API. Some APIs split results across pages or impose request limits. Power Query’s basic From Web connection may return only the page supplied by the address; pagination usually needs additional query logic that follows the service’s documented next-page links or parameters.

Turn lists and records into useful columns

The first conversion depends on the top-level JSON shape. If the document is an array of similar objects, convert the list to a table and expand the records. If the document is a single object, it may appear as a record; convert that record to a table to show its field names and values. The interface can present different conversion choices depending on the selected value, so inspect the preview before transforming it.

When a column contains Record values, select its expand icon and choose the properties to bring into the table. For instance, an order may contain a nested customer object with a name and region. Expanding it can create columns such as customer.name and customer.region. You can clear the option to use the original field name as a prefix if shorter column names are preferable, but make sure names remain distinct.

A nested List requires a choice about the desired shape. Expanding a list into rows can repeat the parent order’s values once for each list item. Converting or aggregating the list in another way may be more suitable if it represents a collection you want to keep together. There is no universally correct flattening: decide whether each output row should represent an order, a line item, a customer, or another entity before expanding.

After expansion, check for null values and columns with mixed types. One record might omit a property that other records contain. Power Query can represent missing values as null, and a column containing both numbers and text may not behave as expected in later calculations. Set the type deliberately after reviewing what the source actually provides.

Use M code for a repeatable conversion

The interface records each transformation as a Power Query step. You can inspect or edit those steps in the formula bar or Advanced Editor. For a top-level JSON array containing objects, a short M query can parse a file and turn its list of records into a table. In Power Query Editor, choose Home > Advanced Editor and adapt the file path:

let
    Source = Json.Document(File.Contents("C:\\Data\\orders.json")),
    AsTable = Table.FromRecords(Source)
in
    AsTable

This example assumes the file is a valid JSON array and each item is an object. The output has one row per object and columns based on the object fields. If the top level is a single object rather than an array, Table.FromRecords is not the appropriate conversion for that value; use record-oriented transformations such as Record.ToTable, or adapt the query to the actual structure. M is case-sensitive in some contexts and its functions expect particular data types, so a query that works for one JSON shape may fail on another.

For most workbook users, the connector and expansion controls are easier to maintain than writing M from scratch. M becomes useful when the same transformation must be repeated, when the source shape needs a conditional step, or when a user interface operation is difficult to reproduce consistently.

Load, refresh, and maintain the result

Power Query keeps the transformation instructions separately from the loaded table. The worksheet output is therefore a result of the query, not a live view that updates every time the source file is edited. To retrieve the latest file contents, use Data > Refresh All or right-click the output table and choose Refresh. A refresh reruns the query steps against the current source.

Keep the source in a stable location. If you move or rename a local file, the saved file path can become invalid; edit the source step or reconnect to the file. If other people need to refresh the workbook, they may need access to the same file location and any required credentials. A path on your own computer may work for you but not for a colleague.

When the source structure changes, the query may need attention. Renamed properties can break later steps that refer to the old column name; new properties may not appear if the query selects only a fixed set of fields. Refresh after meaningful source changes and review the output, especially before using it for reports or downstream calculations.

Excel for Mac also includes Power Query capabilities, but connector availability and menu layout can differ by Excel version and update channel. Look under Data > Get Data for the available import options. Excel for the web has different query-authoring and refresh capabilities from desktop Excel, so a Windows workflow should not be assumed to work identically in a browser.

Common problems and practical fixes

  • Power Query reports invalid JSON: Check for missing commas, unmatched braces or brackets, comments, or extra text before or after the JSON document. JSON is stricter than many programming-language object literals; for example, property names and text values normally need double quotes.
  • The preview shows one column or a list: The content may not have been converted yet. Use To Table, then expand Record or List values as needed. A list is a valid starting shape, not an error by itself.
  • Only some fields appear: Different objects in the array may have different properties, or an earlier step may have selected a limited set. Inspect the source and the Applied Steps, then adjust the expansion or selection step.
  • Numbers or dates look wrong: JSON values and locale conventions can be interpreted differently. Check the column’s data type and use the appropriate locale-aware type conversion where needed. Confirm whether a date is supplied as text, a timestamp, or another representation before transforming it.
  • Refresh fails: Verify that the file still exists at its saved path, that a web source is reachable, and that credentials have not expired. A changed API response or a permission restriction can also stop a previously working query.

A useful troubleshooting habit is to select steps in the Applied Steps pane one at a time. This reveals where the preview first stops matching expectations, which is usually more informative than deleting the whole query and starting over.

Frequently Asked Questions

Can Power Query convert JSON directly into an Excel table?
Yes. Import the file or web response, then convert its list or record structure into a table and expand nested fields. Close and load the query to create a worksheet result.

Why does Power Query show Record or List instead of the JSON values?
Those labels indicate nested objects or arrays. The values have been parsed, but they are not yet flattened into columns or rows. Use the column’s expand control and choose the fields or list behavior you need.

Can I paste JSON into Power Query instead of importing a file?
Yes, but the file connector is usually simpler for a saved document. In Power Query Editor, create a blank query and use the Advanced Editor to define an M expression with the JSON text, such as Json.Document(Text.ToBinary("{""name"":""Ava""}")). Escaping quotes correctly is important; for substantial or frequently updated data, a file or web connection is easier to maintain.

Will the Excel table update automatically when the JSON changes?
Not merely because the source changed. Refresh the query, or configure refresh behavior through the query or connection properties when appropriate. The source must remain accessible and its structure must still match the transformations.

Can Power Query handle nested JSON?
Yes. It can parse nested records and lists, but you must decide how to expand or preserve them. Expanding a list can produce multiple rows and repeat parent values, so choose a row layout that fits the analysis you intend to do.

Choosing the right output shape

Before expanding everything, decide what one row should mean in the finished table. For a simple array of orders, one row per order is natural. If each order has several products, one row per product may be better for summing quantities or comparing items; expanding that list repeats order-level fields across the product rows. You can also keep separate queries for orders and order lines when combining both levels in one flat table would be confusing.

That decision affects totals, filters, and relationships in the workbook. A flattened table is convenient for ordinary sorting and PivotTables, but repeated parent totals can be accidentally summed more than once. Choose fields deliberately, give columns clear names, and verify a few rows against the original JSON before relying on the results. With a sound row structure and a refreshable query, Power Query provides a practical bridge from structured JSON to an Excel table.

Related

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.