Forum Discussion
Parsing Embedded JSON
Hi All.
Hit a bit of a snag with this one... Was wondering if anyone could point me in the right direction?
I have two JSON datasets:
Set #1:
[
{
"id": "ABC1",
"name": "ABC Corp"
},
{
"id": "DEF2",
"name": "XYZ Corp Pte Ltd."
},
{
"id": "XYZ3",
"name": "NSK Kabushiki Gaisha"
}
]
Set #2:
[
{
"id": "DEF2",
"value": "{\"fields\":{\"Criteria1\":\"50\",\"Criteria2\":\"10\",\"Criteria3\":\"John Howard\"}}"
}
]
Basically, I need to take the "fields" out of "Value" in set #2, and break them out into their own table. Then merge this table with the relevant row of a table created from Set #1.
The result should be something like:
| id | name | Criteria1 | Criteria2 | Criteria3 |
| ABC1 | ABC Corp | |||
| DEF2 | XYZ Corp Pte Ltd. | 50 | 10 | John Howard |
| XYZ3 | NSK Kabushiki Gaisha |
I appreciate that this is quite complex to handle client side - but unfortunately I have no option.
I'm comfortable merging the datasets once I have the second one cleaned up... Unfortunately, as a novice with this language, everything I have tried to get to "clean" it has failed.
Thanks for your help!!
This is pretty straightforward: copy the JSON into a new query in Power BI, press some buttons until it is a table, repeat for the second JSON and finally merge the 2 tables,
This 3 minute video shows it all. So just go ahead and start clicking. :smileylol:
3 Replies
This is pretty straightforward: copy the JSON into a new query in Power BI, press some buttons until it is a table, repeat for the second JSON and finally merge the 2 tables,
This 3 minute video shows it all. So just go ahead and start clicking. :smileylol:
MarcelBeug thank-you so much! This made my day.
Worked perfectly. :-)
Here's a different take on the solution using Tool Slick.
- Take Set #2 and paste it in the input of JSON Formatter.
- Select Format Embedded JSON in settings.
- Click on Format
- Take Set #1 and paste it in the input of JSON to CSV Converter
- Copy the subset of output of Set #2:-
"Criteria1": "50", "Criteria2": "10", "Criteria3": "John Howard"
- Paste it in the JSON to CSV Converter inside the object with id "DEF2", below the name property. So that the input becomes:-
[ { "id": "ABC1", "name": "ABC Corp" }, { "id": "DEF2", "name": "XYZ Corp Pte Ltd.", "Criteria1": "50", "Criteria2": "10", "Criteria3": "John Howard" }, { "id": "XYZ3", "name": "NSK Kabushiki Gaisha" } ] - Hit Convert
You will get the same output:-
id,name,Criteria1,Criteria2,Criteria3 ABC1,ABC Corp,,, DEF2,XYZ Corp Pte Ltd.,50,10,John Howard XYZ3,NSK Kabushiki Gaisha,,,
ABC1 ABC Corp DEF2 XYZ Corp Pte Ltd. 50 10 John Howard XYZ3 NSK Kabushiki Gaisha
