如何用Power Query添加工作表名列并解决去重后值变为1的问题？

问题根源 #

你之前的自定义列函数返回的是包含所有工作表名称的完整表，而非当前行对应的单个工作表名称，导致每个单元格嵌套了整个表，展开后重复；去重后变成"1"是因为Power Query将嵌套表视为单一值处理，去重操作返回的是错误的聚合结果，而非目标名称。

最优解决方案：从数据源源头关联工作表名称 #

直接重构数据加载逻辑，在读取每个工作表时就绑定对应的名称，避免事后补全的问题：

let
    // 替换为你的Excel文件路径
    Source = Excel.Workbook(File.Contents("c:\temp\test0001.xlsx")),
    // 筛选出所有工作表
    FilteredSheets = Table.SelectRows(Source, each [Kind] = "Sheet"),
    // 添加自定义列：加载每个工作表的内容，并提前跳过前2行（整合原代码逻辑）
    AddedSheetData = Table.AddColumn(FilteredSheets, "SheetData", each Table.Skip([Data], 2)),
    // 展开工作表数据列，保留原有的工作表名称列（Name）
    ExpandedSheetData = Table.ExpandTableColumn(AddedSheetData, "SheetData", {"Attribute", "Value"}, {"Content.Attribute", "Content.Value"}),
    // 以下是你原有的数据处理步骤，全程保留工作表名称列
    RemovedDuplicates = Table.Distinct(ExpandedSheetData, {"Content.Value"}),
    RemovedBlankRows = Table.SelectRows(RemovedDuplicates, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    RemovedErrors = Table.RemoveRowsWithErrors(RemovedBlankRows, {"Content.Value"}),
    SortedRows = Table.Sort(RemovedErrors,{{"Content.Value", Order.Ascending}}),
    ExtractedTextRange = Table.TransformColumns(SortedRows, {{"Name", each Text.Middle(_, 1, 8), type text}})
in
    ExtractedTextRange

兼容原有Query1的解决方案（如果无法修改Query1） #

如果必须基于已有的Query1()继续处理，可通过索引关联工作表名称：

let
    Source = Query1(),
    // 获取所有工作表名称列表
    SheetNames = List.Select(
        Table.Column(Excel.Workbook(File.Contents("c:\temp\test0001.xlsx")), "Name"),
        each Excel.Workbook(File.Contents("c:\temp\test0001.xlsx")){[Name=_]}[Kind] = "Sheet"
    ),
    // 为原数据添加索引
    AddedIndex = Table.AddIndexColumn(Source, "Index", 0, 1),
    // 为工作表名称列表创建带索引的表
    SheetNameTable = Table.AddIndexColumn(
        Table.FromList(SheetNames, Splitter.SplitByNothing(), {"SheetName"}),
        "Index", 0, 1
    ),
    // 通过索引合并两张表，关联对应的工作表名称
    MergedTables = Table.NestedJoin(AddedIndex, {"Index"}, SheetNameTable, {"Index"}, "SheetNameTable", JoinKind.LeftOuter),
    // 展开工作表名称列
    ExpandedSheetName = Table.ExpandTableColumn(MergedTables, "SheetNameTable", {"SheetName"}, {"SheetName"}),
    // 执行你原有的数据处理步骤
    RemovedTopRows = Table.Skip(ExpandedSheetName,2),
    ExpandedContent = Table.ExpandTableColumn(RemovedTopRows, "Content", {"Attribute", "Value"}, {"Content.Attribute", "Content.Value"}),
    RemovedDuplicates = Table.Distinct(ExpandedContent, {"Content.Value"}),
    RemovedBlankRows = Table.SelectRows(RemovedDuplicates, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    RemovedErrors = Table.RemoveRowsWithErrors(RemovedBlankRows, {"Content.Value"}),
    SortedRows = Table.Sort(RemovedErrors,{{"Content.Value", Order.Ascending}}),
    ExtractedTextRange = Table.TransformColumns(SortedRows, {{"Name", each Text.Middle(_, 1, 8), type text}})
in
    ExtractedTextRange

注意事项 #

• 第一种方案是首选，因为它在数据加载的源头就建立了数据与工作表名称的关联，避免后续关联错误
• 第二种方案要求Query1()返回的数据行顺序与Excel中工作表的顺序完全一致，否则会出现名称不匹配的问题

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