Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
roelf
Helper II
Helper II

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

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
v-lili6-msft
Community Support
Community Support

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.

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

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"

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.