JSON to Excel: Load Records as Rows with Power Query

To load JSON to Excel as rows, use Data > Get Data > From File > From JSON. Power Query opens the file, and you turn the list of records into a table, expand each record into columns, set the column types, and load the result to a sheet. Next month, drop in the new file and press Refresh.

Which Excel versions can load JSON to a worksheet?

Excel loads JSON through Power Query, the data import engine behind the Get Data button. The desktop Excel that comes with Microsoft 365 has it on the Data tab. Microsoft's page on the Power Query JSON connector lists Excel among the products that support it, with import as its supported capability.

In practice, three questions settle whether your copy can do it:

• Is there a Get Data button on the Data tab? If yes, open its From File menu and look for From JSON.
• Are you in the browser version? Excel for the web has fewer import options than the desktop app, so a JSON route that works on a desktop may be missing there. Excel Online versus Google Sheets compares what each browser spreadsheet can import.
• Is it an old perpetual licence? Older versions handled Power Query as a separate add-in or with fewer connectors. If From JSON is missing, a JSON-to-CSV step is the simplest way round.

That last route is worth knowing even when Power Query works. A CSV opens anywhere, including on phones and in other spreadsheet apps. Converting JSON to CSV covers how to flatten the file first, which suits a one-off look at the data. Power Query suits a file you will load again and again.

Opening a JSON file with Get Data in Excel

Opening a JSON file in Excel takes four clicks: Data, Get Data, From File, From JSON, then pick the file. Excel does not put the data on a sheet straight away. It opens the Power Query Editor, a separate window where you shape the data before loading it, and that is where the next steps happen.

The worked example below uses a small transactions file. The values are invented for illustration.

{
  "account": "Example Checking 2208",
  "currency": "USD",
  "transactions": [
    { "date": "2026-05-02", "description": "RENT PAYMENT", "amount": "-1350.00",
      "tags": ["housing"] },
    { "date": "2026-05-09", "description": "PAYROLL EXAMPLE CO", "amount": "2840.15",
      "tags": ["income", "salary"] },
    { "date": "2026-05-17", "description": "GROCER ON MAIN", "amount": "-96.42",
      "tags": [] }
  ]
}

The step list, from file to table

1. In Excel, open the Data tab and choose Get Data > From File > From JSON.
2. Pick the file and choose Import. The Power Query Editor opens.
3. If the top of the file is an object, as here, you see a Record with one row per key: account, currency and transactions. Click the word List next to transactions to drill into the array.
4. You now see a list of records. Choose To Table on the ribbon, accept the defaults, and choose OK.
5. A single column called Column1 appears, with the word Record in every row. Click the small two-arrow icon in its header.
6. Tick the fields you want, untick Use original column name as prefix, and choose OK.
7. You now have one row per transaction with date, description, amount and tags columns.

Microsoft notes that the connector applies automatic table detection, so on some files the editor skips straight to a flat table. When it does, check that it picked the array you wanted. On a file with several arrays it may choose a different one.

Before and after

The tags column still says List in every row. That is the next problem to solve.

Expanding JSON records and lists into Excel columns

A record in JSON is a set of named values, and expanding it in Power Query turns each name into a column. A list is an array of values, and expanding it turns each item into its own row. Records widen the table; lists lengthen it. Knowing which one you are expanding tells you what the result will look like.

Look at the tags column from the example. The two-arrow icon offers two choices for a list: Expand to New Rows or Extract Values. Each gives a different table.

• Expand to New Rows copies the whole transaction once per tag. The payroll line, with two tags, becomes two rows. A sum of the amount column now counts that payment twice.
• Extract Values joins the tags into one text cell with a separator you choose, such as a comma. The table keeps one row per transaction, and totals stay correct.

For anything with money in it, Extract Values is the safer default. Expanding to new rows is the right call only when you want to count or filter by tag and you will never total the amounts in that table.

Header fields that live outside the array

In the example, account and currency sit at the top of the file, outside the transactions array. When you drill into the list, they drop out of view. To keep them, go back to the first record step, convert it to a table, and use a custom column or a merge to attach the account to every row. For a single-account file, it is often quicker to type the account name into a column after loading.

Deeper nesting

Some files nest records inside records, such as a merchant object with a name and a category inside each transaction. Each level gets its own two-arrow icon. Expand one level at a time, and rename columns as you go, so you do not end up with headers like merchant.category.name.

Which column types should a JSON import in Excel use?

Power Query loads most JSON values as text or as the generic Any type, so dates do not sort as dates and amounts do not sum. Setting the type of each column in the editor, before loading, fixes that once and the setting is kept for every refresh.

Click the small icon at the left of each column header and pick a type:

• date: Date. If your file uses day-first dates, choose Using Locale from the same menu and pick a matching locale, so 03/04 lands on the right day.
• description: Text.
• amount: Fixed Decimal Number. Microsoft's Power Query data types reference calls it the Currency type: it always keeps four digits after the decimal separator and, unlike Decimal Number, is always precise, which suits money.
• tags: Text, after extracting the values.

Amounts in this example are quoted strings, such as "-96.42". That is common in financial JSON, because quoted decimals keep exact cents. The type change converts them to numbers inside Excel, so you get the sums without losing the exactness in the file.

One more habit helps. Put the type step last, after every expand and rename, and delete any automatic Changed Type step that Power Query added earlier. A type set too early sometimes errors when a later file has an empty value or a new field.

Refreshing the Excel query when a new JSON file arrives

A Power Query import is saved as a query inside the workbook, so the steps you clicked run again every time you choose Refresh. To load next month's JSON file, replace the old file with the new one at the same path, or point the query's source at the new file, and refresh.

Three ways to handle a monthly file, from simplest to sturdiest:

1. Same name, same folder. Save each new file over the old one, then choose Data > Refresh All. Nothing in the query changes.
2. New name each month. Open the query, click the gear icon next to the Source step, and browse to the new file. Every later step stays the same.
3. One folder of files. Microsoft's page recommends a multi-file connector such as the Folder connector to import several JSON files at once. Put every month's file in one folder, connect to the folder, and the query combines them into one table.

The folder route is best when you want a year of statements in one sheet. Make sure every file has the same structure. A new field in one month is usually fine, but a renamed field breaks the expand step for that file.

If the data needs to go the other way, for example from a spreadsheet back into an app that reads JSON, turning a CSV into JSON covers the reverse trip and the type choices it involves.

JSON shapes that Excel's import cannot flatten cleanly

Some JSON shapes resist Excel's import. JSON Lines files, with one object per line and no outer array, fail to load. Arrays that mix numbers, text and objects expand unevenly. Very large files can exceed the worksheet's row limit. Each has a workaround, but none of them is a simple click.

JSON Lines

A JSON Lines file looks like JSON, but each line is its own object, with no brackets around the whole file. Microsoft's troubleshooting note names a JSON Lines file as one cause of the connector's error message. The same page gives a short piece of M code that reads the file line by line, parses each line as JSON, and leaves you to expand the result. Pasting that into a blank query in the Advanced Editor is the fastest fix.

Mixed arrays

An array that holds a number in one item and an object in the next cannot become one tidy column. Power Query will show some cells as values and others as Record. Filter the column by type, handle each group, and append them back together, or ask whoever produces the file to keep each array to one kind of item.

Very large files

An Excel worksheet holds just over a million rows. A JSON file with more records than that cannot load to a sheet in one piece. Load the query to the Data Model instead, which holds far more rows for pivot tables, or filter the rows in the editor so only the period you need reaches the sheet.

If your JSON came from a bank statement, there may be a shorter path. FastBankConvert is getting ready to open with conversion of statement PDFs to Excel, CSV or JSON, so you could take the workbook directly and skip the import. You can get started to hear when it does.