如何用Excel Power Query基于唯一PK完成多CSV数据的最终表构建？

完整M代码实现 #

let
    Source = Folder.Files("Folder containing csv files"),
    #"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", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Added Custom" = Table.AddColumn(#"Expanded Table Column1", "PK", each Text.Combine({[Period],[Week],[District],[Route]},"")),
    // 提取PK对应的基础字段（同一PK的基础字段值一致，取第一行即可）
    #"Add Base Fields" = Table.AddColumn(#"Added Custom", "BaseInfo", each Record.SelectFields(_, {"PK", "Year", "Period", "Week", "District", "Route"})),
    // 按PK分组，保留基础信息和文件名+销售额的子表
    #"Grouped Rows" = Table.Group(#"Add Base Fields", {"PK"}, {
        {"BaseInfo", each List.First([BaseInfo]), type record},
        {"SaleData", each Table.SelectColumns(_, {"Source.Name", "SaleAmt"}), type table}
    }),
    // 透视子表，每个文件对应一列，缺失值自动填null
    #"Pivot Sale Data" = Table.AddColumn(#"Grouped Rows", "PivotedSales", each Table.Pivot([SaleData], List.Distinct([SaleData][Source.Name]), "Source.Name", "SaleAmt")),
    // 展开基础信息记录
    #"Expand Base Info" = Table.ExpandRecordColumn(#"Pivot Sale Data", "BaseInfo", {"Year", "Period", "Week", "District", "Route"}),
    // 展开透视后的销售额列
    #"Expand Pivoted Sales" = Table.ExpandTableColumn(#"Expand Base Info", "PivotedSales", List.Distinct(List.Combine(Table.Column(#"Pivot Sale Data", "PivotedSales")))),
    // 移除中间辅助列
    #"Removed Extra Columns" = Table.RemoveColumns(#"Expand Pivoted Sales", {"SaleData", "PivotedSales"})
in
    #"Removed Extra Columns"

核心步骤解释 #

1. 提取基础字段：通过Record.SelectFields把每行的PK、Year、Period等字段打包成记录，分组后直接取第一行就能得到该PK对应的完整基础信息，避免重复处理。
2. 调整分组逻辑：分组时同时保留基础信息记录和仅包含文件名、销售额的子表，减少后续透视操作的数据量，提升效率。
3. 透视子表：用Table.Pivot把每个CSV文件的销售额转为独立列，List.Distinct([SaleData][Source.Name])会自动识别所有文件名作为列名，某个PK在对应文件中不存在时，该列自动填充null。
4. 展开数据：分别展开基础信息记录和透视后的销售额列，得到最终的扁平表结构，最后移除中间辅助列即可。

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