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 ...
Please repost your desired outcome. Here is an example of a standard JSON parser.
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"})
in
#"Expanded DataSourceFields1"which results in
For the desired output, I want to have all of the information combined into a single column using delimiters (see above example format). The data source can have any number of records, so using this method would create many unneccessary columns.
Please repost your expected output. It got mangled by the forum due to the HTML tags in your data. Post a screenshot of the expected outcome.
Here is an example of how I want the string to look in the column.