Power Query：按列名列表连接列并保留原始列的方法

问题描述 #

需要实现：根据指定列名列表，用竖线|作为分隔符连接对应列的内容，同时保留所有原始列。当前使用Table.CombineColumns进行合并时，会移除被合并的原始列，无法满足需求。

现有尝试代码 #

#"Trimmed Text" = Table.TransformColumns(#"Sorted Rows",{{"Id", Text.Trim, type text}}),
    newMergedHeader = Table.ColumnNames(mergeHeaders){0},
    
    #"Merged Columns" = 
        if newMergedHeader <> null then 
          Table.RenameColumns(
          Table.CombineColumns(#"Trimmed Text",Table.Column(mergeHeaders,newMergedHeader),each Combiner.CombineTextByDelimiter("|", QuoteStyle.None)(List.Difference(List.RemoveNulls(_), {""})),"Merged"),{{"Merged",newMergedHeader}})
        else #"Trimmed Text"

输入示例 #

| Other Colums | Label              | Segment              | Type   | Other Colums |
|--------------|--------------------|----------------------|--------|--------------|
| stuff        | Alexis Bhat        |                      |        | stuff        |
| stuff        | Alphonse Wilkinson |                      | Person | stuff        |
| stuff        | Bobbi Carter       | Network Blue         |        | stuff        |
| stuff        |                    | Community Healers    | Person | stuff        |
| stuff        | Dannie Cano        | Community Healers    | Person | stuff        |
| stuff        | Darius Neel        | Community Healers    | Person | stuff        |
| stuff        |                    |                      |        | stuff        |
| stuff        | Delores Rana       | Community Healers    | Person | stuff        |
| stuff        | Dionne Merino      | Education Commission | Person | stuff        |

期望输出示例 #

| Other Colums | Label              | Segment              | Type   | Other Colums | Blue                                      |
|--------------|--------------------|----------------------|--------|--------------|-------------------------------------------|
| stuff        | Alexis Bhat        |                      |        | stuff        | Alexis Bhat                               |
| stuff        | Alphonse Wilkinson |                      | Person | stuff        | Alphonse Wilkinson|Person                 |
| stuff        | Bobbi Carter       | Network Blue         |        | stuff        | Bobbi Carter|Network Blue                 |
| stuff        |                    | Community Healers    | Person | stuff        | Community Healers|Person                  |
| stuff        | Dannie Cano        | Community Healers    | Person | stuff        | Community Healers                         |
| stuff        | Darius Neel        | Community Healers    | Person | stuff        | Person                                    |
| stuff        |                    |                      |        | stuff        |                                           |
| stuff        | Delores Rana       | Community Healers    | Person | stuff        | Delores Rana|Community Healers|Person     |
| stuff        | Dionne Merino      | Education Commission | Person | stuff        | Dionne Merino|Education Commission|Person |

解决方案 #

核心思路是新增一列存储合并结果，而非替换或移除原始列。Table.CombineColumns的特性是合并后删除目标列，而Table.AddColumn可以在保留所有原始列的前提下，新增计算列。

修改后的代码 #

#"Trimmed Text" = Table.TransformColumns(#"Sorted Rows",{{"Id", Text.Trim, type text}}),
    newMergedHeader = Table.ColumnNames(mergeHeaders){0},
    columnsToMerge = if newMergedHeader <> null then Table.Column(mergeHeaders, newMergedHeader) else {},

    #"Added Merged Column" = 
        if List.IsEmpty(columnsToMerge) then 
            #"Trimmed Text"
        else
            Table.AddColumn(
                #"Trimmed Text",
                newMergedHeader,
                each 
                    let
                        // 获取当前行指定列的内容，去除空值和空字符串
                        values = List.RemoveNulls(Record.FieldValues(Record.SelectFields(_, columnsToMerge))),
                        cleanedValues = List.Difference(values, {""}),
                        // 用|连接非空内容
                        mergedText = Combiner.CombineTextByDelimiter("|", QuoteStyle.None)(cleanedValues)
                    in
                        mergedText,
                type text
            )

代码说明 #

1. 提取需要合并的列名列表columnsToMerge
2. 通过Table.AddColumn新增列，列名使用指定的newMergedHeader
3. 对每行数据：
   • 用Record.SelectFields筛选出需要合并的列内容
   • 用List.RemoveNulls和List.Difference清理空值和空字符串
   • 用Combiner.CombineTextByDelimiter完成内容连接
4. 如果没有指定合并列，直接返回原表

此操作会保留所有原始列，同时新增合并结果列，完全符合需求。

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