Json Transformations

[v-lionel-msft]

Scenario:

In Power BI Desktop, when we connect to a .json file, power query editor will transform it automatically if its structure is single.

Sample data1:

Output in power query:

From the APPLIED STEPS we can find that power query editor expands all columns automatically to get this result table.

Extension:

If the json file’s structure is a bit complex and expand all columns which could not get the expected result, how can we transform the file in power query?

Sample data2:

Expected output:

Preface：

If we connect to this .json file and expand all columns directly, it will get this table which is not we wanted:

Operations:

1. From the .json file we can get one list column with a single structure ‘[ID]’ and another list column with a composite ‘[Contents]’

2. Expand the [Contents] column, we will get two rows with Records. In addition, for transforming data more conveniently, we can rename the columns.

3. Add a custom column to extract the ‘values’ list from the .json file:

#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Value", each [Contents][Price]{0}[values])

Note: This step is the combination of the steps that we click ‘Record => Price[list] => Record => values[lists]’, just add a custom column to show it.

4. Add the second column to extract [Products] records’ values, here returns a new record.

#"Added Custom1" = Table.AddColumn(#"Added Custom", "Products", each Record.FromList(

    List.Zip(

        Record.ToList([Contents][Products])),

        {"A_B","B_C"}

        ))

Note: For this step, we need to use List.Zip() function to ‘pack and compress’ a new list due to the column and row transformation, then transform it to a record for next steps.

 

5. Expand the new Record column [Products] to get the list columns.

6. Add the third column to ‘pack and compress’ the [ID] column and [Value] column as new lists, also use List.Zip()

#"Added Custom2" = Table.AddColumn(#"Expanded Products", "Custom", each List.Zip({[ID],[Value]}))

 

7. After the above steps, we have basically completed the data conversion, and the rest is to organize the data. Only keep the two product columns and the Custom column, remove other columns and expand them.

8. Compare with the expected output, we need to filter the two Products column

9. Convert the list column to record column, expand the record column to get the ID column and Value column. If we expand the list directly, we will make the rows expanded down. So need to use Table.TransformColumns() and Record.FromList() to convert.

#"List to Record" = Table.TransformColumns(#"Filtered Rows",{"Custom",each Record.FromList(_,{"ID","values"})}),

#"Expanded Custom1" = Table.ExpandRecordColumn(#"List to Record", "Custom", {"ID", "values"}, {"Custom.ID", "Custom.values"})

10. Reorder the columns, rename and change the data type through the GUI to get the expected result.

Main Functions reference:

1. List.Zip()
2. Record.ToList()
3. Record.FromList()
4. Table.Transformcolumns()

Author: Yingjie Li

Reviewer: Ula Huang, Kerry Wang

Json Sample.zip