如何用Power Query展开转置后列并避免重复数据？

问题根源 #

转置后的每一列均为嵌套表，直接批量展开会触发笛卡尔积匹配，导致数据重复。要实现逐列展开且行对齐无重复，需通过循环逐列处理并按行索引合并。

修改后的M代码 #

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Renamed Columns" = Table.RenameColumns(Source,{{"instrument_key", "Symbol1"}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Renamed Columns", "Symbol.1", each Symbol([Symbol1])),
    #"Removed Columns" = Table.RemoveColumns(#"Invoked Custom Function",{"symbol", "year", "W/M", "Strike", "Option", "Symbol1", "tradingsymbol", "Lot"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns"),
    // 获取转置后的所有列名
    ColumnNames = Table.ColumnNames(#"Transposed Table"),
    // 循环逐列展开并按索引合并
    #"Expanded All Columns" = List.Accumulate(ColumnNames, #table({}, {}), (state, current) =>
        let
            CurrentColumn = Table.SelectColumns(#"Transposed Table", {current}),
            ExpandedCurrent = Table.ExpandTableColumn(CurrentColumn, current, Table.ColumnNames(CurrentColumn{0}[current])),
            // 添加索引确保行对齐
            AddedIndex = Table.AddIndexColumn(ExpandedCurrent, "Index", 0, 1, Int64.Type),
            // 首次处理直接用当前展开表，后续按索引合并
            Merged = if state = #table({}, {}) then AddedIndex else Table.Join(state, "Index", AddedIndex, "Index")
        in
            Merged
    ),
    // 移除临时索引列
    #"Removed Index Column" = Table.RemoveColumns(#"Expanded All Columns", {"Index"})
in
    #"Removed Index Column"

关键逻辑说明 #

• List.Accumulate循环：遍历所有列，逐个展开嵌套表，避免同时展开触发的交叉重复。
• 索引列对齐：给每列的展开结果添加索引，确保合并时是同一行数据匹配，而非全量交叉。
• 渐进式合并：从空表开始，逐步将每一列的展开结果合并到最终表中，保证数据结构与目标效果一致。

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