Power Query Nested JSON (Registry-> Record List -> Registry)

Hi Syndicate_Admin , one thing that might be causing you an issue is that you have an extra double quote (") in the JSON example you gave which is causing the JSON to appear invalid. It's after "How do you rate your overall experience?". Use something like JSON Formatter which would highlight where the glitch is

https://jsonformatter.curiousconcept.com/

Once that's resolved, you should be able to process that JSON column in Power Query using the following steps

1. Use Text.AfterDelimiter followed by clean/trim to get rid of the [List]: prefix
2. Add opening and closing square brackets to that, so that your JSON is converted into a JSON Array
3. Use Json.Document to interpret the text as JSON
4. Expand to new rows then expand into columns

I have copied the Power Query M below to show what I mean, of course my data source is just manually entered data from your original post

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7ZNfa9swFMW/ykVPG6SpLEv+9zLKGGSw0a0UBovDUO2bWNS2gizXNaPffVK80i3JAntuXoS5ujr3pyOf5ZIsP6nOrrK8zdufOfnaY2eVbj+WOckgJ0EU0iiMBI94LIKczOClZ+pY6AFqdY/1CNIgjLoHq8FgoZsG29IXDFiUDXSDskWl2g3g4xaNwrZA3yuh0HWNctPjOzchJ1dtN6D5DUB9yc2y4xankpHtxn0+zU4RRzGjNNwdPgKMshthkB0o6wkaee9IKoSi8tqg1xO2u8O2t2gOscJd5T+ZJhfZP5hKvTPPSIvTdP2ARtb1H27tc4R/MQx4t9am+eEUnMmnYAKWpGkcMcbDIzDfqvEZZqMeEPrOeaM6mHQPvfD9amelhDu31u4Ge/ZYfLRrhXV5ioo5olSkPKHHnu3G/SPXvXsuJwpvFl8uP1+9v7y+XXy4eXtA5Lb2ACpVluiUnsiMMMqCi4BeUH4bRFmQZmE0FzH97rcSspqdM3HOxOvOBE8ywedJEvlMxOKciXMmXn0mhMgonzMufCY4JavVLw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [questions_answers = _t, submitted_on = _t, spent_time = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"questions_answers", type text}, {"submitted_on", type datetime}, {"spent_time", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "question_answers_clean", each Text.AfterDelimiter([questions_answers],":")),
#"Cleaned Text" = Table.TransformColumns(#"Added Custom",{{"question_answers_clean", Text.Clean, type text}}),
#"Added Custom2" = Table.AddColumn(#"Cleaned Text", "json_object", each "[" & [question_answers_clean] & "]"),
#"Added Custom1" = Table.AddColumn(#"Added Custom2", "Custom", each Json.Document([json_object])),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom1", "Custom"),
#"Expanded Custom1" = Table.ExpandRecordColumn(#"Expanded Custom", "Custom", {"QuestionId", "Question", "Answer", "type"}, {"QuestionId", "Question", "Answer", "type"})
in
#"Expanded Custom1"

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Thanks for the reply:

Actually this is the JSON when validating it on the page I do not see any error:

[
  {
    "question_answers": [
      {
        "QuestionId": "16306365464751",
        "Question": "¿Qué tan probable es que recomiendes tu experiencia de cambio de equipo a un colega?",
        "Answer": "10",
        "type": "range"
      },
      {
        "QuestionId": "16306366720030",
        "Question": "¿Qué tan fácil fue realizar el cambio de tu equipo?",
        "Answer": "5",
        "type": "range"
      },
      {
        "QuestionId": "16306365464752",
        "Question": "¿Cómo calificas tu experiencia en general? ",
        "Answer": 5,
        "type": "webform_rating"
      },
      {
        "QuestionId": "16312899762243",
        "Question": "¿Por qué nos das esta calificación?",
        "Answer": "La persona que me ayudo con el cambio de mi equipo fue muy amable y el cambio fue muy rápido ",
        "type": "textfield"
      },
      {
        "QuestionId": "16327629594800",
        "Question": "Tipo de RollOut (HP/MAC/OTRO)",
        "Answer": "HP",
        "type": "hidden"
      }
    ],
    "submitted_on": "2021-10-12T15:33:06.479Z",
    "submitted_by": "0",
    "spent_time": 49
  },
  {
    "question_answers": [
      {
        "QuestionId": "16306365464751",
        "Question": "¿Qué tan probable es que recomiendes tu experiencia de cambio de equipo a un colega?",
        "Answer": "10",
        "type": "range"
      },
      {
        "QuestionId": "16306366720030",
        "Question": "¿Qué tan fácil fue realizar el cambio de tu equipo?",
        "Answer": "5",
        "type": "range"
      },
      {
        "QuestionId": "16306365464752",
        "Question": "¿Cómo calificas tu experiencia en general? ",
        "Answer": 5,
        "type": "webform_rating"
      },
      {
        "QuestionId": "16312899762243",
        "Question": "¿Por qué nos das esta calificación?",
        "Answer": "La anticipación y los webinars para respaldo fueron útiles ",
        "type": "textfield"
      },
      {
        "QuestionId": "16327629594800",
        "Question": "Tipo de RollOut (HP/MAC/OTRO)",
        "Answer": "HP",
        "type": "hidden"
      }
    ],
    "submitted_on": "2021-10-12T15:33:06.777Z",
    "submitted_by": "0",
    "spent_time": 95
  },
  {
    "question_answers": [
      {
        "QuestionId": "16306365464751",
        "Question": "¿Qué tan probable es que recomiendes tu experiencia de cambio de equipo a un colega?",
        "Answer": "10",
        "type": "range"
      },
      {
        "QuestionId": "16306366720030",
        "Question": "¿Qué tan fácil fue realizar el cambio de tu equipo?",
        "Answer": "5",
        "type": "range"
      },
      {
        "QuestionId": "16306365464752",
        "Question": "¿Cómo calificas tu experiencia en general? ",
        "Answer": 5,
        "type": "webform_rating"
      },
      {
        "QuestionId": "16312899762243",
        "Question": "¿Por qué nos das esta calificación?",
        "Answer": "Por qué el ingeniero que me ayudó sabía bien lo que hacia",
        "type": "textfield"
      },
      {
        "QuestionId": "16327629594800",
        "Question": "Tipo de RollOut (HP/MAC/OTRO)",
        "Answer": "HP",
        "type": "hidden"
      }
    ],
    "submitted_on": "2021-10-12T16:13:38.086Z",
    "submitted_by": "0",
    "spent_time": 64
  }
]