Forum Discussion
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 ...
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 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:falseIn this example, if the sublist is "false" then it does not appear that I can parse the entire column.
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 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!
