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

     

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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