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 acro...
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

     

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

 

  • melimob's avatar
    melimob
    Frequent Visitor
    6 years ago

    oh thank you thank you thank you!

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      I am glad it worked for you! ğŸ˜Š

      • melimob's avatar
        melimob
        Frequent Visitor
        6 years ago

        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