How to easily parse JSON in Power BI Desktop

Sometimes you want to use JSON data in Power BI. And sometimes Power BI doesn’t exactly do what you want it to do with the information. This blog will explain how to easily make your JSON data readable with Power BI Desktop.

Data in one column

Initially, we start from a .json file as shown in the image below.

When you select JSON as your data source, Power BI is smart enough to parse the JSON in all the available columns for you, which is the result we want. But in some cases, you will find the whole JSON data in one column. If that is the case, this blog will guide you in the right direction to retrieve all the available columns that are listed in the JSON data.

Create an Excel file

To reach this starting point, we created an Excel file that contains the same data as our .json file. The data is about orders that have been made by a specific customer. For each order, we have put it in a cell in Excel. This Excel file will be our data source that we import into Power BI Desktop.

After importing this Excel file, we have a column containing all this JSON data in one column. This will be our starting point.

Transform your table

If you have a similar column like ours, head over to your Power Query Editor to transform the table that has the column that contains the JSON data. That’s step one. In our table ‘OrderDetails’, we have five records containing the details of an order in JSON format. When selecting one of the records, you will see the whole JSON data below.

Step 2: In case you don’t have one already, add an index column.

Step 3: Luckily, there is a ‘Parse JSON’ option in the ‘Add Column’ section. This will create an extra column that contains the records.

By clicking on one of the records in the newly added column, you will also see what is inside each record.

Step 4: In the new column, simply click on the two arrows facing away from each other.

Select all the necessary columns and press ‘OK’ to get all the required data that was once in JSON format.

And there you have it. Four simple steps to change your JSON data into actual readable format within Power BI Desktop.

Nested JSON data

In case your JSON data contains nested data, there are some things to consider. In our table ‘OrderDetails_NestedJSON’, we have one record that contains nested JSON data. After copying the whole JSON and pasting it into the JSON online editor (https://jsoneditoronline.org/) you can see there are two extra variables in the Item variable.

This means we can expand the column, which is created with the ‘Parse JSON’ function, multiple times.

Expanding it once:

Expanding it twice:

Eventually after expanding it 5 times, we get all the necessary columns:

As you probably have noticed, the record with index ‘1’ appears four times now, due to expanding the nested JSON.

Something we could have predicted if we expanded all the variables in the online JSON editor. There we can see that there are two items for each order_id. Thus, creating four rows in Power BI Desktop.

Definitely something to keep in mind when expanding nested JSON data, as multiplying your records x-times in a table will decrease the performance of your Power BI Report. In addition to performance, this can also cause uncertain calculations, just because the rows are duplicated, and some things are counted twice.