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
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?
Solved! Go to Solution.
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
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.
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
63 | |
54 | |
27 | |
17 | |
13 |