#2 sum over columns - Bill Szysz.pq

let
    /*
       Creating a list of monthnames... format and language of the list can 
       easily be changed by the last two parameters
    */
    MonthsList = List.Transform({0..11}, each Date.ToText(#date(1900,1,1) + 
                                              Duration.From(_*31), "MMMM", "en-GB")),

    Source = Excel.CurrentWorkbook(){[Name="Datenbasis"]}[Content],

    //Get all column names
    ColNames = List.Buffer(Table.ColumnNames(Source)),
    
    //Get all Month columns
    ColsToSum = List.Buffer(List.Intersect({ColNames, MonthsList})),
    
    /*
       Determining the headers for the sum-table... 
       - column1 is the column with the cost centres, where we want to see 
         the word 'TOTAL' in the last row
       - all the other columns are month columns with the sum value in the 
         last row
       - All other columns that may occur should not be summed... 
         this is why they are not included in this step 
    */
    Headers = Table.Buffer(Table.FromColumns({{ColNames{0}} & ColsToSum})),

    /*
       Creating a table with the column names of the source table in column1
       and the values of the source table in lists in column 2
    */
    Custom1 = Table.FromColumns({ColNames, Table.ToColumns(Source)}),
    
    
    /*
      The merge results in the column name for the first column 'Cost centre' 
      and all month columns. However, it returns a NULL value for all other 
      columns that may occur
    */
    #"Merged Queries" = 
        Table.NestedJoin(
            Custom1, {"Column1"}, Headers, {"Column1"}, "Custom1", JoinKind.LeftOuter
            ),
    
    // Expanding the columns leads to column names or NULL value
    #"Expanded {0}" = 
     Table.ExpandTableColumn(
           #"Merged Queries", "Custom1", {"Column1"}, {"Column1.1"}
     ),
    
    /*
       Depending on the result of the join, generate the following calculated column
    */
    #"Added Custom" = 
     Table.AddColumn(
        #"Expanded {0}", 
        "ColumnsValue", each
             // if the join returns 'Cost centre'
             if [Column1.1] = ColNames{0} then 
                //then create a combined list of Costs centres and 'Total'
                [Column2] & {"Total"} 
             else 
                //if the join doesn't return 'Cost centre', but is not NULL then
                if [Column1.1] <> null then
                   /* 
                     Create a combination the list of values and 
                     the sum of values in the bottom line
                   */
                   [L = List.Sum([Column2]), N = [Column2] & {L}][N] 
                else
                   /*
                     if the value is NULL, then return only 
                     the values, but create no sum 
                   */
                   [Column2]
                ),
    
    /*
      Create a final table from the values column and 
      the column 1, which includes the column headers
    */
    TblFromCols = 
       Table.FromColumns(
           #"Added Custom"[ColumnsValue], #"Added Custom"[Column1]
        )
in
    TblFromCols