Messy PDF headers are a classic pain point, and the solution isn't another manual cleanup session. It's a smarter use of Power Query's built-in tools, and the user who posed this question is already on the right track by thinking in terms of folder-level transformation rather than file-by-file fixes. The core issue here isn't the data itself, it's the shifting location of the header row. When dates live in row 27 one week and row 28 the next, you can't rely on a static reference. But Power Query doesn't care about rows the way we do. It cares about patterns.
The practical move is to promote the first row as headers, then filter out the noise before that row. You can use a conditional column or a simple "Table.SelectRows" that checks for the word "Product" or whatever static label exists in your data. Once you've isolated the true header row, you can promote it and let the dates become column names dynamically. The trick is to avoid hardcoding anything. You're not telling Power Query where the header is. You're telling it to find the row that contains your known identifier, then pivot everything below it. That way, when next week's PDF lands in the folder, the query rebuilds itself.
What this means for you is that merging weekly PDFs stops being a chore and becomes a background task. You set up the transformation once, save it as a function, and then invoke it against every file in the folder. The dates that used to break your pivot tables now become part of the flow. You refresh, and your table updates with the latest columns and values. No copy-paste, no VLOOKUP gymnastics, no risk of missing a row because the header moved. The system absorbs the variance instead of forcing you to accommodate it.
The real win here is that you stop fighting the source file and start designing for its behavior. The question isn't "how do I fix this week's PDF?" It's "how do I make my query robust enough to handle whatever lands in the folder?" That's the shift in mindset that separates a one-time fix from a sustainable workflow. You don't need a perfect source. You need a transform that expects imperfection and routes around it. Start by identifying your anchor row, promote it conditionally, and let Power Query handle the rest. Your pivot tables will thank you, and so will your Friday afternoon.