Convert JSON data to new columns

[lionzandvliet]

Im trying to convert a column with JSON-data to new columns. This is my input:

I need my output like this:

I'm trying to convert my 'assessments' column to JSON in the Power Query Editor, but not getting the right outcome:

let
Source = objects,
#"Parsed JSON" = Table.TransformColumns(Source,{{"assessments", Json.Document}}),
#"Expanded assessments" = Table.ExpandRecordColumn(#"Parsed JSON", "assessments", {"36", "43"}, {"product_id", "value"})
in
#"Expanded assessments"

Thanks in advance!

[lionzandvliet]

@MFelix thanx for your solution.

[MFelix · Accepted solution]

Hi @lionzandvliet ,

If you have the information has you present in the first image you just need to make some addtional steps:

• Remove the { }
• Split columns to rows using the comma +space ", " separator:

• Split column by delimiter two dots ":"

Check PBIX file attach, it's not based on JSON but you only need the steps after "opening" the JSON that is what I show here.