cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
roelf Regular Visitor
Regular Visitor

Tranform JSON to table

Hi. I'm in need of help to transform data from an object into a table. I've tried Parsing to JSON, splitting to columns, etc. but I cannot get the data to the format i need it in. When parsing to JSON it creates OBJECTS, and I need it to create list, which i can then split to rows, and then extract the data from the object.

 

The 2 columns in my dataset are as follows:

ID	        DATA
#13:11245	{"#13:200422":{"sortname":"AkaranaDan Ben Hannah20B - DBH","commodity":"Grapes","cultivarname2":"Grapes - Dan Ben Hannah","cultivarname":"Dan Ben Hannah","cultivar_id":"#13:198085","farmname":"Akarana","farm_id":"#13:199769","status":"active","block":"#13:199811","size":1.45,"rid":"#13:200422","name":"20B - DBH"},"#13:200425":{"sortname":"AkaranaFlame4 - FLM","commodity":"Grapes","cultivarname2":"Grapes - Flame","cultivarname":"Flame","cultivar_id":"#13:38657","farmname":"Akarana","farm_id":"#13:199769","status":"active","block":"#13:199814","size":1.5,"rid":"#13:200425","name":"4 - FLM"},"#13:200429":{"sortname":"AkaranaFlame7 - FLS","commodity":"Grapes","cultivarname2":"Grapes - Flame","cultivarname":"Flame","cultivar_id":"#13:38657","farmname":"Akarana","farm_id":"#13:199769","status":"active","block":"#13:199818","size":1.7,"rid":"#13:200429","name":"7 - FLS"},"#13:200421":{"sortname":"AkaranaStarlight20A - STL","commodity":"Grapes","cultivarname2":"Grapes - Starlight","cultivarname":"Starlight","cultivar_id":"#13:200329","farmname":"Akarana","farm_id":"#13:199769","status":"active","block":"#13:199810","size":1.33,"rid":"#13:200421","name":"20A - STL"},"#13:200412":{"sortname":"AkaranaTawney Seedless12 - TNY","commodity":"Grapes","cultivarname2":"Grapes - Tawney Seedless","cultivarname":"Tawney Seedless","cultivar_id":"#13:198094","farmname":"Akarana","farm_id":"#13:199769","status":"active","block":"#13:199801","size":1.38,"rid":"#13:200412","name":"12 - TNY"},"#13:200430":{"sortname":"AkaranaTawney Seedless8 - TNY","commodity":"Grapes","cultivarname2":"Grapes - Tawney Seedless","cultivarname":"Tawney Seedless","cultivar_id":"#13:198094","farmname":"Akarana","farm_id":"#13:199769","status":"active","block":"#13:199819","size":1.7,"rid":"#13:200430","name":"8 - TNY"},"#13:200485":{"sortname":"De HoopTawney SeedlessW7 - TNY","commodity":"Grapes","cultivarname2":"Grapes - Tawney Seedless","cultivarname":"Tawney Seedless","cultivar_id":"#13:198094","farmname":"De Hoop","farm_id":"#13:199771","status":"active","block":"#13:199874","size":1.32,"rid":"#13:200485","name":"W7 - TNY"}}

and I need to get it looking something like this;

ID	        DATA
#13:11245	#13:200422":{"sortname":"AkaranaDan Ben Hannah20B - DBH","commodity":"Grapes","cultivarname2":"Grapes - Dan Ben Hannah","cultivarname":"Dan Ben Hannah","cultivar_id":"#13:198085","farmname":"Akarana","farm_id":"#13:199769","status":"active","block":"#13:199811","size":1.45,"rid":"#13:200422","name":"20B - DBH"}
#13:11245	#13:200425":{"sortname":"AkaranaFlame4 - FLM","commodity":"Grapes","cultivarname2":"Grapes - Flame","cultivarname":"Flame","cultivar_id":"#13:38657","farmname":"Akarana","farm_id":"#13:199769","status":"active","block":"#13:199814","size":1.5,"rid":"#13:200425","name":"4 - FLM"}
#13:11245	#13:200429":{"sortname":"AkaranaFlame7 - FLS","commodity":"Grapes","cultivarname2":"Grapes - Flame","cultivarname":"Flame","cultivar_id":"#13:38657","farmname":"Akarana","farm_id":"#13:199769","status":"active","block":"#13:199818","size":1.7,"rid":"#13:200429","name":"7 - FLS"}
#13:11245	#13:200421":{"sortname":"AkaranaStarlight20A - STL","commodity":"Grapes","cultivarname2":"Grapes - Starlight","cultivarname":"Starlight","cultivar_id":"#13:200329","farmname":"Akarana","farm_id":"#13:199769","status":"active","block":"#13:199810","size":1.33,"rid":"#13:200421","name":"20A - STL"}
#13:11245	#13:200412":{"sortname":"AkaranaTawney Seedless12 - TNY","commodity":"Grapes","cultivarname2":"Grapes - Tawney Seedless","cultivarname":"Tawney Seedless","cultivar_id":"#13:198094","farmname":"Akarana","farm_id":"#13:199769","status":"active","block":"#13:199801","size":1.38,"rid":"#13:200412","name":"12 - TNY"}
#13:11245	#13:200430":{"sortname":"AkaranaTawney Seedless8 - TNY","commodity":"Grapes","cultivarname2":"Grapes - Tawney Seedless","cultivarname":"Tawney Seedless","cultivar_id":"#13:198094","farmname":"Akarana","farm_id":"#13:199769","status":"active","block":"#13:199819","size":1.7,"rid":"#13:200430","name":"8 - TNY"}
#13:11245	#13:200485":{"sortname":"De HoopTawney SeedlessW7 - TNY","commodity":"Grapes","cultivarname2":"Grapes - Tawney Seedless","cultivarname":"Tawney Seedless","cultivar_id":"#13:198094","farmname":"De Hoop","farm_id":"#13:199771","status":"active","block":"#13:199874","size":1.32,"rid":"#13:200485","name":"W7 - TNY"}

 

I actually need an additional column per individual element but I recon if i can get it to the above format I can split to columns again, or extract values

 

 

If there is someone who can assist, it will be much appreciated.

 

Thanks

Roelf

 

1 ACCEPTED SOLUTION

Accepted Solutions
roelf Regular Visitor
Regular Visitor

Re: Tranform JSON to table

For those interested, here is what i did in the end.

 

Using 'search and replace' I changed the JSON. With various steps I moved the value i want to split into rows on, into the OBJECT, so that I end up with an array of objects. This allows me to "split to rows", ending up with individual OBJECTS, and then i extract the data i need from the OBJECT.

 

let
    Source = Sql.Databases("XXXX.database.windows.net"),
    XXXX = Source{[Name="XXXX"]}[Data],
    dbo_table = XXXXX{[Schema="dbo",Item="dbo_table"]}[Data],
    #"Added Prefix" = Table.TransformColumns(dbo_table, {{"zones", each "[" & _, type text}}),
    #"Added Suffix" = Table.TransformColumns(#"Added Prefix", {{"zones", each _ & "]", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Added Suffix","},","}},{",Replacer.ReplaceText,{"zones"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","{""#","{""zonerid"":""#",Replacer.ReplaceText,{"zones"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",":{""s",",""s",Replacer.ReplaceText,{"zones"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","}}","}",Replacer.ReplaceText,{"zones"}),
    #"Parsed JSON" = Table.TransformColumns(#"Replaced Value3",{{"zones", Json.Document}}),
    #"Expanded zones" = Table.ExpandListColumn(#"Parsed JSON", "zones"),
    #"Expanded zones1" = Table.ExpandRecordColumn(#"Expanded zones", "zones", {"zonerid"}, {"zones.zonerid"})
in
    #"Expanded zones1"
3 REPLIES 3
Community Support Team
Community Support Team

Re: Tranform JSON to table

hi, @roelf

After my research , I'm afraid it's hard to achieve in Power BI for now.

It's easy to achieve it that use this code to transform JSON to table like below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("5dVNT8IwGADgv7LMK5p+bKzjBiHIAb2MxBhGSIUqC/sg29Cg4b9bCo62FFASDupu7fuR9kn2djCwryBuQIgc1xKfXbOtj1DsIgAchEK7wddFlpcpTRhfhXZzRnOa0jZNrRZLrS5NUzpFoGVdW+1WN7RroT3OkiSbROVSFNzmdM6KTWARl9ErzdfNkBRc1yr99rJF8pGcUTQRKeJCPgHEFeFnmif6yauAWuN7dV+EipKWi0JE6Jg3Z2L3Kc7GMzmdQLhJj97X7eGN4/JVLvX8IuTb1RkkqFVNSnMPS3divuHwqk7v7mxe0cSsuh+SYTCpu97lLR3V0kjpqpQViQLpn4D0RFXwZyGJCumZIH0VsiJRIOFhyKCkeRy9TEsEmrw26PfO5qxamUnN4ZF6Iby90EVdgeqKsQkW6j97xSPTwiNjtU/fUra0AsYmMSsKiHiD/v3j2b5aP7PysSR9sPrOxa2BNlgxMVhDbbDupGRqDL5NTf6hNPRPTwsMVGhiciam96vNrG6WzbU7P3i/AXp79oPQHvwRtKe9bxgZpIn2wO2kVit7OPwE", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"ID             " = _t, #" DATA" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID             ", type text}, {" DATA", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Json.Document([#" DATA"])),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"#13:200422", "#13:200425", "#13:200429", "#13:200421", "#13:200412", "#13:200430", "#13:200485"}, {"#13:200422", "#13:200425", "#13:200429", "#13:200421", "#13:200412", "#13:200430", "#13:200485"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Expanded Custom", {"ID             ", " DATA"}, "Attribute", "Value"),
    #"Expanded Value" = Table.ExpandRecordColumn(#"Unpivoted Columns", "Value", {"sortname", "commodity", "cultivarname2", "cultivarname", "cultivar_id", "farmname", "farm_id", "status", "block", "size", "rid", "name"}, {"sortname", "commodity", "cultivarname2", "cultivarname", "cultivar_id", "farmname", "farm_id", "status", "block", "size", "rid", "name"})
in
    #"Expanded Value"

13.JPG

For the format you require, it may need to be done in the data source and it's hard to achieve in Power BI.

 

Best Regards,

Lin

 

 

 

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
roelf Regular Visitor
Regular Visitor

Re: Tranform JSON to table

Thanks a lot Lin

 

I have this problem with different columns in the dataset also, so though the code looks great, it's gonna be too much of a hassle to convert etc. I think our solution lies in writing the data differently.

 

Thanks for your effort, much appreciated.

 

Roelf

roelf Regular Visitor
Regular Visitor

Re: Tranform JSON to table

For those interested, here is what i did in the end.

 

Using 'search and replace' I changed the JSON. With various steps I moved the value i want to split into rows on, into the OBJECT, so that I end up with an array of objects. This allows me to "split to rows", ending up with individual OBJECTS, and then i extract the data i need from the OBJECT.

 

let
    Source = Sql.Databases("XXXX.database.windows.net"),
    XXXX = Source{[Name="XXXX"]}[Data],
    dbo_table = XXXXX{[Schema="dbo",Item="dbo_table"]}[Data],
    #"Added Prefix" = Table.TransformColumns(dbo_table, {{"zones", each "[" & _, type text}}),
    #"Added Suffix" = Table.TransformColumns(#"Added Prefix", {{"zones", each _ & "]", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Added Suffix","},","}},{",Replacer.ReplaceText,{"zones"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","{""#","{""zonerid"":""#",Replacer.ReplaceText,{"zones"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",":{""s",",""s",Replacer.ReplaceText,{"zones"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","}}","}",Replacer.ReplaceText,{"zones"}),
    #"Parsed JSON" = Table.TransformColumns(#"Replaced Value3",{{"zones", Json.Document}}),
    #"Expanded zones" = Table.ExpandListColumn(#"Parsed JSON", "zones"),
    #"Expanded zones1" = Table.ExpandRecordColumn(#"Expanded zones", "zones", {"zonerid"}, {"zones.zonerid"})
in
    #"Expanded zones1"