cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ohsocreamy
New Member

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:

idnameCriteria1Criteria2Criteria3
ABC1ABC Corp   
DEF2XYZ Corp Pte Ltd.5010John Howard
XYZ3NSK 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!!

1 ACCEPTED SOLUTION

Accepted Solutions
MarcelBeug Community Champion
Community Champion

Re: Parsing Embedded JSON

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. Smiley LOL

Specializing in Power Query Formula Language (M)

View solution in original post

3 REPLIES 3
MarcelBeug Community Champion
Community Champion

Re: Parsing Embedded JSON

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. Smiley LOL

Specializing in Power Query Formula Language (M)

View solution in original post

ohsocreamy
New Member

Re: Parsing Embedded JSON

MarcelBeug thank-you so much!  This made my day.  

 

Worked perfectly. 🙂

maxotek
New Member

Re: Parsing Embedded JSON

Here's a different take on the solution using Tool Slick.

 

  1. Take Set #2 and paste it in the input of JSON Formatter.
  2. Select Format Embedded JSON in settings.
  3. Click on Format
  4. Take Set #1 and paste it in the input of JSON to CSV Converter
  5. Copy the subset of output of Set #2:-
    "Criteria1": "50",
    "Criteria2": "10",
    "Criteria3": "John Howard"
  6. 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"
        }
    ]
  7. 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,,,

 

 

ABC1ABC Corp   
DEF2XYZ Corp Pte Ltd.5010John Howard
XYZ3NSK Kabushiki Gaisha   

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors