Consolidating the same sheet from many team workbooks

Case study 5 of 5. Names and figures are made up, and the code is a clean reconstruction. See all the lessons.

Several teams each keep their own workbook with an internal journal sheet, saved to a cloud-synced shared folder. Finance needed one consolidated journal across all of them, refreshed on demand, with no copy-pasting.

I’ve covered the basics in combining a folder of monthly files. Team workbooks are harder, because each one is maintained by different people.

What made it hard

• A moving file list: workbooks get added, renamed or left behind as temporary copies.
• Inconsistent workbooks: some have extra sheets, slightly different headers, or blank rows above the data.
• Sync clutter: cloud-synced folders hold lock files (names starting with ~$) and conflict copies.
• Traceability: every consolidated line has to show which file it came from.

The query

The folder location is a query parameter, FolderPath, so it isn’t hard-coded.

let
    Files  = Folder.Files(FolderPath),
    Real   = Table.SelectRows(Files, each
               Text.Lower([Extension]) = ".xlsx" and
               not Text.StartsWith([Name], "~$") and
               not Text.Contains(Text.Lower([Name]), "conflict")),

    GetJournal = (bin as binary) as table =>
        let
            Wb    = Excel.Workbook(bin, null, true),
            Sheet = Table.SelectRows(Wb, each [Kind] = "Sheet" and Text.Upper([Name]) = "JOURNAL"),
            Data  = if Table.IsEmpty(Sheet) then #table({}, {}) else Sheet{0}[Data],
            // Skip any blank rows above the headers
            Body  = Table.Skip(Data, each List.NonNullCount(Record.FieldValues(_)) = 0),
            Hdr   = Table.PromoteHeaders(Body, [PromoteAllScalars=true]),
            Trim  = Table.TransformColumnNames(Hdr, Text.Trim),
            Keep  = Table.SelectColumns(Trim,
                      {"Date","Account","Class","Memo","Debit","Credit"}, MissingField.UseNull)
        in
            Table.SelectRows(Keep, each [Account] <> null),

    WithData = Table.AddColumn(Real, "Data", each GetJournal([Content])),
    Slim     = Table.SelectColumns(WithData, {"Name","Data"}),
    Combined = Table.ExpandTableColumn(Slim, "Data",
                 {"Date","Account","Class","Memo","Debit","Credit"}),
    Renamed  = Table.RenameColumns(Combined, {{"Name","SourceFile"}}),
    Typed    = Table.TransformColumnTypes(Renamed,
                 {{"Date", type date}, {"Account", type text}, {"Class", type text},
                  {"Memo", type text}, {"Debit", type number}, {"Credit", type number}})
in
    Typed

How it handles the mess

Problem	What handles it
Lock files and conflict copies	The filters in Real
Extra sheets	Finding the sheet by name, in any letter case
A workbook with no journal sheet	An empty table instead of an error
Blank rows above the data	Table.Skip with a condition
Stray spaces in headers	Table.TransformColumnNames with Text.Trim
A missing column	MissingField.UseNull

Doing this inside a function means each workbook is cleaned before it’s combined. Types are then set once, on the combined table.

The output

SourceFile	Date	Account	Class	Debit	Credit
Team_North.xlsx	2026-06-30	5300 Field Expenses	North	12,500.00
Team_North.xlsx	2026-06-30	2100 Accrued Payables	North		12,500.00
Team_South.xlsx	2026-06-30	5300 Field Expenses	South	8,750.00

With SourceFile on every line, a wrong number can be traced straight back to its owner.

The catch with forgiving code

Each safeguard here can also hide a problem. A team’s workbook with no journal sheet returns no rows, and a misspelled header becomes an empty column, and neither one raises an error.

So add a small check next to the output: count rows per SourceFile, and compare the list against the teams you expect. A missing team or a sudden drop then stands out straight away.

The result

One consolidated journal from all the team workbooks, refreshed in a single step.