Calculate Column Total for multiple columns

[nixoniraj]

Dear Team,

This query is for Power Query Editor in Power BI

I have an original Table like this :

I want to take column total for each country at last part of the table (ie, after 9. Sep) like this :

Anyhelp would be much appreciated for showing stepwise process.

[BA_Pete]

Hi @nixoniraj ,

You just need to use Table.InsertRows with List.Sum functions on the country columns:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtRT8ErMU9JRcq0oyC8qKQayTA0MgKQFkIzViVYy0lNwS01CUWBuClJgZABVYayn4JtYhKLCCKZCKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Type = _t, India = _t, China = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"India", Int64.Type}, {"China", Int64.Type}}),
    
    insertRow =
    Table.InsertRows(chgTypes, Table.RowCount(chgTypes),
        {
            [
                Month = "Grand Total",
                Type = null,
                India = List.Sum(chgTypes[India]),
                China = List.Sum(chgTypes[China])
            ]
        }
    )
in
    insertRow

Output:

Please note, however, that I would strongly recommend against using Power Query for this sort of thing. It's really not what it's designed for.

Pete