How to Convert JSON to Excel Using Power Query

JSON stands for JavaScript Object Notation. It is a lightweight data-interchange format used to exchange data between a browser and a server in a text file format. This text format is easy to read and write by any conventional C series programmer. Also, it is easy to generate by machines. The JSON file format saves the table data in a specific structure. In this blog, we would learn how to convert or import the JSON file to Excel using the Power Query feature of Excel.

Interestingly, converting a JSON File to Excel using a power query is just a matter of a few mouse clicks. No need of installing any JSON File converter.

Let us first get some insight into what does power query means for excel users.

Introduction About Power Query Excel

Power Query (aka ‘Get & Transform’) is an amazing excel feature used to import data from various external sources (like PDF, Text Files, Websites, and many other sources) into excel. Day by day, Microsoft is adding new data import formats to the Power Query feature.

This feature is pre-installed by Microsoft in the newer excel versions of Excel 2016, 2019, and Office 365.

Are you Excel 2010 or Excel 2013 user? If yes, then check this link.

To learn some basics on Power Query, I recommend you go through the Overview of Power Query Excel Feature.

Sample JSON File Data For Converting To Excel

In the below image you can see how the JSON text file data looks like.

As you can see in the above image, the JSON file contains details about students. Each record is placed within curly brackets, with column headers as – StudentID, FirstName, LastName, ContactNumber, and EmailAddress.

We would use this data to convert the JSON file to an excel table.

I have put the sample JSON file for your practice below. Use the ‘Download‘ button to get this file.

So, let us now begin !!

Step by Step Guide to Convert JSON to Excel

Follow the undermentioned steps to import or extract JSON file format to Excel:

• Open a new excel workbook and navigate to Data tab > Get & Transform Data group > Get Data > From File > From JSON.

• In the ‘Import Data’ dialog box that appears, navigate and search for the JSON file. Select it and click on ‘Import’ button.

• As a result, the new window would pop up on your screen called – ‘Power Query Editor’.

• In this window, click on the ‘List’. This would list of all the individual records in the JSON file, would appear on the screen. There are five records in our JSON file. See the demonstration below:

• To see what values do each individual record consist, simply click on the ‘Record’. Consequently, the records would come up, like this:

• Now, let us convert each individual record in table form. Simply, navigate to the ‘List Tools Transform’ tab > ‘To Table’ option (as shown below).

• In the ‘To Table’ dialog box that appears, click OK. As a result, excel would create an array for the records. Finally, use the two-side faced arrow button on the array column header and click OK to expand the array.

• The final step now is to load this table from the power query editor window to excel. To do so, go to ‘Home’ > ‘Close & Load’.

You finally have the JSON data loaded to excel in proper excel table format. See the image below:

With this, we have completed the steps to import JSON data to Excel.