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

[melimob]

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

[Anonymous]

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]

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 · Accepted solution]

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

[melimob]

oh thank you thank you thank you!

worked perfectly! you've made me so happy thank you!

[Anonymous]

I am glad it worked for you! 😊

[melimob]

Hi

So sorry to ask for help again but I tried to use your code within another query.  Tried various ways and it's not working the way I need it.

Here's the code:

let
Source = NWDataCountTbl,
#"Filtered Rows" = Table.SelectRows(Source, each ([Policy Cat Written Aborts] = "Mortgages Written") and ([Rebook] = "Mortgage")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Category Count", each "Mortgages Written"),
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)))),
#"Removed Other Columns1" = Table.SelectColumns(#"Added Custom",{"Category Count", "Policy Cat Written Aborts", "Consultant", "Written Month", "Written Year"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns1",{"Category Count", "Written Month", "Policy Cat Written Aborts", "Written Year", "Consultant"}),
#"Pivoted Column" = Table.Pivot(#"Reordered Columns", List.Distinct(#"Reordered Columns"[#"Written Month"]), "Written Month", "Policy Cat Written Aborts", List.Count)
in
#"Pivoted Column"

Also, for some reason I am now getting a 'November' and a 'November 2' which is blank?  Any ideas to solve?

many thanks

[Anonymous]

My best guess is that the [#"Written Month"] field has a November 2 in the data. Do you see this value if you filter on the #"Reordered Columns" step?

I have a separate question - is #"Pivoted Column" showing the Totals Column? The query appears to be ignoring steps ColumnNames thru AddTotal. My gut feel is that the code should look more like the below. It might fix theissue.

let
Source = NWDataCountTbl,
#"Filtered Rows" = Table.SelectRows(Source, each ([Policy Cat Written Aborts] = "Mortgages Written") and ([Rebook] = "Mortgage")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Category Count", each "Mortgages Written"),


#"Removed Other Columns1" = Table.SelectColumns(#"Added Custom",{"Category Count", "Policy Cat Written Aborts", "Consultant", "Written Month", "Written Year"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns1",{"Category Count", "Written Month", "Policy Cat Written Aborts", "Written Year", "Consultant"}),
#"Pivoted Column" = Table.Pivot(#"Reordered Columns", List.Distinct(#"Reordered Columns"[#"Written Month"]), "Written Month", "Policy Cat Written Aborts", List.Count)

ColumnNames = List.Buffer(List.Intersect({Table.ColumnNames(#"Pivoted Column" ), {"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