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 text per each row, but ran into errors using Extract Values and have not had any luck using Text Combine. Field values can be blank, and there is no standardization in number of records in a list. Below is an example of the column in question for one row:

 

 

{
  "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": []
    }
  ]
}

 

 

 Desired output: ContentWithImage;/platform/content/location;true;Title - Title value, Image - https://assets.location.com, Description - |Cards;/platform/content/location; ....(etc)

Need Help

7 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    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

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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.

      • lbendlin's avatar
        lbendlin
        Super User
        4 years ago

        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.