Parsing JSON column to different columns

Hi Anonymous ,

 

try this (with the help of chatgpt):

let
    // or path to a json file
    Source = 
    "  
    [
        [
            {""Question"":""Number of controls"",""QuestionOrder"":1,""Response"":""15""},
            {""Question"":""Number of controls already implemented"",""QuestionOrder"":2,""Response"":""implemented""},
            {""Question"":""Is the considered material for the implementation of the specific controls"",""QuestionOrder"":3,""Response"":""8/20/2024""},
            {""Question"":""Entity HM Code"",""QuestionOrder"":4,""Response"":""34""},
            {""Question"":""applicability has been assessed"",""QuestionOrder"":5,""Response"":""MONDAY""}
        ],
        [
            {""Question"":""Number of controls"",""QuestionOrder"":1,""Response"":""46""},
            {""Question"":""Number of controls already implemented"",""QuestionOrder"":2,""Response"":""Sept""},
            {""Question"":""Is the considered material for the implementation of the specific controls"",""QuestionOrder"":3,""Response"":""9/20/2024""},
            {""Question"":""Entity HM Code"",""QuestionOrder"":4,""Response"":""6""}
        ]
    ]",
    
    // Parse JSON text into a list of lists
    ParsedJson = Json.Document(Text.FromBinary(Text.ToBinary(Source))),
    
    // Add an index to identify each response set
    IndexedData = Table.AddIndexColumn(Table.FromList(ParsedJson, Splitter.SplitByNothing(), {"Responses"}), "ResponseSet", 1, 1, Int64.Type),
    
    // Replace nulls with empty lists before expanding
    EnsureList = Table.TransformColumns(IndexedData, {"Responses", each if _ = null then {} else _}),
    
    // Expand the inner lists into rows
    ExpandedData = Table.ExpandListColumn(EnsureList, "Responses"),
    // Expand records into Question and Response columns
    ExpandedRecords = Table.ExpandRecordColumn(ExpandedData, "Responses", {"Question", "Response"}, {"Question", "Response"}),
    
    // Pivot the table to create columns for each question
    PivotedTable = Table.Pivot(
        ExpandedRecords,
        List.Distinct(ExpandedRecords[Question]),
        "Question",
        "Response"
    ),
    #"Removed Columns" = Table.RemoveColumns(PivotedTable,{"ResponseSet"})
in
    #"Removed Columns"