In a workbook i have sheets that look like this:

The sheets im concerned with extracting data from follow a naming convention, where the sheet names are pre-fixed with ‘d_’.
Additionally, in the above, there are local named ranges for:

1. tbl_pr: named range for the entire table highligted by the border above, with columns ‘month, date, gross’
2. tot_{jan}: named range for each month total row (i.e Jan Total=jan_total named range, Feb Total = feb_total, etc)
3. tot_qtr{1}: named range for each qtr total row (Qtr1 Total = qtr1_total)
4. employee_name: named range for the name of the person
5. ssn: named range for the ssn

Using power query, for every sheet that begins with ‘d_’, i would like to extract and show a table consisting of columns:
Name, SSN, Qtr1, Qtr2, Qtr3, Qtr4

For the Qtr# rowss i need just the cell in the ‘Gross’ column.

i googled on how to iterate sheets in power query and extract data, but not much. best i can find was this: https://community.fabric.microsoft.com/t5/Desktop/Looping-though-tabs-or-tables-to-extract-data-from-excel-files/m-p/932205

So as my very first attempt at trying to understand power query..what ive learned is it is impossible to get the sheet name from the current workbook. So instead u have to open and read the contents of an external file. Here is what i have so far

let
    //read file
    curr_year=Excel.CurrentWorkbook(){[Name="sys_year"]}[Content]{0}[Column1],
    payroll_file = Text.Replace("Z:onedrive{year}{year} pr.xlsm", "{year}", Number.ToText(curr_year)),
    tbl1 = Excel.Workbook(File.Contents(payroll_file), null, true),
    //filter for only sheet we are interested
    emp_sheets = Table.SelectRows(tbl1, each [Kind] = "Sheet" and Text.StartsWith([Name], "d_") and [Name] <> "d_tmplt"),
    
    //generate employee named range reference by concatenating sheet name + 'employee_name'
    emp_nr_added = Table.AddColumn(emp_sheets, "emp_nr", each [Item] & "!employee_name"),
    //now try to fetch the named range
    emp_nr_val =  Excel.CurrentWorkbook(){[Name = each emp_nr_added[emp_nr]]}[Content]{0}[Column1]
    //tbl2 = Table.AddColumn(emp_sheets, "test", each Expression.Evaluate(emp_nr{0}))
in
    emp_nr_val

This fails when trying to fetch the named range value

Expression.Error: The key didn't match any rows in the table.
Details:
    Key=[Record]
    Table=[Table]

I have confirmed that the named range exist and i can reference them manually via a standard excel equation (“={sheetname}!employee_name”)
Also, after solving this, i think i need to do an Index/Match to get the Qtr gross cell, not quite sure yet how i will do that w/ power query.

Any help much appreciated