Parsing JSON or XML 139

Other information that can be extracted is as follows:

•Length of characters

•Defined ranges

•Text before/after delimiters

•Text between delimiters

In this way, we can keep only relevant information and rename columns with a more suitable name.

Parsing JSON or XML

We may sometimes find mixed data structures within the same query. In this recipe, we will see how to deal with mixed data structures when single columns within a table contain JSON data structure. The same reasoning would apply to XML structure too.

Getting ready

For this recipe, you need to have Power BI Desktop running on your machine. You need to download the following file in a local folder:

• InternetSales CSV file

In this example, we will refer to the C:\Data folder.

140 Reshaping Your Data

How to do it

Once you open your Power BI Desktop application, you are ready to perform the following steps:

1.Click on Get Data and select the Text/CSV connector.

2.Browse to your local folder where you downloaded the InternetSales CSV file and open it. The following window with a preview of the data will pop up. Click on

Transform Data:

Figure 4.49 – CSV data preview

Parsing JSON or XML 141

3.Browse to the Home tab and click on Use First Row as Headers and observe the data structure as follows:

a)ID column

b)SalesData column containing a JSON data structure with information on sales transactions

You will see the two columns displayed as in the following screenshot:

Figure 4.50 – Mixed data structure

4.Select the SalesData column, browse to the Transform tab, click on Parse, and then on JSON:

Figure 4.51 – Parse JSON

142Reshaping Your Data

5.You will see that instead of the column with JSON data, you now have a list of records that you can expand. You can visualize and select the values that were parsed from the JSON file:

Figure 4.52 – Manage parsed data

6. Select all data and click on OK:

Figure 4.53 – Expanded parsed data