cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Solution Specialist
Solution Specialist

Re: 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

 

View solution in original post

7 REPLIES 7
Highlighted
Solution Specialist
Solution Specialist

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

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

 

Highlighted
Frequent Visitor

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

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

Highlighted
Solution Specialist
Solution Specialist

Re: 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

 

View solution in original post

Highlighted
Frequent Visitor

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

oh thank you thank you thank you!

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

Highlighted
Solution Specialist
Solution Specialist

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

I am glad it worked for you! 😊

Highlighted
Frequent Visitor

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

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

Highlighted
Solution Specialist
Solution Specialist

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

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

 

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021