Forum Discussion
Extracting Values from Nested Records and Lists
Hello everyone, I am struggling to parse and flatten a list of records with a nested list of records. Ideally, I would like to combine all the information using delimiters into one string of ...
This is exactly how I want my output, with HTML tags.
let
Source = "{
""RenderingInformation"": [
{
""RenderingName"": ""ContentWithImage"",
""RenderingDataSource"": ""/platform/content/location"",
""IsLocal"": true,
""DataSourceFields"": [
{
""FieldName"": ""Title"",
""FieldValue"": ""Title value""
},
{
""FieldName"": ""Image"",
""FieldValue"": ""<image src=\""https://assets.location.com\"">""
},
{
""FieldName"": ""Description"",
""FieldValue"": """"
}
]
},
{
""RenderingName"": ""Cards"",
""RenderingDataSource"": ""/platform/content/location"",
""IsLocal"": true,
""DataSourceFields"": [
{
""FieldName"": ""CardButtonLabel"",
""FieldValue"": ""Read Now""
},
{
""FieldName"": ""Card Description"",
""FieldValue"": ""Description of card.""
}
]
},
{
""RenderingName"": ""Column Splitter"",
""RenderingDataSource"": """",
""IsLocal"": false,
""DataSourceFields"": []
}
]
}",
#"Parsed JSON" = Json.Document(Source),
#"Converted to Table" = Record.ToTable(#"Parsed JSON"),
#"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
#"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"RenderingName", "RenderingDataSource", "IsLocal", "DataSourceFields"}, {"RenderingName", "RenderingDataSource", "IsLocal", "DataSourceFields"}),
#"Expanded DataSourceFields" = Table.ExpandListColumn(#"Expanded Value1", "DataSourceFields"),
#"Expanded DataSourceFields1" = Table.ExpandRecordColumn(#"Expanded DataSourceFields", "DataSourceFields", {"FieldName", "FieldValue"}, {"FieldName", "FieldValue"}),
#"Added Custom" = Table.AddColumn(#"Expanded DataSourceFields1", "Result", each [RenderingName] & ";" & [RenderingDataSource] & ";" & Text.From([IsLocal]) & ";" & [FieldName] & ";" & [FieldValue]),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Result"})
in
#"Removed Other Columns"