August 2, 2023
I have used a data analysis software for "messy" PDF to Excel conversions at a previous job and am curious if Power Query is a tool that could be utilized similarly where I'm at now. I'm very much a beginner with Power Query at this time.
Please see the attached file exemplifying my question. I would like to create an output that lists, for each individual:
1) Name ("Last, First")
2) A column value for specific row specified (e.g. the "Contribution" amount in the "ESOP - Stock Cost" row)
The report may vary from 75 to 100 pages but consistently have the same structure (multiple individuals, the columns displayed, row descriptions, etc.). The report is generated by a 3rd party who will charge me a fee for the "custom" report I'm after.
Saving the PDF directly as an Excel file is a debacle, with merged and inconsistent columns throughout.
Power Query generates many different tables when the file is uploaded. I suspect I may spend as much time sorting through and linking these tables in Power Query as I would just powering through and hard keying the amounts I want.
Can anyone confirm if Power Query is the tool for this task (with minimal future manual effort), if there are alternative methods better suited, or if I'm out of luck altogether?