cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Highlighted
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"

View solution in original post

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

Highlighted
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"

View solution in original post

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors