如何在Power Query中展开含Record、List、null类型的列？

问题背景 #

• 单一类型列（如全为Record）可通过下拉按钮直接展开，但目标列同时包含Record、嵌套Record的List以及null值，无自动展开按钮，需手动编写代码实现
• 参考社区方案因真实数据结构差异无法适配，自行编写的代码运行报错，需完成最后一列的完全展开

原报错代码 #

...
    // Convert the API Response data to a table (of lists)
    #"Converted to Table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    
    // Expand to get 1 column of Record types
    #"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
    
    // Expand those records into their own columns, we need to target Column1.target
    #"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"_links", "id", "type", "emitted_at", "conversation", "source", "target"}, {"Column1._links", "Column1.id", "Column1.type", "Column1.emitted_at", "Column1.conversation", "Column1.source", "Column1.target"}),
    
    // At this point, Column1.target is a column of null and Record value types. Expand it again
    #"Expanded Column1.target" = Table.ExpandRecordColumn(#"Expanded Column2", "Column1.target", {"data"}, {"Column1.target.data"}),
    
    // Now we need to target Column1.target.data, which is the column that has Record, List, AND null types
    #"Added NEWEXPANDEDCOLUMN" = Table.AddColumn(#"Expanded Column1.target", #"Expanded Column1.target.data.NEW", each if Value.Type([#"Expanded Column1.target.data"]) = List.Type then [#"Expanded Column1.target.data"] else {[#"Expanded Column1.target.data"]})
in
    #"Added NEWEXPANDEDCOLUMN"

错误分析 #

1. 列名语法错误：新列名#"Expanded Column1.target.data.NEW"未用正确的标识符格式包裹
2. null值未处理：Value.Type(null)会触发错误，且转换逻辑未覆盖null场景
3. List处理逻辑错误：原代码将已有List再次嵌套一层List，导致后续展开层级混乱

修正后的代码 #

...
    // Convert the API Response data to a table (of lists)
    #"Converted to Table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    
    // Expand to get 1 column of Record types
    #"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
    
    // Expand those records into their own columns, we need to target Column1.target
    #"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"_links", "id", "type", "emitted_at", "conversation", "source", "target"}, {"Column1._links", "Column1.id", "Column1.type", "Column1.emitted_at", "Column1.conversation", "Column1.source", "Column1.target"}),
    
    // At this point, Column1.target is a column of null and Record value types. Expand it again
    #"Expanded Column1.target" = Table.ExpandRecordColumn(#"Expanded Column2", "Column1.target", {"data"}, {"Column1.target.data"}),
    
    // 统一转换为List：null转空List，Record转单元素List，List保持原样
    #"Converted to List" = Table.AddColumn(#"Expanded Column1.target", "TargetDataList", each 
        if [Column1.target.data] = null then {}
        else if Value.Is([Column1.target.data], List.Type) then [Column1.target.data]
        else {[Column1.target.data]}),
    
    // 展开List列
    #"Expanded TargetDataList" = Table.ExpandListColumn(#"Converted to List", "TargetDataList"),
    
    // 展开最终的Record列（替换为实际需要提取的字段名）
    #"Expanded Final Records" = Table.ExpandRecordColumn(#"Expanded TargetDataList", "TargetDataList", {"field1", "field2", "field3"}, {"TargetData.field1", "TargetData.field2", "TargetData.field3"}),
    
    // 可选：删除中间辅助列
    #"Removed Auxiliary Columns" = Table.RemoveColumns(#"Expanded Final Records", {"Column1.target.data", "TargetDataList"})
in
    #"Removed Auxiliary Columns"

关键说明 #

• 统一类型转换：通过判断值类型，将三种数据格式统一转为List，确保后续展开操作可批量执行
• 分步展开：先展开List列，再展开嵌套的Record列，避免层级混乱
• 字段自定义：将代码中的{"field1", "field2", "field3"}及对应输出列名，替换为你实际需要提取的Record字段

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