This user is asking the right question, and the answer matters more than they might realize. The choice between Power Query and Excel formulas for calculating average annualized salary across complex teams is not just a technical preference, it is a fork in the road that determines whether your workflow stays nimble or becomes a maintenance burden.
The scenario is a classic data normalization problem. One employee has two rows because they split their time across departments and salary grades. To get a company-wide average, you need to collapse those rows into a single employee record weighted by FTE percentage. To get the average excluding Department B, you need to remove those rows entirely before grouping. Both approaches work, but they are not equally sustainable.
Power Query is the better choice here, and not because it is flashy. It is better because it separates the transformation logic from the output. You build the grouping and filtering steps once, and when next quarter's data arrives, you refresh the queries. No rechecking formulas. No accidentally dragging a SUMIF across the wrong range. The two-table approach the user describes, one with Department B filtered out, one without, is exactly how you keep the logic explicit and auditable. Excel formulas will work, but they embed the calculation inside the spreadsheet structure, making it harder to trace when something breaks or when a coworker needs to understand what you built.
The practical takeaway is this: when your data has a many-to-one relationship, one person, multiple rows, your tooling must match that complexity. Power Query handles that matching natively. Excel formulas require you to manually enforce it through array logic or helper columns, which is where errors creep in. The user's instinct to reach for Power Query is sound, and the only refinement worth adding is to store the output as a proper data model rather than as static tables, so the pivot tables can pull directly from the query results without a manual refresh step.
Stop debating the method and start building the query. Set up your two grouped tables in Power Query, load them as connections only, and let the pivot tables do the averaging. That setup takes ten minutes and saves you ten hours of troubleshooting later. The spreadsheet should serve you, not the other way around.