Forum Discussion
Add Totals Column and Total Row to Pivoted Query for dynamic headers
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
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
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
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 AddTotaloh thank you thank you thank you!
worked perfectly! you've made me so happy thank you!
