Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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 ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Here is an example of how I want the string to look in the column.

lbendlin's avatar
lbendlin
Super User
4 years ago

For example 

 

""FieldValue"": ""<image src=\""https://assets.location.com\"">""

 

does not render correctly in your post because of the HTML tags.  Screenshotting your post will not make it any better.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    This is exactly how I want my output, with HTML tags.

    • lbendlin's avatar
      lbendlin
      Super User
      4 years ago
      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"