Parsing a .json column in Power BI

Question

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.

score 55 · Answer 1 · edited Apr 24 '18 at 10:45

55

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.

edited Apr 24 '18 at 10:45

Erik Oppedijk

3,496
4
31
42

answered Oct 03 '17 at 11:52

Nelson Gomes Matias

1,787
5
22
35

2

This should be the accepted answer! I spent a good hour looking at coding solutions, until I saw this, which did it with a few clicks. Thank you! – Erikk Ross Apr 19 '18 at 12:30
1

THIS! I've read so many answers jumping through hoops when there's a built in function that exists in Power BI! – Zach J. Aug 23 '18 at 14:10
1

Thanks! The above answer really helped. – Niklas Rosencrantz Sep 14 '18 at 06:41
EXACTLY what I need! Thanks bud! – OutOfThisPlanet Sep 21 '21 at 08:17

score 14 · Accepted Answer · answered Nov 29 '16 at 06:05

14

Use Json.Document function like this

let
    ...
    your_table=imported_the_data_directly_from_the_server,
    json=Table.AddColumn(your_table, "NewColName", each Json.Document([JsonResult]))
in
    json

And then expand record to table using Table.ExpandRecordColumn

Or by clicking this button

answered Nov 29 '16 at 06:05

Sergey Lossev

1,430
10
20

1

I followed these steps and got new column contains "Record" as a value in it successfully, but not able to expand the column as it contains null values also. If I apply a filter to remove null value rows then it allow me to expand. How to expand with null values? as other column values are needed where expanded values will be null. – Priyanka Mane Mar 15 '17 at 10:14
@Priyanka Mane, I don't understand your problem. Nulls are successfully expanded to record, containing null values in fieldnames `let t = #table({"col"}, {{[q=1, w=2, e=3]},{null},{[w=22, e=33, r=4]}}), expand = Table.ExpandRecordColumn(t, "col", {"q","w","e","r"}, {"q","w","e","r"}) in expand` – Sergey Lossev May 02 '17 at 16:25

score 1 · Answer 3 · answered Oct 23 '19 at 13:33

Use Json.Document() function to convert string to Json data.

let
    Source = Json.Document(Json.Document(Web.Contents("http://localhost:18091/pools/default/buckets/Aggregation/docs/AvgSumAssuredByProduct"))[json]),
    #"Converted to Table" = Record.ToTable(Source),
    #"Filtered Rows" = Table.SelectRows(#"Converted to Table", each not Text.Contains([Name], "type_")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Name", "AvgSumAssuredByProduct"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Value", type number}})
in
    #"Changed Type"

score -6 · Answer 4 · answered Nov 28 '16 at 14:33

-6

import json
from urllib import urlopen
import string
from UserList import *
l=[]
j=[]
d_base=urlopen('https://api.thingspeak.com/channels/193888/fields/1.json?results=1')
data = json.load(d_base)
for k in data['feeds']:
        name = k['entry_id']
        value = k['field1']
        l.append(name)
        j.append(value)

print l[0]
print j[0]

**this python code may useful for you ** **270 1035 **

answered Nov 28 '16 at 14:33

suria sarath

485
1
4
9

How can you use Python in Power BI? – David Ferenczy Rogožan Jun 20 '17 at 13:18
2

hello from the future https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-august-2018-feature-summary/#python – adrien Jan 09 '19 at 07:51

Parsing a .json column in Power BI

4 Answers4

Use Json.Document function like this