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

[Strunec16]

Hi,

I have a folder with many Excel files (around 150 per year).
Each file has the same standardized layout (not formatted as a Table, just ranges).

From each file I only need to extract a few specific cells and combine them into one row in a master file (Overview_2025.xlsx) on a sheet called TOTAL.

For example, from each file I need:

• Cell C4 → Date

• Cell C1 → Project Number (first 9 characters)

• Cell C1 → Project Name (characters after 10th)

• Cell C2 → Customer

• Cell C3 → Country

The goal is:

• Each source file = one new row in the TOTAL sheet

• Columns:

  • A = No. (sequential number, auto-increment)

  • B = Date

  • C = Project Number

  • D = Customer

  • E = Country

  • F = Project Name

I want this to refresh automatically when new files are added to the folder.
The master file should not break if I move it (e.g., to email it), and when I reconnect it back to the folder, the refresh should still work.

Is this possible to achieve with Power Query, given that my source files only have ranges (not tables)? Or do I need to convert my ranges to proper Excel Tables first to make this work reliably?

Please give me some direction on where to look for help.

Thanks!

[tayloramy · Accepted solution]

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.

[Strunec16]

Thanks for your time, i will try this solution.