Calculate Row Total and Column Total in Power Query

[v-yiruan-msft]

Scenario: 

In Power BI we can simply add up each column to get the column total, but we normally cannot add row total directly to the table. This blog will introduce you how to add column totals and row totals to a table in Power Query.

Sample data: 

Let's take the following simple table as an example:

Expected result:

Detailed steps: 

Method 1:  Use the List.Zip() function

1. Use Table.ToRows() to decompose Table into multiple columns in units of rows
2. Use List.Transform() and List.Sum() to construct with column group of row total. These new lists will be transformed by List.Zip(), then List.Transform() and List.Sum() will be used again to construct lists with the column total.
3. Use Table.FromColumns() to restore the column group to new table.

The full applied codes as follow:

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIBYnOlWJ1oJSMgyxSILcA8YyDLDIgtlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}}),

    ToRows = Table.ToRows( #"Changed Type" ),

    RowTotal = List.Transform( ToRows, each _ & { List.Sum(_) } ),

    Zipping = List.Zip( RowTotal ),

    ColTotal = List.Transform( Zipping, each _ & { List.Sum(_) } ),

    New_Table = Table.FromColumns( ColTotal )

in

New_Table

The result would be like below:

Method 2:  use Table.Transpose() function

1. Create a custom column as below to get the column total like below using List.Sum() function

But for row total we cannot directly get it. What we need to do is to use Table.Transpose() to transpose the above table into below table:

TransposeTable = Table.Transpose( ColumnTotal )

Then we need to calculate the new column totals again.

2.  Convert the table back again by using Table.Transpose() function

The full applied codes as follow:

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIBYnOlWJ1oJSMgyxSILcA8YyDLDIgtlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}}),

    ColumnTotal = Table.AddColumn(  #"Changed Type", "Column Total", each List.Sum( Record.FieldValues(_) ), Int64.Type ),

    TransposeTable = Table.Transpose( ColumnTotal ),

    RowTotal = Table.AddColumn(TransposeTable, "RowTotal", each List.Sum( Record.FieldValues(_) ), Int64.Type),

    Table2 = Table.Transpose( RowTotal )

in

   Table2

Summary:

These are the two methods to get column totals and row totals in Power Query. The principle is to transpose the rows and columns of the table and then calculate the sum. In addition to the two methods, there are other functions that can get the same result (such as the List.TransformMany() function), which is the charm of Power Query. Thanks for reading.

Author: Jay Wang

Reviewer: Ula Huang, Kerry Wang

Calculate Row Total and Column Total in Power Query.pbix