如何使用Excel Power Query解析包含特殊sublist字段的嵌套JSON数组

问题根因 #

• 原代码fixSublist步骤引用的表对象错误，上一步输出名为#"Expanded response_json"，不存在名为response_json的表
• 早期判断逻辑混淆了布尔值false和字符串"false"，经Json.Document解析后，JSON中的false会转为Power Query的逻辑值，不是文本类型
• 字段处理顺序错误，需要先展开response_json的顶层记录，拿到sublist字段后再做类型兼容处理

修正后完整代码 #

let
    Source = Excel.Workbook(File.Contents("D:\Downloads\ExampleDataSet.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}, {"Column2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"response_id", type text}, {"response_json", type text}}),
    // 解析顶层JSON
    #"Parsed JSON" = Table.TransformColumns(#"Changed Type1",{{"response_json", Json.Document}}),
    // 展开顶层JSON数组为行
    #"Expanded response_json list" = Table.ExpandListColumn(#"Parsed JSON", "response_json"),
    // 展开顶层JSON的记录字段，拿到sublist
    #"Expanded response_json record" = Table.ExpandRecordColumn(#"Expanded response_json list", "response_json", {"sublist", "aggregate_category", "question"}, {"sublist", "aggregate_category", "question"}),
    // 统一sublist格式：false/空数组都转为包含默认空记录的数组
    #"Fixed sublist type" = Table.TransformColumns(#"Expanded response_json record", {"sublist", each 
        if _ = false then {[option_1 = null, option_2 = null, option_3 = null]} 
        else if _ = [] then {[option_1 = null, option_2 = null, option_3 = null]}
        else _
    }),
    // 展开sublist数组为行
    #"Expanded sublist" = Table.ExpandListColumn(#"Fixed sublist type", "sublist"),
    // 展开sublist的记录字段为新列
    #"Expanded sublist fields" = Table.ExpandRecordColumn(#"Expanded sublist", "sublist", {"option_1", "option_2", "option_3"}, {"option_1", "option_2", "option_3"})
in
    #"Expanded sublist fields"

关键逻辑说明 #

• 对sublist的三种值类型做了统一兼容：
  • 值为布尔false时，自动转为包含默认空记录的数组
  • 值为空数组[]时，同样转为包含默认空记录的数组，避免展开后丢失原有行
  • 有实际内容的数组保持原格式不变
• 调整了字段展开顺序，先处理顶层JSON结构，再处理嵌套的sublist字段，避免表引用错误
• 如果不需要保留空值行，可以在#"Fixed sublist type"步骤后添加筛选，过滤掉sublist为空数组的行即可

内容的提问来源于stack exchange，提问作者jason