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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
lionzandvliet
Frequent Visitor

Convert JSON data to new columns

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

lionzandvliet_0-1634562620484.png

I need my output like this:
lionzandvliet_2-1634562988272.png

 

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

 

lionzandvliet_3-1634563277732.png

 

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!

 

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

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:

MFelix_0-1634634792787.png

 

MFelix_1-1634634811265.png

  • Split column by delimiter two dots ":"

MFelix_2-1634634875030.pngMFelix_3-1634634886692.png

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
lionzandvliet
Frequent Visitor

@MFelix thanx for your solution.

lionzandvliet_0-1634636368237.png

 

MFelix
Super User
Super User

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:

MFelix_0-1634634792787.png

 

MFelix_1-1634634811265.png

  • Split column by delimiter two dots ":"

MFelix_2-1634634875030.pngMFelix_3-1634634886692.png

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.