Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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"
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
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"
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |