Excel Power Query JSON: Import, Expand, and Refresh

How-to basics · Updated November 21, 2026

In Excel for Microsoft 365 on Windows, import a JSON file with Data > Get Data > From File > From JSON, then choose Transform Data to shape it in Power Query before loading it to a worksheet. For JSON returned by a web service, use Data > Get Data > From Other Sources > From Web. Power Query can turn JSON records and arrays into tables, expand nested data, and refresh the query when the source changes.

What Excel Power Query does with JSON

JSON is a text format for representing data as objects, arrays, values, and combinations of those structures. It is common in web APIs, configuration files, and data exports. Unlike a typical spreadsheet, a JSON document does not have to be a flat grid: one object may contain another object, and an array may hold many records or values.

Power Query is Excel’s data-import and transformation tool. It reads the JSON structure, presents it in the Power Query Editor, and lets you convert it into rows and columns. You can choose fields, rename columns, change data types, filter records, and expand nested content. The result is a repeatable set of steps, rather than a one-time manual copy and paste.

A useful distinction: Power Query does not make every JSON document a ready-made table automatically. It interprets the document’s structure, but you may need to decide which records should become rows and which fields should become columns. A small, flat JSON response may need little adjustment; a deeply nested response can require several expansions.

Import a JSON file

  1. In Excel, select Data > Get Data > From File > From JSON.
  2. Browse to the file and select Import. Power Query opens a preview of the detected content.
  3. Select Transform Data to open the Power Query Editor. Use this option when you need to inspect or reshape the data before loading it.
  4. When the result is ready, select Home > Close & Load to put it into a worksheet. Use Home > Close & Load To if you want to choose a destination or create a connection without loading a table to a sheet.

The exact appearance of the preview depends on the JSON’s top-level structure. If it is an array of similar objects, Power Query can often present an option to convert the list to a table. If it is a single record, you may first see a record view with fields and values. Select or drill into the relevant item, then use the table-conversion or expansion controls that appear in the editor.

Power Query records the import and transformation steps in a query. In Excel for Windows, open the query again from Data > Queries & Connections, then double-click it or right-click and choose Edit. This lets you adjust the transformation later without repeating the import from scratch.

Understand records, lists, and nested data

Knowing three JSON building blocks makes the editor easier to navigate. An object, which Power Query commonly represents as a record, contains named fields. An array, often shown as a list, contains an ordered collection of values or objects. A value is an individual item such as text, a number, a Boolean, or null. JSON uses braces for objects and square brackets for arrays.

For example, a response might contain an array called orders, with each order holding an id, a customer record, and an items array. Convert the orders list to a table to get one row per order. Expand the customer record to create customer columns. Expand the items array to get item-level rows. If an order has several items, expanding that array can produce several rows carrying the same order ID; that is expected, not necessarily duplicated data.

In a table column containing records or tables, the expand button in the column header (often shown as two arrows) opens a field-selection menu. Select the fields to include and, when useful, clear the option to use the original column name as a prefix. Lists may first need to be converted to a table or expanded to new rows. Check the row count after each expansion: expanding a one-to-many relationship changes the grain of the result from one row per order to one row per item.

Shape JSON into a useful table

Before loading, decide what one row in the final table should represent. It could be one customer, one order, one product, or one order line. That decision guides whether nested arrays should become separate tables or be expanded into the main table. Flattening everything into one table is convenient for analysis, but it can repeat parent values and increase the number of rows.

Common transformations are available from the Power Query Editor ribbon and column menus. You can rename columns, remove fields you do not need, filter rows, reorder columns, split or combine text, and set data types. Use the type icons in column headers or the Transform and Home tabs to specify types such as text, whole number, decimal number, date, or date/time. Do not assume a value that looks like a date is already typed as a date; inspect it, especially when the source uses an unusual format or includes time-zone information.

JSON field names are not always consistent. Some records may omit a field, provide null, or use different types for the same field. For example, a quantity might be numeric in most records but text in one. Power Query may report an error when a conversion cannot be made. Review the affected rows and decide whether to correct the source, replace or handle the value, or keep the column as text. Changing a type does not repair an invalid source value.

Example: turn orders into rows

Imagine a file whose top level is an array of order objects. Each object has an order number, a date, a customer object, and a list of products. After converting the array to a table, expand the customer record to expose fields such as name and region. Set the date column to Date if it contains dates without times, and set the order number to text if leading zeroes are meaningful. This prevents Excel from treating an identifier like a quantity.

Next, choose how to handle products. Expanding the product list into rows makes it possible to analyze each product sold, but an order with three products becomes three rows. If the goal is a summary with one row per order, keep the product list separate or create a separate query for order lines instead of flattening it into the order table. You can reference or duplicate a query in the editor when you need related outputs at different levels of detail.

Finally, give the query and columns clear names, check that the resulting row count makes sense, and select Close & Load. In the worksheet, confirm that IDs, dates, and numeric values display as intended. A sound table design is useful later for PivotTables, charts, and other analysis because it makes clear what each row and column represents.

Load JSON from a web service

For an API or other web endpoint, select Data > Get Data > From Other Sources > From Web, enter the URL, and follow the prompts for credentials or access method if they appear. Choose Transform Data to inspect the returned content. Some endpoints return a JSON document directly; others require authentication, query parameters, or a particular request setup. Use only an endpoint you are authorized to access, and follow its documentation and usage limits.

Power Query’s M language can also express a web request. For example, a basic request can use Json.Document(Web.Contents("https://example.com/data.json")). This is a pattern, not a working data source: replace the example address with the actual endpoint. The M expression parses the response as JSON, but it does not by itself solve every API requirement. An API that needs headers, a token, pagination, or a request body may require additional query steps or a supported authentication configuration.

Many APIs return only the first page of results. A query that successfully imports that response may therefore still be incomplete. Check the service’s documentation for page numbers, continuation tokens, maximum page sizes, and rate limits. Pagination often needs deliberate query logic; do not assume Refresh automatically retrieves every page just because the first page loaded.

Refresh and maintain the query

After loading, refresh the results with Data > Refresh All, or right-click the loaded table and choose Refresh. Refresh runs the saved query steps against the source again. For a file source, the file path must remain available and the replacement file should retain a compatible structure. For a web source, the endpoint must still respond and any required credentials must be valid.

If a source changes its field names or structure, a step that refers to a removed field may fail. A new field might appear in the source but not be included in a query that explicitly selected columns earlier. When a refresh error occurs, open the query in Power Query Editor and review the Applied Steps pane to identify the step that failed. Fixing the step may involve selecting the new field, revising a type conversion, or adjusting the source.

Excel for Microsoft 365 on Mac also includes Power Query, but connector availability and features can differ by Excel build. Check Data > Get Data for the JSON or Web connector on your installed version; if a connector is not offered, Windows Excel may be needed for that import workflow. Excel for the web does not provide the full Power Query authoring experience available in desktop Excel, so create or edit complex queries in the desktop app.

Common problems and practical checks

  • The preview looks like a record or list, not a table: That reflects the document’s outer structure. Drill into the relevant field or convert the list to a table, then expand records into columns.
  • Some fields are blank: The source may omit those fields in certain records or provide null values. Blank cells do not necessarily mean the import failed.
  • Numbers or dates become errors: Check for mixed types, unexpected text, locale-specific number formats, or date/time values that do not match the conversion step.
  • Refresh asks for credentials: Review the data-source authentication settings and use the access method required by the file location or service. Do not place private credentials in a shared workbook or an unsecured query.
  • A web query works once but not later: The service may have changed, credentials may have expired, or the API may limit requests. Check its status and documentation as well as the query’s source and authentication settings.
  • Refresh returns fewer rows than expected: Check for pagination and filters, and verify that the source response itself includes the records you expect.

For shared or sensitive data, consider who can access the workbook and its source. Power Query may store source details or authentication settings as part of Excel’s data-source configuration. Use the organization’s approved credential and privacy practices, and avoid distributing a workbook that exposes access tokens or confidential endpoint information.

Power Query versus other ways to use JSON

Power Query is a good fit when the goal is to import structured JSON, reshape it with repeatable steps, and refresh the result in Excel. Compared with copying JSON text into cells and manually splitting it, a query is easier to rerun and maintain. Compared with writing a custom program, the editor offers a visual workflow for common transformations, while M allows additional control when the interface is not enough.

For a one-time, small document, a text editor or a JSON-aware viewer may be quicker for simply inspecting the raw structure. For large, complex, or frequently changing API data, a dedicated data platform or a purpose-built script may offer more control over pagination, error handling, scheduling, and monitoring. Excel’s worksheet grid also has a limit of 1,048,576 rows per sheet, so very large results may need a connection or a different storage and analysis tool rather than a loaded worksheet table.

Power Query is not the same as an Excel cell formula that parses arbitrary JSON. Its steps run as a data-import transformation and produce a loaded table or connection. Once loaded, ordinary Excel tools can analyze the result, but changing the original JSON generally means refreshing the query rather than editing the output cells, which can be overwritten on the next refresh.

Frequently Asked Questions

Can Excel open a JSON file directly?
Yes. In Excel for Microsoft 365 on Windows, use Data > Get Data > From File > From JSON. Power Query opens the file so you can inspect, transform, and load its contents.

How do I expand nested JSON in Excel?
Convert the relevant list to a table if needed, then use the expand control in a column containing records or tables to choose fields. Lists may need to be converted or expanded into rows first.

Can Power Query import JSON from a URL?
Yes, when the endpoint and authentication method are supported. Use Data > Get Data > From Other Sources > From Web, then inspect the response. API pagination or special request requirements may need additional setup.

Will Excel update the JSON table automatically?
Not simply because the source changes. Use Refresh or Refresh All to rerun the query. Automatic refresh behavior, where available through query properties, depends on the workbook and Excel environment.

Why does my JSON import show only one row?
The top-level document may be a single record, or the array of records may be nested inside a field. Inspect the record and drill into the array, then convert that list to a table. Also check whether the API returned only one page or a limited result.

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.