Extracting Values from Nested Records and Lists

[bienthused]

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)

[lbendlin]

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

[bienthused]

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]

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.

[bienthused]

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

[lbendlin]

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.

[bienthused]

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

[lbendlin]

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"