This is a problem that should be simple, and it is frustrating that Power Query makes it feel like a puzzle. The user, Difficult_Cricket319, is doing everything right, renaming columns to avoid conflicts and setting tables to connection-only, and still hitting a wall trying to average six scores across two tables.
The core issue here is that Power Query expects you to think in steps, not in outcomes. When you merge two tables on Agent, you get a nested column. To average six scores, you need to expand that nested table, then unpivot the resulting columns so every score becomes a row in a single column. Only then does the average function see all six values together. The user is likely trying to average the columns directly after the merge, which only processes the first table's scores because the second table's data is still locked inside that nested structure.
What this means in practical terms is that the solution is not about a better formula, it is about changing how you approach the data's shape. In Power Query, averages are calculated vertically, not horizontally. The tool is built for rows of data, not rows of calculations. By unpivoting the scores into a single column, you transform the problem from "average these six separate columns" to "average this one column of six values." That is the mental shift that makes the query work.
Our advice: take the merged table, select all the score columns, and use the "Unpivot Columns" command in the Transform tab. Then group by Agent and average the resulting Value column. This approach also scales naturally, if you later add Score5 or Score6 to a table, the unpivot catches them automatically. The user is not doing anything wrong; they are just thinking in spreadsheet terms inside a tool that rewards database thinking. The fix is one click and one group-by away.