We have a clear opinion on this: the struggle to extract tax data from a pile of PDFs is not a personal failing; it is a tool failure. Power Query is a powerful feature, but it was never designed to handle the messy reality of scanned documents, inconsistent form layouts, and the sheer variety of W-9s that land in an inbox. When a user says they can get file names but not file contents, that is not a skill gap; that is a fundamental limitation of the technology they are being asked to use.
What this means in practical terms is that the manual work, opening each PDF, copying the taxpayer ID, pasting it into a spreadsheet, is not a step you can optimize away with traditional spreadsheet functions. Power Query works beautifully when your data is already structured in rows and columns inside a folder. It fails when the data is trapped inside a PDF's visual layout. The user's experience mirrors what many professionals discover: the tools they know well simply were not built for this job. The frustration is real, and it is not the user's fault.
The better path forward is to use a spreadsheet that can read PDFs natively. Modern AI-native spreadsheet tools can interpret the text and layout of a PDF, extract the fields you need, company name, tax ID, address, and place them into rows automatically. This eliminates the need to batch-process files through Power Query or to write complex VBA macros. The process becomes: drag the PDFs into the spreadsheet, let the AI parse them, and then organize the results. For end-of-year accounting, this means you end up with a clean table of vendor data that you can sort, filter, and export without ever opening each individual form.
The concrete takeaway is straightforward: the goal is not to become better at forcing Power Query to read PDFs. The goal is to choose a tool that treats PDF data as a first-class input. When a spreadsheet can understand what a W-9 actually contains, the manual extraction step disappears. That is the real simplification.