Write Power Query like a LAMBDA with Claude when every sheet looks different
Describe the pieces you need (anchor column, labels, date row, cash row), have Claude draft a named M query that finds values by what they are, prove it on one sheet, wrap it as a function, then drive it across every sheet.
Tools used
- ClaudeDrafts the LAMBDA-shaped M query from your piece list
- Power QueryRuns the named pieces and the per-sheet function
- ExcelHolds the multi-sheet workbook and the final table
Step navigator
One driver query that calls a sheet function and returns one clean table (property × year) from many differently laid-out sheets.
Excel users comfortable with Power Query who face many sheets with drifting layouts, not a single tidy table.
Before you start
David Bahlavooni (The New Excel, October 2026) had a real-estate workbook with 135 property sheets in two layouts and cash-flow rows that moved around. Claude proposed an M query shaped like a LET/LAMBDA: independent named pieces, combined at the end, no hard-coded cell addresses. He checked every piece against the real sheets before wrapping it as a function. This guide follows that post. Exact prompts he typed are not published, so those are gaps.
Step-by-step instructions
List the independent pieces you must find on every sheet
Bahlavooni's sheets needed three outputs (property name, years, total cash collected) but layouts differed and the cash row moved. He broke the problem into named pieces Claude could target: key/anchor column, property name, date header row, which years to keep (from master-workbook parameters), and the row labeled "Total Cash Collected".
Why this works
If you cannot name the pieces, Claude will invent a brittle cell-address recipe.
Ask Claude for a LAMBDA-shaped M query, not a linear Applied Steps chain
When he outlined the problem, Claude's draft "didn't look like a line at all. It looked like a LAMBDA." Each piece was a named binding; the final Output combined them. Importantly: "no cell addresses. Every value is found by what it is, not where it sits." That is why one query survived 135 sheets. He did not publish the chat prompt itself.
Draft Power Query M that finds these pieces by label and shape, not cell address: anchor column, property name, date header row, years to keep from my parameters, and the Total Cash Collected row. Bind each as a named step like LET, then build one output table at the end.
Why this works
The prompt text above restates his piece list; his exact wording is unpublished.
Prove the standalone query on one visible sheet before anything becomes a function
His recommended pattern, layer 1: "A standalone query for one sheet. Before anything is a function, get it working on a single sheet you can see. Check every piece against the real values." Only when property name, year columns and cash row all match do you move on.
Why this works
A wrong piece on sheet 1 becomes 135 wrong rows once you fan it out.
Wrap the same logic as a function of the sheet name
Layer 2: "Wrap the query in a function that takes a sheet name. Nothing inside changes. It just stops being hard-wired to one sheet."
Add a driver that lists every sheet and expands the results
Layer 3: "A short query lists every sheet in the workbook and calls the function once per sheet. Expand the results, and you have one table: one row per property, one column per year."
Keep Claude as the drafter; you integrate and stand behind the workbook
Closing line of the post: "Claude proposed the query. I checked his results against the real sheets, integrated it into my project, and delivered the finished workbook." In his companion post he refuses to let Claude apply changes unattended: Claude explains and offers a fix, and only then does David decide.
Why this works
The finished workbook is yours to defend; Claude never becomes the author of record.
End of guide · Reviewed Oct 29, 2026
Pro tips2
- Find values by label and shape, not by cell address, or the next layout break will kill the query.
- Keep Claude as the drafter: he proposes M, you verify against real sheets before it ships.
Common mistakes2
- Building only in the Applied Steps chain when the pieces are independent; Bahlavooni found the linear shape hid the better design.
- Letting Claude edit the workbook unattended; in his earlier post he keeps Claude from applying changes until he approves.
FAQ2
Is this the same as combining monthly files?
Do I need the Claude Excel add-in?
Related field guides.
Turn a recorded macro into reusable VBA with ChatGPT, then test it on a copy
Record the task, have ChatGPT clean up and comment the code and make the ranges dynamic, then compile, step through and test it on a copy before it touches real data.
Run a Python analysis in Excel with Copilot's Advanced Analysis
Put the data in a Table in a cloud-saved workbook, start Advanced Analysis in Copilot, let it write Python cells, and read each one before you reuse the code.
Get up to speed on an inherited workbook before you let AI change it
Work on a copy, inspect connections and named ranges, trace backwards from the outputs you need, and only then ask Copilot to describe what a sheet does.
Decode a long Excel formula with Evaluate Formula and an LLM
Paste the formula into ChatGPT or Claude for a plain-English explanation, then confirm each part with Evaluate Formula and F9 so you aren't relying on a wrong answer.
Extend an existing financial model with Claude in Excel without breaking it
Have Claude explain how the tabs connect and annotate complex formulas first, then extend the model following the original patterns while you check its assumptions.
Combine monthly files with Power Query, using an LLM one step at a time
Point Power Query at a folder of same-shaped files, then use ChatGPT or Copilot only to write or explain individual M steps, testing each one before adding the next.
Write complex Excel formulas with Copilot, then prove they work
How r/excel users actually use Copilot for formulas: describe each field, take the draft as a starting point, test it on awkward rows, and check every reference before filling down.
Automate a recurring Excel report: deterministic Python, AI only for the fuzzy parts
The pattern practitioners recommend on r/AI_Agents and r/automation: scripts for ingest, transform and validate, an OpenAI or Claude API call only for fields that need interpretation, and a hard stop when totals don't reconcile.