Power Query提取带d_前缀工作表指定命名区域数据求助

核心问题分析 #

你当前的自定义函数仅适配单个单元格类型的命名区域（如employee_name、SSN），但tot_qtr{季度}是整行命名区域，需要调整函数逻辑来提取其中gross列的值。以下是完整解决方案：

修改后的自定义函数 #

新增可选参数colName，同时支持单个单元格和整行区域的值提取：

let get_nr=(nr as text, optional colName as text) =>
    let
        // 获取目标命名区域的内容
        nrContent = Excel.CurrentWorkbook(){[Name=nr]}[Content],
        // 根据是否传入列名，选择对应的取值逻辑
        value = if colName <> null then
                   // 从整行区域中提取指定列的第一行值
                   nrContent{0}[colName]
               else
                   // 单个单元格区域直接取第一行第一列值
                   nrContent{0}[Column1]
    in
        value
in get_nr

完整主查询代码 #

let
    // 读取当前年份参数
    curr_year=Excel.CurrentWorkbook(){[Name="curr_year"]}[Content]{0}[Column1],
    payroll_file = Text.Replace("Z:\onedrive\{year}\{year} pr.xlsm", "{year}", Number.ToText(curr_year)),
    tbl_wrkbk = Excel.Workbook(File.Contents(payroll_file), null, true),

    // 筛选目标工作表：d_前缀，排除模板表
    emp_sheets = Table.SelectRows(tbl_wrkbk, each [Kind] = "Sheet" and Text.StartsWith([Name], "d_") and [Name] <> "d_tmplt"),

    // 生成所有需要的命名区域引用
    add_nr_refs = Table.AddColumns(emp_sheets, {
        "emp_nr", each [Name] & "!employee_name",
        "ssn_nr", each [Name] & "!SSN",
        "qtr1_nr", each [Name] & "!tot_qtr1",
        "qtr2_nr", each [Name] & "!tot_qtr2",
        "qtr3_nr", each [Name] & "!tot_qtr3",
        "qtr4_nr", each [Name] & "!tot_qtr4"
    }),

    // 调用函数提取所有字段值
    extract_values = Table.AddColumns(add_nr_refs, {
        "Name", each get_nr([emp_nr]),
        "SSN", each get_nr([ssn_nr]),
        "Qtr1", each get_nr([qtr1_nr], "gross"),
        "Qtr2", each get_nr([qtr2_nr], "gross"),
        "Qtr3", each get_nr([qtr3_nr], "gross"),
        "Qtr4", each get_nr([qtr4_nr], "gross")
    }),

    // 保留最终需要的输出列
    final_table = Table.SelectColumns(extract_values, {"Name", "SSN", "Qtr1", "Qtr2", "Qtr3", "Qtr4"})
 in 
   final_table

关键说明 #

1. 命名区域引用格式：如果工作表名包含空格/特殊字符，需用单引号包裹，例如修改为"'[Name]'!employee_name"（注意引号嵌套）
2. 错误排查：若仍出现The key didn't match any rows in the table错误，需确认：
   • 所有d_前缀工作表都存在对应的tot_qtr1~4、employee_name、SSN命名区域
   • 命名区域的拼写完全匹配（区分大小写）

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