Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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
MarcelBeug
Community Champion
Community Champion

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
maxotek
New Member

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   
MarcelBeug
Community Champion
Community Champion

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)

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

 

Worked perfectly. 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.