Power Query中List与Number无法应用>运算符的错误求助

错误原因 #

你遇到的Expression.Error核心问题是：在自定义列的each上下文里，用#"Added Index"[Index]这种写法会引用整个列的所有值（List类型），而你要的是当前行的单个索引值（Number类型），所以会出现列表和数字无法比较的错误。同理，其他地方用#"Added Index"[Contractor]也是引用整个列，而非当前行的值。

另外，最后一步只提取第一行的Date Range，这不符合你合并所有员工记录的需求。还有提前把Detail Start Date转成文本也没必要，后续拼接时转换即可，保留日期类型更灵活。

修正后的完整代码 #

let
    Source = Excel.Workbook(File.Contents("C:\Users\Robert\Downloads\Assignment_Detail_Tracking_TEST.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Contractor", type text}, {"ID", Int64.Type}, {"Event Date", type date}, {"Detail Start Date", type date}, {"Detail End Date", type date}, {"Amendment Event Type", type text}, {"Amendment Reason", type text}, {"Amendment Type", type text}, {"Comment", type text}, {"Bill Rate", type number}, {"Current Start Date", type date}, {"Supplier", type text}, {"Hiring Manager", type text}, {"Tax Work Location", type text}, {"VMO", type text}, {"Detail Status", type text}, {"Longevity In Days", Int64.Type}, {"Status", type text}, {"Sub Status", type text}, {"Labor Category", type text}}),
    #"Multi Sort on Contractor Ascending and Detail Start Date Descending" = Table.Sort(#"Changed Type",{{"Contractor", Order.Ascending}, {"Detail Start Date", Order.Descending}}),
    #"Extract Building Number from Tax Work Location" = Table.AddColumn(#"Multi Sort on Contractor Ascending and Detail Start Date Descending", "Building", each Text.BeforeDelimiter([Tax Work Location], " -"), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Extract Building Number from Tax Work Location",{"ID", "Event Date", "Detail End Date", "Amendment Reason", "Amendment Type", "Comment", "Bill Rate", "Current Start Date", "Hiring Manager", "VMO", "Detail Status", "Status", "Sub Status", "Longevity In Days", "Labor Category", "Tax Work Location"}),
    #"Include Only Creation and Termination" = Table.SelectRows(#"Removed Columns", each Text.Contains([Amendment Event Type], "Termination") or Text.Contains([Amendment Event Type], "Creation")),
    #"Added Index" = Table.AddIndexColumn(#"Include Only Creation and Termination", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Date Range", each 
        let
            currentIndex = [Index],
            totalRows = Table.RowCount(#"Added Index"),
            isLastRow = currentIndex = totalRows - 1,
            isFirstRow = currentIndex = 0,
            currentContractor = [Contractor],
            currentSupplier = [Supplier],
            currentBuilding = [Building],
            currentEventType = [Amendment Event Type]
        in
            if not isLastRow 
                and currentContractor = #"Added Index"{currentIndex + 1}[Contractor]
                and currentSupplier = #"Added Index"{currentIndex + 1}[Supplier]
                and currentBuilding = #"Added Index"{currentIndex + 1}[Building]
                and Text.Contains(currentEventType, "Termination")
                and Text.Contains(#"Added Index"{currentIndex + 1}[Amendment Event Type], "Creation")
            then Text.From(#"Added Index"{currentIndex + 1}[Detail Start Date]) & " - " & Text.From([Detail Start Date])
            else if not isFirstRow
                and currentContractor = #"Added Index"{currentIndex - 1}[Contractor]
                and currentSupplier = #"Added Index"{currentIndex - 1}[Supplier]
                and currentBuilding = #"Added Index"{currentIndex - 1}[Building]
                and Text.Contains(currentEventType, "Creation")
                and Text.Contains(#"Added Index"{currentIndex - 1}[Amendment Event Type], "Termination")
            then Text.From([Detail Start Date]) & " - " & Text.From(#"Added Index"{currentIndex - 1}[Detail Start Date])
            else if Text.Contains(currentEventType, "Creation")
            then Text.From([Detail Start Date]) & " - " & Text.From(DateTime.LocalNow())
            else null
    ),
    // 可选：移除重复的日期范围记录，保留每个员工的有效记录
    #"Removed Duplicates" = Table.Distinct(#"Added Custom", {"Contractor", "Supplier", "Building", "Date Range"})
in
    #"Removed Duplicates"

关键修正点 #

• 所有引用当前行值的地方，改用[列名]（比如[Index]、[Contractor]），而非整个表的列（#"Added Index"[Index]）
• 添加了isLastRow和isFirstRow判断，避免索引越界报错
• 取消了Detail Start Date转文本的步骤，拼接时用Text.From()转换日期，保留原数据类型
• 最后添加了去重步骤，避免同一员工的重复日期范围记录
• 不再只提取第一行结果，返回完整的合并后表

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