Power Query中无连接ID时合并同行数DataFrame列的方法

问题背景 #

需要将两个行数完全相同的表按行合并列（无可用连接ID），且无法用Python处理（避免丢失二进制文件）。尝试用Table.AddColumn(Source, "filterout", each Source2[Name])时，新列每行都是整个列的列表，而非对应行的单个值。

示例表格 #

原表：

| Content              | Name           | Extension  | Date accessed       | Date modified       | Date created        | Attributes | Folder Path                                                              |
|----------------------|----------------|------------|---------------------|---------------------|---------------------|------------|--------------------------------------------------------------------------|
| Binary.FromText(...) | .gitignore     | .gitignore | 2023/12/12 21:14:57 | 2023/12/12 15:24:49 | 2023/12/12 15:24:49 | [Record]   | G:\wsp\dataset\COVID-19\csse_covid_19_data\csse_covid_19_daily_reports\  |
| Binary.FromText(...) | 01-01-2021.csv | .csv       | 2023/12/12 22:16:12 | 2023/12/12 15:24:49 | 2023/12/12 15:24:49 | [Record]   | G:\wsp\dataset\COVID-19\csse_covid_19_data\csse_covid_19_daily_reports\  |
| Binary.FromText(...) | 01-01-2022.csv | .csv       | 2023/12/12 22:19:20 | 2023/12/12 15:24:49 | 2023/12/12 15:24:49 | [Record]   | G:\wsp\dataset\COVID-19\csse_covid_19_data\csse_covid_19_daily_reports\  |
| Binary.FromText(...) | 01-01-2023.csv | .csv       | 2023/12/12 22:19:20 | 2023/12/12 15:24:49 | 2023/12/12 15:24:49 | [Record]   | G:\wsp\dataset\COVID-19\csse_covid_19_data\csse_covid_19_daily_reports\  |

期望结果：

| Content              | Name           | Extension  | Date accessed       | Date modified       | Date created        | Attributes | Folder Path                                                              | filterout     |
|----------------------|----------------|------------|---------------------|---------------------|---------------------|------------|--------------------------------------------------------------------------|---------------|
| Binary.FromText(...) | .gitignore     | .gitignore | 2023/12/12 21:14:57 | 2023/12/12 15:24:49 | 2023/12/12 15:24:49 | [Record]   | G:\wsp\dataset\COVID-19\csse_covid_19_data\csse_covid_19_daily_reports\  | AppendedData1 |
| Binary.FromText(...) | 01-01-2021.csv | .csv       | 2023/12/12 22:16:12 | 2023/12/12 15:24:49 | 2023/12/12 15:24:49 | [Record]   | G:\wsp\dataset\COVID-19\csse_covid_19_data\csse_covid_19_daily_reports\  | AppendedData1 |
| Binary.FromText(...) | 01-01-2022.csv | .csv       | 2023/12/12 22:19:20 | 2023/12/12 15:24:49 | 2023/12/12 15:24:49 | [Record]   | G:\wsp\dataset\COVID-19\csse_covid_19_data\csse_covid_19_daily_reports\  | AppendedData1 |
| Binary.FromText(...) | 01-01-2023.csv | .csv       | 2023/12/12 22:19:20 | 2023/12/12 15:24:49 | 2023/12/12 15:24:49 | [Record]   | G:\wsp\dataset\COVID-19\csse_covid_19_data\csse_covid_19_daily_reports\  | AppendedData1 |

失败代码 #

let
    Source = Folder.Files("G:\wsp\dataset\COVID-19\csse_covid_19_data\csse_covid_19_daily_reports"),
    Source2 = Folder.Files("G:\wsp\dataset\COVID-19\csse_covid_19_data\csse_covid_19_daily_reports"),
    // #"appendedTable" = Table.AddColumn(Source, "filterout", each [Name]) // 此代码正确，每行取当前行Name值
    #"appendedTable" = Table.AddColumn(Source, "filterout", each Source2[Name]) // 错误：每行返回整个Source2的Name列列表
in
    appendedTable

失败原因 #

each Source2[Name]的逻辑是：在遍历Source每一行的上下文里，直接引用外部的Source2表，Source2[Name]会提取整个表的Name列，得到一个完整的列表。因为没有任何逻辑将Source的当前行与Source2的对应行绑定，所以每行都会重复插入这个完整列表，而非对应位置的单个值。

解决方案 #

方法1：添加索引列后合并 #

通过给两个表添加相同顺序的索引列，将索引作为连接键，实现按行匹配：

let
    Source = Folder.Files("G:\wsp\dataset\COVID-19\csse_covid_19_data\csse_covid_19_daily_reports"),
    Source2 = Folder.Files("G:\wsp\dataset\COVID-19\csse_covid_19_data\csse_covid_19_daily_reports"),
    // 给两个表添加从0开始的索引列
    SourceWithIndex = Table.AddIndexColumn(Source, "Index", 0, 1),
    Source2WithIndex = Table.AddIndexColumn(Source2, "Index", 0, 1),
    // 按索引列左连接两个表
    MergedTables = Table.NestedJoin(SourceWithIndex, {"Index"}, Source2WithIndex, {"Index"}, "Source2Data", JoinKind.LeftOuter),
    // 从嵌套的Source2数据中提取对应行的Name列作为filterout
    AddedFilterOut = Table.AddColumn(MergedTables, "filterout", each [Source2Data][Name]{0}),
    // 移除索引列和临时嵌套列
    FinalTable = Table.RemoveColumns(AddedFilterOut, {"Index", "Source2Data"})
in
    FinalTable

方法2：用List.Zip配对记录 #

将两个表的记录列表按位置配对，再合并每一对记录：

let
    Source = Folder.Files("G:\wsp\dataset\COVID-19\csse_covid_19_data\csse_covid_19_daily_reports"),
    Source2 = Folder.Files("G:\wsp\dataset\COVID-19\csse_covid_19_data\csse_covid_19_daily_reports"),
    // 将两个表的记录列表按行位置配对
    ZippedRecordPairs = List.Zip({Table.ToRecords(Source), Table.ToRecords(Source2)}),
    // 合并每一对记录，添加filterout列
    MergedRecords = List.Transform(ZippedRecordPairs, (pair) => 
        Record.Combine({pair{0}, [filterout = pair{1}[Name]]})
    ),
    // 将合并后的记录转回表格
    FinalTable = Table.FromRecords(MergedRecords)
in
    FinalTable

内容的提问来源于stack exchange，提问作者Nor.Z