Power Query Can't Seem to Deal With Calculating and Comparing Percentages

Power Query isn’t bad at percentages, it’s getting slow because your design forces it to repeatedly materialize and merge intermediate results, and (most importantly) you’re likely losing query folding back to PostgreSQL once you start doing complex/nested transforms. When folding breaks, Power Query pulls more data locally and does joins/grouping in the mashup engine, which tanks performance.

A better pattern (very similar to what you ended up doing with CTEs) is:

1) Normalize first, then aggregate once
Right now you have 8 “wide” tables per subject (candidates + totals + measures) and you keep merging them. Instead:

• Append the 8 ballot-type tables into one fact table for candidates with a column BallotType

• Append the 8 ballot-type tables into one fact table for measures with a column BallotType

This removes the need for many separate merges.

2) Pre-aggregate before you merge
Do the heavy Group By first (Year, Party, BallotType, Geography), compute totals and percentages, and then merge the small aggregated tables.

Example approach:

• CandidatesAgg: group by Year, Party, BallotType, Precinct (or Jurisdiction) → sum votes, sum total votes, then %

• MeasuresAgg: group by Year, Measure, BallotType, Precinct (or Jurisdiction) → sum yes/no, total, then %

Then merge CandidatesAgg ↔ MeasuresAgg on Year + BallotType + Geography. That join is tiny compared to joining raw rows.

3) Keep folding as long as possible
In Power Query:

• Do filtering, column selection, type changes early

• Avoid steps that commonly break folding before your final aggregation/merge (custom functions, some “Add Column” with complex logic, merging too early, etc.)

• Right-click a step → View Native Query. If it disappears, folding broke before that step.

If folding stays, PostgreSQL will do the joins/grouping fast.

4) Stop duplicating queries for performance
Duplicating often makes things worse. Prefer a single query with clear steps (like a CTE) and only reference the final small output if needed. The real win is folding + pre-aggregation.

5) If you’re exporting to Excel for a layperson
Consider exporting the final aggregated outputs (CandidatesAgg, MeasuresAgg, and the comparison table), not the entire intermediate chain. That keeps refresh light and the workbook understandable.