Hi @dogbural ,
Yes, you can absolutely do this using Power Query. It's the perfect tool for transforming messy, semi-structured data like the kind extracted from PDF invoices into a clean, usable table.
The general strategy is to create a new, dedicated column for each piece of information you need: the Tax Invoice number, Customer PO#, PO Item #, Part #, and Quantity. You will use Power Query's functions to scan your existing columns, find the rows that contain the labels for your data (like "Customer PO#"), and then pull the corresponding values into your new, clean columns.
You can start this process by adding a custom column for each field. Go to the Add Column tab and select Custom Column. To get the Tax Invoice number, you can use a formula like the one below. This M code checks if a cell in Column1 contains the text "Tax Invoice" and, if it does, it returns the value from the adjacent column, Column2. You will need to adjust these column names to match your specific table.
if Text.Contains([Column1], "Tax Invoice") then [Column2] else null
You will then repeat this process for the other header-level information. For example, to extract the Customer PO number, you would create another custom column using a similar formula, again adjusting the column names as needed.
if Text.Contains([Column1], "Customer PO#") then [Column2] else null
After creating all the new columns for your data, you will notice that the values only appear on single rows, with the rest of the cells being null. To fix this, you need to carry those values down to all the relevant line items. Select the new columns you just created, navigate to the Transform tab, click on the Fill button, and then select Down. This action will populate the null cells with the value from the first populated cell above, correctly associating each line item with its parent invoice and PO number.
The final step is to clean up the table. Now that all your required data is organized in its own columns, you can remove the rows you no longer need. A good way to do this is to filter the "Part #" or "Quantity" column to show only the rows that are not null. This will effectively remove all the original header and miscellaneous text rows. Once that's done, you can simply right-click and remove the original, messy columns from the PDF import, leaving you with the perfectly structured table you need.
Best regards,
