The following should help with any of our connector types, where the data is stored in a column (or columns) in JSON format.



Power BI and JSON

 

One of great things about Power BI is its ability to handle many different types of data which can be in a table column. The type of data in a column can be classified as simple or complex. Examples of simple data types would be numbers, strings,  dates etc. Examples of complex data types would be JSON or XML.  Here we look at how to work with the JSON data type.

 

JSON which stands for JavaScript Object Notation is format for passing messages that is easy for people and machines to generate and process. It is based on what is known as key-value pairs, e.g. “name”:“John Smith”, here the key is ‘name’ and ‘value’ is ‘John Smith’.  A typical JSON message would consist of many key-values some of which could be ‘objects’ or ‘arrays’ which in themselves could embed further JSON.

 

Add … Cases of why a datasource may contain json

 

Here we will look at a ‘relatively’ simple JSON message to demonstrate how we can use Power Query to convert the message into columns in a table.

 

A typical example would be details of a person, e.g. name, age, address and phone numbers structured as:

 

Key

Type

Value

name

String

The contacts fullname

age

Integer

The contacts age

isMarried

Bool

True/false 

address

Object

An object containing address details. Objects are enclosed in curly brackets {}

phoneNumbers

Array

An array of objects containing the contacts phone numbers Arrays are enclosed in square brackets []

 

E.g. 

{

  "name": "John Doe", "age": 35, "isMarried": true,

  "address": {  "street": "123 Main St",    "city": "Anytown",     "state": "CA",     "zip": "12345"   },

   "phoneNumbers": [

    { "type": "home",  "number": "555-555-1234"    },

    { "type": "work",    "number": "555-555-5678"    }

                ]

}

  

 

Convert JSON message into table columns

Using the example above the JSON message would look as below – typically Power Query sets the data type as text:

 



  1. Transform the column to be of type from text to JSON records – right click the record type ‘ABD 123’:



  1. Expand the record, select the required columns and click OK.



Following the expansion we get:

  1. Simple fields – name, age, isMarried 
  2. Embedded record – the address
  3. Embedded array (List) - Phone numbers 



 

Following the expansion of the ‘address’ record we get:




  1. Expand the phone numbers we click on the ‘expand’ icon. Note this time we are asked to add new rows or expand values:





We need to select the new rows option because we know from the JSON description of the message that the array is an array of objects.

Following the ‘Expand to new Rows’ we get the following, note the number of rows has increased by one row for each telephone number the customer has. Note Fred Smith only has one phone number.



Expand the phone numbers field to extract the phone details by clicking on the expansion icon:

 

 

If the table was large then there will be a lot of space used up with all the duplicated rows (ie Name, age etc).

To avoid that:

create a duplicate of the table and name them differently eg CustNameAddress and CustPhoneNumbers.

  • Table CustNameAddress:
    1. Follow steps 1 and 2 as above
    2. Delete the phone numbers column (ie. Don’t expand it, just delete it)

  • Table CustPhoneNumbers:
    1. Follow step 1 as above
    2. Delete all the columns except Cust and phone numbers
    3. Expand the phone numbers column as in step 3 above.

 

 

  • The two tables can now be linked in the data model as a 1:many relationship using the Cust column.

 

 

 

This concludes the document on extracting complex data.