Forum Discussion

fpennisi17's avatar
fpennisi17
Helper III
4 years ago
Solved

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"...
  • ImkeF's avatar
    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