JSON parsing with changing keys

Question

when I try to parse answers in JSON, I can not receive the data I need as the key (Row 1 - **ecf2f5d0c94747a595765cca40443262_2_1**, Row 2 - **ecf2f5d0c94747a595765cca40443262_2_123**) is always changing.

Examples:

JSON Row 1

{
  "event": {
    "problem_id": "block-v1:SkillFactory+ADMIN-FREE+2021+type@problem+block@ecf2f5d0c94747a595765cca40443262",
    "answers": {
      "**ecf2f5d0c94747a595765cca40443262_2_1**": "34"
    },
    "submission": {
      "ecf2f5d0c94747a595765cca40443262_2_1": {
        "question": "",
        "answer": "34",
        "response_type": "numericalresponse",
        "input_type": "formulaequationinput",
        "correct": false,
        "variant": "",
        "group_label": ""
      }
    }
  }
}

JSON Row 2

{
  "event": {
    "problem_id": "block-v1:SkillFactory+ADMIN-FREE+2021+type@problem+block@ecf2f5d0c94747a595765cca40443262",
    "answers": {
      "**ecf2f5d0c94747a595765cca40443262_2_123**": "3433"
    },
    "submission": {
      "ecf2f5d0c94747a595765cca40443262_2_123": {
        "question": "",
        "answer": "3433",
        "response_type": "numericalresponse",
        "input_type": "formulaequationinput",
        "correct": false,
        "variant": "",
        "group_label": ""
      }
    }
  }
}

the ideal solution should be different columns.

I found this solution but it did not help.

Answer 1

Try

let Source = Json.Document(File.Contents("C:\Temp\a.json")),
ListToTable = Table.FromRecords(Source),
#"Added Custom1" = Table.AddColumn(ListToTable, "Custom.1", each Record.FieldNames([event][answers]){0}),
#"Added Custom" = Table.AddColumn(#"Added Custom1", "Custom", each Record.Field([event][answers],[Custom.1]))
in  #"Added Custom"

which gets you

[ { "event": { "problem_id": "block-v1:SkillFactory+ADMIN-FREE+2021+type@problem+block@ecf2f5d0c94747a595765cca40443262", "answers": { "ecf2f5d0c94747a595765cca40443262_2_1": "34" }, "submission": { "ecf2f5d0c94747a595765cca40443262_2_1": { "question": "", "answer": "34", "response_type": "numericalresponse", "input_type": "formulaequationinput", "correct": false, "variant": "", "group_label": "" } } } }, { "event": { "problem_id": "block-v1:SkillFactory+ADMIN-FREE+2021+type@problem+block@ecf2f5d0c94747a595765cca40443262", "answers": { "ecf2f5d0c94747a595765cca40443262_2_123": "3433" }, "submission": { "ecf2f5d0c94747a595765cca40443262_2_123": { "question": "", "answer": "3433", "response_type": "numericalresponse", "input_type": "formulaequationinput", "correct": false, "variant": "", "group_label": "" } } } } ]