Add Row and Column Total in Excel and Power Query

Today we are going to discuss about numerous methods in Excel and Power Query on how to add row and column totals.

In today's post, I will demonstrate 3 techniques.

Our source data consists of 5 * 3 Grid.

Method-1:

Choose the input range, including an extra row and column to allow for totals. You can use the shortcut key (ALT + =).

Method-2:

=REDUCE(B3:D7,{1,2},LAMBDA(a,v,TRANSPOSE(HSTACK(a,BYROW(a,SUM)))))

=REDUCE(initial_value,array,function)

initial_value -> input data in B3:D7,

array -> We intend to perform 2 iterations, so ensure there are 2 items inside curly braces. These can be anything like {0,0}, {1,1}, or {"",""}.

function -> REDUCE is a LAMBDA helper function, so begin with LAMBDA and declare 2 variables,

a -> Accumulator,

v -> Current Value.

As mentioned, there will be 2 iterations, and during the first iteration,

'a' refers to our input range B3:D7,

The BYROW function helps us obtain row totals, and the HSTACK function allows us to horizontally stack the input range and row totals.

The TRANSPOSE function will assist in transposing the table with the row total.

Now, the row total column will become the last row, and this table will serve as the initial value for the next iteration.

If we perform the same transformation in the 2nd iteration, our table will revert to its original position with row and column totals.

Method-3:

= Table.AddColumn(Source & Table.FromRows({List.Transform(Table.ColumnNames(Source), (f) => List.Sum(Table.Column(Source,f)))},Table.ColumnNames(Source)),"Total", each List.Sum(Record.ToList(_)))

'Source' is our input table.

Table.ColumnNames(Source) -> {"Column1","Column2","Column3"}

Selects the column names from our input table and presents them in a list format.

List.Transform(Table.ColumnNames(Source), (f) => List.Sum(Table.Column(Source,f))) -> {421,194,207}

The List.Transform function helps in modifying each element in the list.

The Table.Column function extracts a column and returns it as a list. The List.Sum function will add up the numbers in the list.

Using curly braces around List.Transform allows us to convert the list into a nested list structure, which can then be used as input for the Table.FromRows function.

Currently, we have a single-row table with a column total. Then, the Table.AddColumn function assists us in adding a row total.