# 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. - **Category:** [Excel](https://stepgleam.com/categories/excel) - **Difficulty:** Intermediate - **Time:** ~45 minutes - **Tools:** Claude, Power Query, Excel - **Reliability score:** 84/100 - **Steps:** 6 - **Last updated:** 2026-10-29 - **Published:** 2026-10-29 ## Tools used - [Claude](https://claude.ai) — Drafts the LAMBDA-shaped M query from your piece list - [Power Query](https://support.microsoft.com/en-us/office/about-power-query-in-excel) — Runs the named pieces and the per-sheet function - [Excel](https://support.microsoft.com/excel) — Holds the multi-sheet workbook and the final table ## What you'll get One driver query that calls a sheet function and returns one clean table (property × year) from many differently laid-out sheets. ## Who this is for 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. ## Steps ### Step 1: List the independent pieces you must find on every sheet **Tool:** Excel 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. [Open step 1](https://stepgleam.com/guides/write-power-query-like-a-lambda-with-claude-for-messy-sheets#step-1) ### Step 2: Ask Claude for a LAMBDA-shaped M query, not a linear Applied Steps chain **Tool:** Claude 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. **Why this works:** The prompt text above restates his piece list; his exact wording is unpublished. **Prompt:** ``` 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. ``` [Open step 2](https://stepgleam.com/guides/write-power-query-like-a-lambda-with-claude-for-messy-sheets#step-2) ### Step 3: Prove the standalone query on one visible sheet before anything becomes a function **Tool:** Power Query 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. [Open step 3](https://stepgleam.com/guides/write-power-query-like-a-lambda-with-claude-for-messy-sheets#step-3) ### Step 4: Wrap the same logic as a function of the sheet name **Tool:** Power Query 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."* [Open step 4](https://stepgleam.com/guides/write-power-query-like-a-lambda-with-claude-for-messy-sheets#step-4) ### Step 5: Add a driver that lists every sheet and expands the results **Tool:** Power Query 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."* [Open step 5](https://stepgleam.com/guides/write-power-query-like-a-lambda-with-claude-for-messy-sheets#step-5) ### Step 6: Keep Claude as the drafter; you integrate and stand behind the workbook **Tool:** Claude 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. [Open step 6](https://stepgleam.com/guides/write-power-query-like-a-lambda-with-claude-for-messy-sheets#step-6) ## Pro tips - 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 mistakes - 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. ## FAQ ### Is this the same as combining monthly files? No. The monthly-files guide appends similar files. This one extracts the same fields from many differently laid-out sheets inside one workbook. ### Do I need the Claude Excel add-in? Bahlavooni used Claude (add-in and chat). Any Claude surface that can return M code works if you paste it into Power Query yourself. --- Canonical guide: https://stepgleam.com/guides/write-power-query-like-a-lambda-with-claude-for-messy-sheets Read this guide as Markdown: https://stepgleam.com/guides/write-power-query-like-a-lambda-with-claude-for-messy-sheets.md