Power Query Editor unpack JSON

Hello. I have a spreadsheet with 5 columns. One of those columns contains a json array (the array also has nested arrays). Is it possible to use Power Query Editor to parse the json array in each ...

Example.xlsx11 KB

BI & Data Analysis

developer

excel

power bi

DreamDelerium

Oct 30, 2021

I found this worked. I first created a custom column. Then added this as the custom forumla

=if [response_json.sublist] <> false then
Table.ToRecords(Table.FromRecords([response_json.sublist]))
else
Table.ToRecords(
Table.FromRecords({
[option_1 = "null", option_2= "null", option_3= "null"]
})
)

DreamDelerium

Copper Contributor

Oct 29, 2021

Riny_van_EekelenThank you! Your steps have gotten me part way there. But, I can't seem to follow your steps on the sub arrays. These are the steps I have taken:

1. Open Excel and Go To Data Tab

2. Select Get Data->From File->From Workbook (and select the workbook with the raw data)

3. In the Navigator window, select the Excel tab with the data and click the "Transform Data" button. This opens the Power Query Editor

4. Select the column with JSON data and right click, selecting Transform->Json. This converts each record as a "list"

5. Click the double arrow icon on the top right corner of the column header and select "Expand to new rows". This converts the fields from "list" to "Record"

6. Click the double arrow icon again, which opens a dialog with all the fields in the json array, and make sure all columns are selected an click OK.

7. This expands all the json data into new columns. Find the column with the second layer of json data and select it.

8. Each item in this column says "List". But, when I right click, there is no "Transform" option.

So, how do I do this for the second layer of JSON data?

DreamDelerium

Copper Contributor

Oct 29, 2021

I think I found the issue. On occasion, the sub array will have sublist:false instead of sublist:[]. Is there a way to skip the json formatting process for the sub array if it is "False"? Or, is this something I would need to do before bringing into Excel PQ?

Forum Discussion

Power Query Editor unpack JSON

Resources