Excel Power Query合并表时提取非空首个ITEM值的代码修改需求

借助现有Power Query代码合并Overview表与Data表后，结果中ITEM类列（ITEM、ITEM 2、Y/N）出现重复值（如345345, 111111），需求为每组仅保留首个非空有效值：

• 原组合为aaaa, 0（0代表空值）→ 期望显示aaaa
• 原组合为0, bbbb → 期望显示bbbb
• 原组合为cccc, dddd → 期望显示cccc

此前修改Grouped Rows部分时，直接取_[ITEM]{0}仅能获取第一个元素，若第一个元素为空则返回空值，无法满足需求。

在分组处理时，先对目标列的所有值过滤空值，再取过滤后列表的第一个元素。使用List.RemoveNulls()清理空值，再通过List.First()获取首个有效内容（若过滤后无有效值则返回null，符合逻辑）。

修改后的Grouped Rows代码段 #

#"Grouped Rows" = Table.Group(#"Expanded Fields", {"Index"}, {{"all", 
    each Table.TransformColumns(_, {
        {"ITEM", (c)=> List.First(List.RemoveNulls(_[ITEM]))},
        {"ITEM 2",(c)=> List.First(List.RemoveNulls(_[ITEM 2]))}, 
        {"Y/N", (c)=> List.First(List.RemoveNulls(_[#"Y/N"]))}}
    ){0}}}),

注：如果你的空值是以文本"0"表示（而非真正的null），需要将List.RemoveNulls替换为List.RemoveItems，示例：

{"ITEM", (c)=> List.First(List.RemoveItems(_[ITEM], {"0"}))},

let
    Source = Excel.CurrentWorkbook(){[Name="Overview"]}[Content],
    #"Overview" = Table.TransformColumnTypes(Source,{
        {"NAME.1", type text}, {"NAME.2", type text}, {"NUMBER", Int64.Type}, {"RANDOM", type text}}),
  
// 添加索引列用于恢复原表顺序
    #"Added Index" = Table.AddIndexColumn(Overview, "Index", 0, 1, Int64.Type),

// 创建合并名称列并展开为单行
    #"Added Custom" = Table.AddColumn(#"Added Index", "Name", each List.RemoveNulls({[NAME.1]} & {[NAME.2]}), type {text}),
    #"Expanded Name" = Table.ExpandListColumn(#"Added Custom", "Name"),

// 对Data表执行相同的名称处理
    #"Added Custom2" =Table.AddColumn(Data,"dataName",each {[NAME.1],[NAME.2]}, type {text}),
    #"Expanded dataName" = Table.ExpandListColumn(#"Added Custom2", "dataName"),

// 提取Data表需要的字段
    #"Data Fields" = List.FirstN(Table.ColumnNames(#"Expanded dataName"),3),

// 合并两个表
    #"Merge Overview/Data" = Table.NestedJoin(#"Expanded Name","Name",#"Expanded dataName","dataName","Join",JoinKind.LeftOuter),

// 提取合并后的目标字段
    #"Add Custom3" = Table.AddColumn(#"Merge Overview/Data", "Fields", each if [Name]=null then null
            else if Table.RowCount([Join]) = 0 then null
            else Record.SelectFields([Join]{0}, #"Data Fields"),type record),
    #"Removed Columns" = Table.RemoveColumns(#"Add Custom3",{"Name", "Join"}),
    #"Expanded Fields" = Table.ExpandRecordColumn(#"Removed Columns", "Fields", {"ITEM", "ITEM 2", "Y/N"}),
    // 修改后的分组逻辑：取首个非空值
    #"Grouped Rows" = Table.Group(#"Expanded Fields", {"Index"}, {{"all", 
        each Table.TransformColumns(_, {
            {"ITEM", (c)=> List.First(List.RemoveNulls(_[ITEM]))},
            {"ITEM 2",(c)=> List.First(List.RemoveNulls(_[ITEM 2]))}, 
            {"Y/N", (c)=> List.First(List.RemoveNulls(_[#"Y/N"]))}}
        ){0}}}),
       
    #"Removed Columns1" = Table.RemoveColumns(#"Grouped Rows",{"Index"}),
    #"Expanded all" = Table.ExpandRecordColumn(#"Removed Columns1", "all", {"NAME.1", "NAME.2", "NUMBER", "RANDOM", "Index", "ITEM", "ITEM 2", "Y/N"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Expanded all",{"Index"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns2",{
        {"NAME.1", type text}, {"NAME.2", type text}, {"NUMBER", Int64.Type}, 
        {"RANDOM", type text}, {"ITEM", type text}, {"ITEM 2", type text}, 
        {"Y/N", type text}})
in
    #"Changed Type"

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