Is it possible in PQ to extract specific cells from multiple Excel files into one master fiile?

Hi Strunec16, 

Yes, this is doable with Power Query without converting your source files to Tables.

• Use Folder.Files to enumerate the folder, Excel.Workbook to read each file, and a small function to grab specific cells (C1, C2, C3, C4).
• Split C1 into Project Number (first 9 chars) and Project Name (rest).
• Expand, sort if you like, and add an Index column for your "No." field.
• For portability of your Overview_2025.xlsx, store the folder path in a named cell (e.g., FolderPath) and have Power Query read that cell so reconnecting later is just updating one cell.

Deep dive (exact steps and code)

1. In Overview_2025.xlsx, create a cell on a sheet called something like “Config”, type your source folder path into it (e.g., C:\Data\Projects\2025\SourceFiles\) and define a Name for that single cell: FolderPath.
2. Create a query function that extracts the needed cells from one workbook:
   • In Power Query (Data > Get Data > Launch Power Query Editor):
     • Home > New Source > Blank Query.
     • View > Advanced Editor, paste this as fxExtractFromWorkbook:

   // fxExtractFromWorkbook // f: file binary, sheetName: optional; pass "" to use first sheet (f as binary, sheetName as text) as table => let WB = Excel.Workbook(f, false), ChosenData = if (sheetName <> null and sheetName <> "") then WB{[Kind="Sheet", Item=sheetName]}[Data] else WB{0}[Data],
   // Helper to read a 0-based row, 0-based column (A=0, B=1, C=2 ...)
   GetCell = (tbl as table, row as number, col as number) as any =>
       let
           r  = try tbl{row} otherwise null,
           v  = if r is record then Record.FieldOrDefault(r, "Column" & Number.ToText(col + 1), null) else null
       in
           v,
   
   // C1..C4 are column index 2 (A=0,B=1,C=2); rows are 0..3
   C1 = GetCell(ChosenData, 0, 2),
   C2 = GetCell(ChosenData, 1, 2),
   C3 = GetCell(ChosenData, 2, 2),
   C4 = GetCell(ChosenData, 3, 2),
   
   // Robust date conversion (handles Excel serials or actual dates)
   DateValue =
       let tryDirect = try Date.From(C4) otherwise null in
       if tryDirect <> null then tryDirect
       else Date.From(#datetime(1899,12,30,0,0,0) + #duration(Number.From(C4),0,0,0)),
   
   ProjectNumber = if C1 = null then null else Text.Start(Text.From(C1), 9),
   ProjectName   = if C1 = null then null else Text.Trim(Text.Middle(Text.From(C1), 10)),
   Customer      = if C2 = null then null else Text.From(C2),
   Country       = if C3 = null then null else Text.From(C3),
   
   Result = #table(
       {"Date","Project Number","Customer","Country","Project Name"},
       { {DateValue, ProjectNumber, Customer, Country, ProjectName} }
   )
   
   
   in
   Result

3. Create the main “From Folder” query:
   • Home > New Source > Blank Query
   • Advanced Editor, paste:

   let // Read the folder path from a named cell in this workbook FolderPathTable = Excel.CurrentWorkbook(){[Name="FolderPath"]}[Content], FolderPath = FolderPathTable{0}[Column1],
   // Get files
   Files = Folder.Files(FolderPath),
   
   // Keep only Excel files you care about
   OnlyExcel =
       Table.SelectRows(
           Files,
           each Text.EndsWith([Extension], ".xlsx")
             or Text.EndsWith([Extension], ".xlsm")
             or Text.EndsWith([Extension], ".xls")
       ),
   
   // OPTIONAL: if your source sheet name is fixed, put it here (e.g., "TOTALINPUT")
   // If not, pass "" and the function will use the first sheet in each file.
   SheetName = "",
   
   // Extract cells
   AddedData = Table.AddColumn(OnlyExcel, "Data", each fxExtractFromWorkbook([Content], SheetName)),
   
   Expanded  = Table.ExpandTableColumn(AddedData, "Data",
       {"Date","Project Number","Customer","Country","Project Name"}),
   
   // Keep just the output columns (you can sort as you wish)
   Trimmed   = Table.SelectColumns(Expanded,
       {"Date","Project Number","Customer","Country","Project Name"}),
   
   // Add No. (1-based sequence)
   Indexed   = Table.AddIndexColumn(Trimmed, "No.", 1, 1, Int64.Type),
   
   // Reorder to your desired layout
   Final     = Table.ReorderColumns(Indexed,
       {"No.","Date","Project Number","Customer","Country","Project Name"})
   
   
   in
   Final

4. Load to your TOTAL sheet
   Close & Load this query to a Table on Overview_2025.xlsx sheet “TOTAL”. Now each refresh scans the folder, picks up new files, extracts C1..C4, splits C1 into Project Number and Project Name, and auto-numbers rows.

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.