Forum Discussion

melimob's avatar
melimob
Frequent Visitor
6 years ago
Solved

Add Totals Column and Total Row to Pivoted Query for dynamic headers

Hi

This is probably really easy but I'm new to G&T and can't figure how to do this all in the query before I load it.

 

Have a very simple table with columns as:

Year, Consultant, and months across (which are pivoted and have count values in)

 

I want to add a TOTALS sum column and row.

 

I managed to add a column summing rows but if the same months in the original query are not present, it errors or doesn't include.

I'm not sure how to sum columns across.

 

Obviously I can do this once it's loaded in the excel table but I also had isssues when the data set expanded.

 

Advice appreciated.

Many thanks

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    It can, replace everything below your source with the code below in the advanced editor.

     

      ColumnNames = List.Buffer(List.Intersect({Table.ColumnNames(Source), {"January","February","March","April","May","June","July","August","September","October","November","December"}})),
    ChangeTypes = Table.TransformColumnTypes(Source,List.Transform(ColumnNames, each { _, type number })),
    AddTotal = Table.AddColumn(ChangeTypes , "Totals", each List.Sum(Record.ToList(Record.SelectFields(_,ColumnNames))))
in
    AddTotal

     

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    I hope somebody responds with an easier solution, but I use M to handle it. As long as the columns to be summed have dates, then the code is flexilble enought to handle the Source. The process is as follows:

    1) Create a list of columnames that you want to sum using the Table.ColumnNames and List.Select function.

    2) Transform the list of columnnames to a type list that can be sent to ChangeTypes.

    3) Transform the list of columnnames to a type list that can be sent to TableGroup.

    4) Append the TableGroup step (along with a "Total" description in consultant) to the bottom of the ChangeTypes.

    5) Use Record.SelectFields (again colnames list is handy), send to a list and sum up.

     

    Below is sample code that can be pasted into Powerquery.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIEYiMgNgZiEyA2BWIzpVidaKUkrDI6SuZg2WSsMjpKFkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Consultant = _t, #"Mar-19" = _t, #"Apr-19" = _t, #"May-19" = _t, #"Jun-19" = _t, #"Jul-19" = _t, #"Aug-19" = _t]),
    ColumnNames = List.Buffer(List.Select(Table.ColumnNames(Source), each Value.FromText(_) is date)),
    #"Changed Type" = Table.TransformColumnTypes(Source,List.Transform(ColumnNames, each { _, type number })),
    #"Grouped Rows" = Table.Group(#"Changed Type", {}, List.Transform(ColumnNames, each {_,(row) => List.Sum(Record.Field(row, _)), type number  })),
    AddTotalConsultant = Table.AddColumn(#"Grouped Rows", "Consultant", each "Total"),
    AppendTotalToBottom = #"Changed Type" & AddTotalConsultant,
    AddTotalColumn = Table.AddColumn(AppendTotalToBottom, "Total", each List.Sum(Record.ToList(Record.SelectFields(_,ColumnNames))))
in
    AddTotalColumn

     

    • melimob's avatar
      melimob
      Frequent Visitor
      6 years ago

      Hi thank you so much for replying.

       

      I'm not sure if the below can be adapted?

      It works if the source data shows every month however, lets say we haven't added Dec data yet, the totals won't work.

      And vice versa, I want to account for all months for when the data is added so I don't have to re-write the months each time.

       

      <code>

      let
      Source = Table.Combine({tblLifeWritten2, tblMortgagesWritten2}),
      #"Inserted Sum" = Table.AddColumn(Source, "Addition", each List.Sum({[January], [February], [March], [April], [May], [June], [July], [August], [September], [October], [November], [December]}), type number),
      #"Renamed Columns" = Table.RenameColumns(#"Inserted Sum",{{"Addition", "Totals"}})
      in
      #"Renamed Columns"

      </code>

       

      Many thanks

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        It can, replace everything below your source with the code below in the advanced editor.

         

          ColumnNames = List.Buffer(List.Intersect({Table.ColumnNames(Source), {"January","February","March","April","May","June","July","August","September","October","November","December"}})),
    ChangeTypes = Table.TransformColumnTypes(Source,List.Transform(ColumnNames, each { _, type number })),
    AddTotal = Table.AddColumn(ChangeTypes , "Totals", each List.Sum(Record.ToList(Record.SelectFields(_,ColumnNames))))
in
    AddTotal