To turn JSON into an Excel table, use Data > Get Data > From File > From JSON, select the file, then use Power Query Editor to convert lists to tables and expand records into columns. Choose Home > Close & Load to put the result in a worksheet; the query can be refreshed when the source changes.
What Power Query does with JSON
JSON is a text format for representing data, but its structure is not automatically a rectangular grid. It can contain objects (collections of named fields), arrays (ordered lists), and values such as text, numbers, true or false, and null. An object might describe one order; an array might contain many orders; an order might itself contain a nested customer object and an array of products.
Power Query reads that structure and displays it as navigable data. In the editor, an object is generally shown as a Record, an array as a List, and a group of rows and columns as a Table. The goal is to reach a table whose rows represent the items you want to analyze and whose columns represent their fields. You decide how to flatten nested data, rather than simply changing a file extension or pasting JSON into cells.
Import a JSON file in Excel for Windows
- In Excel for Microsoft 365 on Windows, open the workbook and select Data > Get Data > From File > From JSON.
- Browse to the .json file and select Import. Power Query Editor opens with a preview of the parsed content. If Excel asks about privacy or credentials for a source, choose settings appropriate to that source.
- Follow the displayed Record or List values into the part of the JSON that contains the data you need. If the useful content is a list, select To Table in the editor and confirm the options if prompted.
- Expand columns containing records or tables using the expand control in the column header. Select the fields to include, and clear the option to use the original column name as a prefix if you prefer shorter headings.
- Review the columns and data types, then select Home > Close & Load. To choose where the result goes or whether it becomes a connection only, use Home > Close & Load To when that option is available.
Menu wording and the editor layout can vary slightly with an Office update, but the important distinction is that From JSON opens the query editor; it does not promise that every JSON file will immediately arrive as a finished worksheet table. Most files require at least one navigation or expansion step.
Convert records and lists into rows and columns
Consider a JSON response shaped like an array of orders: each array item is an object with fields such as orderId, date, and customer. If Power Query shows a List, convert it to a table. Each list item becomes a row, often initially in a single column. Expand the column of Records to expose the fields as separate columns. Rename generic headings such as Column1 to useful names if needed.
If the top-level content is one Record rather than a list, it may represent a single response envelope, for example an object with a status field and a results array. Navigate into results to work with the repeated records. Keeping the envelope fields may be useful for diagnostics, but they are not necessarily part of the row-level table.
Expansion is a deliberate choice. Selecting every available field is convenient for exploration; selecting only the fields needed for reporting produces a slimmer table and can make later maintenance easier. Power Query records each navigation, conversion, and expansion as an applied step, so you can return to an earlier step and adjust the transformation without repeating the import from scratch.
Handle nested objects and arrays
A nested object, such as a customer with name and region fields, can usually be expanded into columns alongside the order fields. A nested array, such as an order’s products, requires a decision about the table’s grain—the meaning of one row. Expanding products to new rows can produce one row per order-product pair. The order ID then repeats for each product, which is useful for product analysis but changes the row count.
For example, an order with three products becomes three rows after the product list is expanded. Summing an order-level total from that result could count the same total three times. To avoid misleading totals, keep order-level and product-level data in separate queries or tables, or carefully choose which fields to aggregate. A flat table is not automatically a faithful one-row-per-order summary when nested arrays are involved.
Power Query also lets you expand a table-valued column and choose the fields to bring across. For highly nested data, repeat the process one level at a time: expand the parent record, inspect the resulting columns, then expand the relevant child list or record. This makes it easier to see where row multiplication occurs and which fields are genuinely needed.
Load the result and refresh it
Use Close & Load to load the output to a worksheet, where it appears as an Excel table. The query’s transformation steps are saved with the workbook. To retrieve updated source data later, select Data > Refresh All, or right-click the loaded table and choose Refresh. Refresh reruns the query; it is not the same as editing the loaded cells. Changes typed directly into query output cells can be overwritten by a refresh.
The workbook needs access to the source when refreshing. For a local file, that means the file must still be available at the saved location; moving or renaming it can break the source step. If colleagues need to refresh the workbook, use a shared, stable location they can access and ensure their permissions and privacy settings allow the connection. The loaded table remains useful as a saved snapshot when a refresh is not possible, but it will not update itself.
Excel for the web can work with some Power Query connections and offers query capabilities that depend on the source and account, but it is not a drop-in substitute for every Windows authoring workflow. On Mac, Power Query features and available connectors depend on the Excel version. If you do not see a JSON connector or the same editing controls, check the current Excel for Mac options or build the query in Excel for Windows and test whether the intended Mac users can refresh it.
Check data types and changing JSON fields
After expansion, inspect types rather than assuming the preview guessed correctly. A numeric-looking identifier such as 00127 may need to remain text so its leading zeros survive. Dates can be interpreted differently depending on their format and locale; confirm that values sort chronologically and display as intended. Nulls are not the same as zero or an empty string, and a field missing from one record may appear as a null in that row after expansion.
JSON APIs and exported files can change over time. A new field may appear, an old field may disappear, or a value may switch between a number and text. Expansion steps commonly store a selected set of field names, so a newly added field may not appear automatically in the output. If a refresh starts producing errors or omits a new attribute, inspect the applied steps and the updated source structure. Conversely, if a field is optional, make sure downstream calculations can handle nulls.
When the source contains dates, currency amounts, or other values requiring exact interpretation, compare a few output rows with the original JSON. Power Query’s automatic type detection is a convenience, not a guarantee that every business field has the intended meaning. Set or change types in the editor after considering how the values will be used in Excel.
Common problems and what to check
- The preview shows one value rather than rows: Check whether you are looking at a top-level Record or at a List that has not yet been converted with To Table. Navigate to the repeated array before expanding it.
- Some expected columns are missing: Reopen the relevant expand step and review its selected fields. Check whether the source has changed or whether the field is nested under another Record.
- Refresh reports that the file cannot be found: Confirm that the file path in the source still exists and that the current user has permission to read it. A path on one person’s computer may not work for another workbook user.
- Values appear in the wrong type or format: Check the type-setting steps, locale-sensitive date interpretation, and whether identifiers should be text rather than numbers.
- There are more rows than expected: Look for an expanded nested list. Each item in that list can create a separate row, so the output may have a different grain from the original parent records.
- The source is a web address rather than a file: Use Data > Get Data > From Other Sources > From Web where available, then provide the URL and handle any authentication or privacy prompt. A sign-in-protected API may require credentials or a supported access method; a URL that works in a browser is not automatically accessible to Power Query.
When to use Power Query—and alternatives
Power Query is a good choice when you need repeatable imports, a visual way to reshape nested data, and a refreshable result in Excel. It is especially useful when the same JSON layout arrives regularly, because the applied steps can be reused. For a one-time, tiny JSON object, manually copying a few values may be quicker, but it is less reliable and does not create a reusable import process.
If the source system can export CSV or Excel, those formats may be simpler when the data is already flat. If the source is an API, Power Query’s web connector may be appropriate, but authentication, pagination, rate limits, and service-specific response formats can require extra work. For large or frequently updated datasets, a database or dedicated data pipeline may be more suitable than loading every record into a worksheet. The best option depends on source access, data volume, refresh needs, and whether nested relationships need to be preserved.
Frequently Asked Questions
Can Excel convert JSON to a table automatically?
Excel can parse a JSON file through Power Query, but the result may initially be a Record or List rather than a worksheet-ready table. Convert lists to tables and expand the fields you want before loading.
How do I expand a JSON list in Power Query?
If the list contains repeated records, select To Table, then use the expand control on the resulting Record column to choose fields. For a nested list inside a table, decide whether expanding it to new rows matches the output you need.
Can Power Query import JSON from a URL?
Often, yes. Use the From Web connector for an accessible endpoint and respond to credential or privacy prompts. Authentication, API permissions, and the endpoint’s response structure can affect whether the import works.
Will the Excel table update when the JSON file changes?
Not by itself. Use Data > Refresh All after the source changes, and make sure the workbook can still reach the file or URL. Refresh reruns the query steps against the current source.
Why does expanding JSON create duplicate-looking rows?
Expanding a nested array can create one output row for every item in that array, repeating parent fields such as an order ID. Those rows may be correct at the item level, but they can inflate parent-level totals if analyzed as though each row were a separate order.
Related
- Excel Power Query Editor: What It Does and How to Use It
- Excel Power Query: How to Extract and Refresh Data
- Excel Power Query Date Format: Types, Changes, and Fixes
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Merge Vertical Cells: Steps, Limits, and AlternativesTo merge cells vertically in Excel for Microsoft 365 on Windows, select a vertical range such as A2:A4,…
- How to Make a Poster Board in PowerPoint for PrintingTo make a poster board in PowerPoint, set a custom slide size to match the finished poster, arrange…
- Convert Excel to Google Docs: A Step-by-Step GuideTo convert an Excel workbook into an editable Google spreadsheet, upload the .xlsx file to Google Drive, open…
- How to Highlight in Microsoft ExcelTo highlight cells in Microsoft Excel, select the cell or range, then choose Home > Fill Color and…
- Creating a Cover Page in Word: Steps and Design TipsTo create a cover page in Microsoft Word, open the Insert tab, choose Cover Page in the Pages…
- How to Add Slide Numbers in Your PowerPoint FooterTo add slide numbers in PowerPoint on Windows, open your presentation, choose Insert > Header & Footer, select…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.