Forum Discussion
fpennisi17
4 years agoHelper III
JSON serialization of a nested data structure
Hi, I need to serialize in JSON, trough Power Query, a structure similar to the following: { "devices": [ { "plant": "Plant A", "device": "Device A1", "measures": [ { "type": "A", "name"...
- 4 years ago
Hi fpennisi17 ,
no worries, here is an idea on how to tackle it:let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCshJzCtRcFTSUXJJLctMTlVwNASyQfyA/PLUIiBtZGBkpGtgrmtgiMwxUorVwac7uCA1NYUo3U4I3U4gRU7E2B0LAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [plant = _t, device = _t, #"type" = _t, name = _t, date_start = _t, date_stop = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"plant", type text}, {"device", type text}, {"type", type text}, {"name", type text}, {"date_start", type date}, {"date_stop", type date}}), AddMeasuresRecord = Table.AddColumn(#"Changed Type", "measures", each [type = [type], name = [name]]), #"Removed Columns" = Table.RemoveColumns(AddMeasuresRecord,{"type", "name"}), #"Grouped Rows" = Table.Group(#"Removed Columns", {"plant", "device", "date_start", "date_stop"}, {{"measures", each _[measures]}}), #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"date_start", "date_stop"}, {{"devices", each Table.ToRecords(Table.RemoveColumns(_, {"date_start", "date_stop"}))}}), #"Reordered Columns" = Table.ReorderColumns(#"Grouped Rows1",{"devices", "date_start", "date_stop"}){0}, Custom1 = Text.FromBinary( Json.FromValue( #"Reordered Columns" )) in Custom1
ImkeF
4 years agoCommunity Champion
Hi fpennisi17 ,
no worries, here is an idea on how to tackle it:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCshJzCtRcFTSUXJJLctMTlVwNASyQfyA/PLUIiBtZGBkpGtgrmtgiMwxUorVwac7uCA1NYUo3U4I3U4gRU7E2B0LAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [plant = _t, device = _t, #"type" = _t, name = _t, date_start = _t, date_stop = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"plant", type text}, {"device", type text}, {"type", type text}, {"name", type text}, {"date_start", type date}, {"date_stop", type date}}),
AddMeasuresRecord = Table.AddColumn(#"Changed Type", "measures", each [type = [type], name = [name]]),
#"Removed Columns" = Table.RemoveColumns(AddMeasuresRecord,{"type", "name"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"plant", "device", "date_start", "date_stop"}, {{"measures", each _[measures]}}),
#"Grouped Rows1" = Table.Group(#"Grouped Rows", {"date_start", "date_stop"}, {{"devices", each Table.ToRecords(Table.RemoveColumns(_, {"date_start", "date_stop"}))}}),
#"Reordered Columns" = Table.ReorderColumns(#"Grouped Rows1",{"devices", "date_start", "date_stop"}){0},
Custom1 = Text.FromBinary( Json.FromValue( #"Reordered Columns" ))
in
Custom1
- fpennisi174 years agoHelper III
Great!
I was trying with grouping, but I wasn't able to control the content of the nested table.
Creating a column which is a record is the key I was missing.
Thank you, this seems really what I need, I'm going to test it.