Parsing a .json column in Power BI
28,254
Solution 1
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
Solution 2
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.
Solution 3
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"
Related videos on Youtube
Author by
eclairs
Updated on July 09, 2022Comments
-
eclairs almost 2 years
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.
-
Priyanka Mane about 7 yearsI 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.
-
Sergey Lossev about 7 years@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
-
David Ferenczy Rogožan almost 7 yearsHow can you use Python in Power BI?
-
Erikk Ross about 6 yearsThis 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!
-
Zach J. over 5 yearsTHIS! I've read so many answers jumping through hoops when there's a built in function that exists in Power BI!
-
Niklas Rosencrantz over 5 yearsThanks! The above answer really helped.
-
adrien over 5 yearshello from the future powerbi.microsoft.com/en-us/blog/…
-
OneOfThePetes over 2 yearsEXACTLY what I need! Thanks bud!