Power Query: Adding a Total Column

February 25, 2020 - by Bill Jelen

In my original solution, I had added a calculated column with the formula Total = [Q1]+[Q2}+[Q3]+[Q4].

Although I mostly built this formula with the mouse, it would be very tedious to be adding [January]+[February]+[March]+[April]+[May]+[June]+[July]+[August]+[September]+[October]+[November]+[December] or even [Week 1]+[Week 2]+[Week 3]+[Week 4]+[Week 5]+[Week 6]+[Week 7]+[Week 8]+[Week 9]+[Week 10]+[Week 11]+[Week 12]+[Week 13]+[Week 14]+[Week 15]+[Week 16]+[Week 17]+[Week 18]+[Week 19]+[Week 20]+[Week 21]+[Week 22]+[Week 23]+[Week 24]+[Week 25]+[Week 26]+[Week 27]+[Week 28]+[Week 29]+[Week 30]+[Week 31]+[Week 32]+[Week 33]+[Week 34]+[Week 35]+[Week 36]+[Week 37]+[Week 38]+[Week 39]+[Week 40]+[Week 41]+[Week 42]+[Week 43]+[Week 44]+[Week 45]+[Week 46]+[Week 47]+[Week 48]+[Week 49]+[Week 50]+[Week 51]+[Week 52]

Just about everyone knew that there is a much faster way. Oz do Soleil from Excel on Fire sent it first, but others included MF Wong, Mike “Excel is Fun” Girvin, Wyn Hopkins, Geert Demulle, Fowmy, and Josh Johnson.

Click the heading for your first numeric column. Ctrl+Click the heading for the last column.

Then, go to Transform tab, choose Standard, Add:

The result: a new total column:

I am not a big fan of the heading called “Addition”. Right-click the heading and choose Rename. Or to change the heading without adding a step, click in the Formula bar and type “Total” instead of “Addition”. I first noticed this cool way to rename a column in a video sent in by Josh Johnson.

Return to the main page for the Podcast 2316 challenge.

Read the next article in this series: Power Query: Using Else If Clauses in Conditional Columns.