Forum Discussion

DreamDelerium's avatar
DreamDelerium
Copper Contributor
Oct 29, 2021
Solved

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 ...
BI & Data Analysis
developer
excel
power bi
  • DreamDelerium's avatar
    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"]
})
)

     

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Oct 29, 2021

DreamDelerium Yes you can. Connect to the table with PQ, Select the column with the JSON code. Tra,nsform, Parse, JSON. Expande fields to new rows. Expend records, OK. Repeat expanding fields and records until all columns contain data only. No M-coding required, just pushing buttons in the correct order.

 

See attached.

DreamDelerium's avatar
DreamDelerium
Copper Contributor
Oct 29, 2021

Riny_van_Eekelen  To make things a little easier to reproduce the error, I have attached a more concise example of the issue.  In this example, the field "sublist" can be:

 

sublist:[]
sublist:[{"option_1":1, "option_2: "aaa", option_3:""}]
sublist:false

In this example, if the sublist is "false" then it does not appear that I can parse the entire column.  

  • DreamDelerium's avatar
    DreamDelerium
    Copper Contributor
    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"]
})
)

     

    • jjtagh's avatar
      jjtagh
      Copper Contributor
      Dec 16, 2021

      DreamDelerium Thanks for taking the time to post your steps here, it's helpful. There doesn't seem to be much out there on parsing messy json data!

Resources