Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!!
Solved! Go to Solution.
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.
Here's a different take on the solution using Tool Slick.
"Criteria1": "50", "Criteria2": "10", "Criteria3": "John Howard"
[ { "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" } ]
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 |
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.
MarcelBeug thank-you so much! This made my day.
Worked perfectly. 🙂