Menu
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.
605
power bi has default JSON document connector you can use that to import your saved JSON strings. 2. once imported you get access to query editor where you can perform number of data manipulation tasks and use it.
If you need to parse a JSON string that returns a dictionary, then you can use the json. loads() method. If you need to parse a JSON file that returns a dictionary, then you can use the json. load() method.
Parse JSON text JavaScript Object Notation (JSON) is a common data format, and you can import it into Excel. To transform the SalesPerson column from text strings to a structured Record column: Select the SalesPerson column. Select Transform > Parse > JSON.
There is an easier way to do it, in the Query Editor on the column you want to read as a 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.
136
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
