Forum Discussion
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.
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.
That's a good place to start from. I'll look at implementing these suggestions.
Getting data from PostGres is simply not happening. They're historical election results, so they don't change. I was comfortable just slopping them all out onto the worksheet. There's also only four ballot types, so a total of eight wide tables, I'm sorry if I was unclear on that. (Then and again, this place has a large number of absentee and provisional subtypes that get counted separately but usually get rolled up into one district wide special precinct in the publicly released counts. They may ultimately need to be included.)
Part of the problem with both this mess and the original morass of a PostGres system was that I generally did the first thing that worked and gave me an answer rather than sit down and think out in advance what the optimal solution would be.
In this case, for a previous attempt at doing only what was needed to generate the final visual table I wanted, I used the following process without Power Query. Dumping out the data into the big worksheet wide tables for each ballot type, using Excel GROUPBY to aggregate on subsequent totals worksheets and then calculating the percentages on an additional worksheet referencing each of the previous set, then another sheet calculating the percentage point differences and a final one using GROUPBY on that sheet to get the averages by year. That system basically got copied and pasted into the current, more complex Excel sheet when I started working on it and I only discovered Power Query midway through the job.
If I'm understanding your point 5) correctly, keep the intermediate queries in Power Query as connection only and do not export them to Excel worksheet as tables. Have I got that right?
Yes, that’s exactly right.
If the source data is static and already dumped into Excel tables, you’ll get the best performance by:
Keeping all intermediate Power Query steps as “Connection only” (don’t load them to worksheets)
Loading only the final outputs you actually need (e.g., CandidatesAgg, MeasuresAgg, Final comparison)
Reasos is that every “Load to sheet” creates a materialized table that Excel has to store, refresh, and sometimes recompute dependencies for, which slows everything down.
A practical setup for your case:
Raw wide tables (loaded once)
Your 4 ballot types × (candidate + totals) etc. stay as the raw tables.Power Query staging queries (Connection only)
Append ballot types into one long table (add BallotType)
Clean / select only needed columns
Group to your required grains (year/party/geo)
Final query outputs (Load to sheet)
Only load:
the final comparison table(s) you chart/report from
any small “check my work” tables you want the reviewer to see
One more tip since you’re no longer using PostgreSQL: Append first, then Group By (so you group once). That will usually be much faster than doing “group per ballot type then merge”.