How to sum N columns in Power Query

Question

My data gets updated every month so I'm trying to create a power query table that would show the sum of the pivoted (N) columns that I created but I can't seem to figure out how to do it in power query.

I have this code currently:

Answer 1

• After Pivoting:
• Create a list of the columns to sum
• Add an Index column to restrict to each row
• Add a column which Sums the columns for just that row
• Remove the Index colum

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month Yr", Date.Type}, {"Attribute", type text}, {"Value", Currency.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "MonthYear", each Date.ToText([Month Yr],"MMMM yyyy")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Month Yr"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[MonthYear]), "MonthYear", "Value", List.Sum),

//NEW code added after your Pivoted Column line

//Get List of columns to sum
//  Assumes this list all columns **except the first** in the Pivot table
//  There are other methods of generating this list if this assumption is incorrect
colToSum = List.RemoveFirstN(Table.ColumnNames(#"Pivoted Column"),1),

//Add Index Column
IDX = Table.AddIndexColumn(#"Pivoted Column","Index",0,1),

//Sum each row of "colToSum"
totals = Table.AddColumn(IDX, "Sum", each List.Sum(
        Record.ToList(
            Table.SelectColumns(IDX,colToSum){[Index]})
    ), Currency.Type),
    #"Removed Columns1" = Table.RemoveColumns(totals,{"Index"})


in
#"Removed Columns1"