Forum Discussion

Helper II

7 years ago

Solved

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

Need Help

roelf

7 years ago

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

v-lili6-msft

Community Support

7 years ago

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"

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

roelf

Helper II

7 years ago

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

Helper II

7 years ago

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

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"

Forum Discussion

Tranform JSON to table

3 Replies

Related content

Tranform the data

Error ingesting json files in warehouse table

ELT JSON / Semi structured

JSON Theme Tables and Matrixes

Excel power query - Tranform JSON data correctly