This user's problem is a textbook case of spreadsheet complexity outpacing the tools designed to manage it. Power Query is a capable tool, but it was built for tidy, uniform data, not for the messy reality of compound headers, missing columns, and inconsistent layouts. The request here is reasonable: merge dozens of files while preserving every column, even when those columns don't appear in every sheet. The fact that Power Query struggles with this isn't a user failure; it's a tool limitation.
The core challenge is that the column headers are spread across four rows, each carrying distinct meaning, result type, cycle number, analyte name, unit. That's not a single identifier; it's a composite key. Power Query can flatten those rows into a single header, but it requires manual steps like transposing, merging text, and promoting headers. When files differ in which columns exist, the process breaks down further. The user is left with two bad options: either copy-paste by hand, or write a custom script that most analysts don't have time to maintain.
This is exactly where an AI-native approach changes the game. Instead of forcing the user to pre-process every file into a rigid schema, a smarter system can infer the structure. It can recognize that rows 1 through 4 are a multi-level header, combine them into a single meaningful column name, and then perform a union of all columns across all files, keeping every unique column that appears at least once. It can also add a source file column automatically, because the user explicitly needs to trace data back to its origin date. That's not a nice-to-have; it's a requirement for the trending analysis they described.
What this user really needs is a tool that meets them where they are, not one that demands they reshape their data into a pre-approved format. Power Query is a workaround for a spreadsheet problem that should have been solved years ago. An AI-native spreadsheet can handle compound headers, variable schemas, and file-level metadata out of the box. The practical takeaway is this: if you're spending more time wrangling headers than analyzing results, the tool is the bottleneck. The solution is to use a tool that treats your data's actual structure as valid input, not as an inconvenience to be worked around.