Forum Discussion

nixoniraj's avatar
nixoniraj
Frequent Visitor
3 years ago

Calculate Column Total for multiple columns

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. Se...
Need Help
BA_Pete's avatar
BA_Pete
Super User
3 years ago

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