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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
fpennisi17
Helper III
Helper 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": "Power"
},
{
"type": "A",
"name": "Speed"
}
]
},
{
"plant": "Plant B",
"device": "Device B1",
"measures": [
{
"type": "B",
"name": "Power"
}
]
}
],
"date_start": "2022-07-01",
"date_stop": "2022-07-02"
}

 

I already serialized, using Json.FromValue a record (or a set of records), but I do not understand how to handle the nested structure.

Any suggestion?

1 ACCEPTED SOLUTION
ImkeF
Super User
Super User

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



Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

4 REPLIES 4
ImkeF
Super User
Super User

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



Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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.

ImkeF
Super User
Super User

Hi @fpennisi17 ,
you can expand it all out into 1 table like so:

let
    Source = "{#(lf)""devices"": [#(lf){#(lf)""plant"": ""Plant A"",#(lf)""device"": ""Device A1"",#(lf)""measures"": [#(lf){#(lf)""type"": ""A"",#(lf)""name"": ""Power""#(lf)},#(lf){#(lf)""type"": ""A"",#(lf)""name"": ""Speed""#(lf)}#(lf)]#(lf)},#(lf){#(lf)""plant"": ""Plant B"",#(lf)""device"": ""Device B1"",#(lf)""measures"": [#(lf){#(lf)""type"": ""B"",#(lf)""name"": ""Power""#(lf)}#(lf)]#(lf)}#(lf)],#(lf)""date_start"": ""2022-07-01"",#(lf)""date_stop"": ""2022-07-02""#(lf)}",
    #"Parsed JSON" = Json.Document(Source),
    Custom1 = Table.FromRecords({ #"Parsed JSON" }),
    #"Expanded devices" = Table.ExpandListColumn(Custom1, "devices"),
    #"Expanded devices1" = Table.ExpandRecordColumn(#"Expanded devices", "devices", {"plant", "device", "measures"}, {"plant", "device", "measures"}),
    #"Expanded measures" = Table.ExpandListColumn(#"Expanded devices1", "measures"),
    #"Expanded measures1" = Table.ExpandRecordColumn(#"Expanded measures", "measures", {"type", "name"}, {"type", "name"})
in
    #"Expanded measures1"



Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

I'm sorry, probably my request was not clear.

I have to produce that JSON (I need to pass it to the body of a post API request).

Hence what I need is to understand how to structure a table with nested elements that then can be serialized in JSON using a command similare to Json.FromValue

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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