Power Query合并列数不同表格：删除特定表格首列求助

核心思路 #

针对不同列数的工作表做针对性处理：

• 3列的正常表格直接保留
• 4列且前两列为重复name的表格，删除首列后统一为3列格式

修改后的完整代码 #

主查询部分 #

let
    Source = Folder.Files("source"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {{"Name", "Source Name"}}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", {"name", "address", "type"})
in
    #"Expanded Table Column1"

自定义函数Transform File部分 #

let
    Source = Excel.Workbook(_, true, true),
    #"Filtered Rows" = Table.SelectRows(Source, each [Kind] = "Sheet"),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows", {"Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", Table.ColumnNames(#"Removed Other Columns"{0}[Data])),
    // 新增列处理逻辑
    #"Get Column Names" = Table.ColumnNames(#"Expanded Data"),
    #"Check Column Condition" = if List.Count(#"Get Column Names") = 4 and #"Get Column Names"{0} = "name" and #"Get Column Names"{1} = "name" then Table.RemoveColumns(#"Expanded Data", {0}) else #"Expanded Data",
    // 强制统一列名（确保后续展开匹配）
    #"Rename to Standard Columns" = Table.RenameColumns(#"Check Column Condition", List.Zip({Table.ColumnNames(#"Check Column Condition"), {"name", "address", "type"}}))
in
    #"Rename to Standard Columns"

关键步骤说明 #

• 精准定位问题表格：通过List.Count(#"Get Column Names") = 4 and #"Get Column Names"{0} = "name" and #"Get Column Names"{1} = "name"判断需要处理的4列表格
• 针对性删除首列：满足条件时用Table.RemoveColumns(#"Expanded Data", {0})删除第一列，不影响正常3列表格
• 统一列名避免异常：强制将所有表格列名改为标准的name/address/type，确保后续展开时列完全匹配
• 固定展开列：主查询直接指定展开标准列，不用动态获取列名，避免异常列干扰合并结果

注意事项 #

• 如果实际列名不是name/address/type，请替换代码中对应的字符串
• 若工作表表头格式混乱，可在自定义函数开头添加Promote Headers步骤确保表头正确

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