We've seen this scenario play out countless times: a user wrestling with Power Query, trying to flatten nested data, and getting lost in a maze of mismatched columns and unpivot steps. The frustration is real. What this user is up against, eight tables, each with a different number of evaluation columns, scattered across teams and categories, is exactly the kind of problem that makes traditional spreadsheets feel like they're working against you. The good news is that the solution is simpler than it seems, and it starts with a single shift in thinking.
The core issue here isn't technical skill; it's structural design. When you have Team 1 Internal with two evaluation columns and Team 1 External with four, you're not just dealing with inconsistent data, you're dealing with a layout that Power Query was built to untangle. The user already identified the right fix: flatten everything into two columns (Score and Evaluation Number) and add a column for the table name. That's exactly the right instinct. Power Query's unpivot feature does this in seconds, turning wide tables into long, analysis-ready formats. The trick is to apply it consistently across all eight tables, then append them into one clean dataset. No manual reformatting, no copy-paste nightmares.
What makes this situation so common is that most people learn Power Query by patching together solutions from forum threads. They get one step working, then hit a wall when the next table doesn't behave the same way. The solution isn't to find the perfect single query, it's to treat each table as a consistent shape that needs the same transformation. By standardizing the columns (Agent Name, Team, Type, Evaluation Number, Score) before merging, you eliminate the chaos. The user's mention of adding a table name column is the missing link: it preserves context while allowing the data to flow freely into a single, unified view.
This is where AI-native spreadsheets offer a clear advantage. Instead of manually configuring eight unpivot steps and hoping the logic holds, you can describe the desired outcome and let the tool handle the transformation. But even within Power Query, the principle remains the same: flatten first, analyze second. The user's goal, a clean overview that calculates correctly, is entirely achievable by following this sequence. The takeaway? Stop wrestling with the structure. Let the tool do the heavy lifting, and focus your energy on what the data actually tells you.