Parsing a .json column in Power BI

json parsing powerbi
eclairs picture eclairs · Nov 28, 2016 · Viewed 18k times · Source

I want to parse a .json column through Power BI. I have imported the data directly from the server and have a .json column in the data along with other columns. Is there a way to parse this json column?

Example:

       Key      IDNumber    Module      JsonResult  
       012      200         Dine        {"CategoryType":"dining","City":"mumbai"',"Location":"all"} 
       97       303         Fly         {"JourneyType":"Return","Origin":"Mumbai (BOM)","Destination":"Chennai (MAA)","DepartureDate":"20-Oct-2016","ReturnDate":"21-Oct-2016","FlyAdult":"1","FlyChildren":"0","FlyInfant":"0","PromoCode":""} 
       276      6303        Stay        {"Destination":"Clarion Chennai","CheckInDate":"14-Oct-2016","CheckOutDate":"15-Oct-2016","Rooms":"1","NoOfPax":"2","NoOfAdult":"2","NoOfChildren":"0"}

I wish to retain the other columns and also get the simplified parsed columns.

Answer

Nelson Gomes Matias picture Nelson Gomes Matias · Oct 3, 2017

There is an easier way to do it, in the Query Editor on the column you want to read as a json:

  • Right click on the column
  • Select Transform>JSON

then the column becomes a Record that you can split in every property of the json using the button on the top right corner.

split columns

Related questions

Why can't Python parse this JSON data?

I have this JSON in a file: { "maps": [ { "id": "blabla", "iscategorical": "0" }, { "id": "blabla", "iscategorical": "0" } ], "masks": [ "id": "valore" ], "om_points": "value", "parameters": [ "id": "valore" ] } I wrote this script to print all of the JSON data: import json from pprint import pprint …

Read More
Parse JSON in JavaScript?

I want to parse a JSON string in JavaScript. The response is something like var response = '{"result":true,"count":1}'; How can I get the values result and count from this?

Read More
How to parse JSON in Java

I have the following JSON text. How can I parse it to get the values of pageName, pagePic, post_id, etc.? { "pageInfo": { "pageName": "abc", "pagePic": "http://example.com/content.jpg" }, "posts": [ { "post_id": "123456789012_123456789012", "actor_id": "1234567890", "picOfPersonWhoPosted": "http://example.com/photo.…

Read More