Improvements for function: Sum only Number Columns, maintain column names

[Nick Burns]

I created a function to dynamically sum all Numeric columns AND maintain all the columns.

I also added the option to make any column the Count column.



Here's the function:

(tbl as table, GroupByColumnList as list, optional CountColumnName as nullable text) as table =>
let
    TableSchema = Table.SelectColumns(Table.Schema(tbl),{"Name","Kind"}),
    NumberColumns = Table.SelectColumns(Table.SelectRows(TableSchema, each ([Kind] = "number")),{"Name"})[Name],
    NonNumberColumns = List.Difference(Table.SelectColumns(Table.SelectRows(TableSchema, each ([Kind] <> "number")),{"Name"})[Name],GroupByColumnList & {CountColumnName}),
    SumColumns = List.Transform(NumberColumns, (ColName) => {ColName, (Partition)=> List.Sum(Table.Column(Partition,ColName)), type nullable number}),
    NonSumColumns = List.Transform(NonNumberColumns, each {_,  each null, type nullable text }),
    CountColumns = List.Transform({CountColumnName}, each {_,  each Table.RowCount(_), Int64.Type}),
    AllColumns = if CountColumnName = null then SumColumns & NonSumColumns else SumColumns & NonSumColumns & CountColumns,
    DynamicGroup = Table.Group(tbl, GroupByColumnList, AllColumns),
    ReorderColumns = Table.SelectColumns(DynamicGroup,Table.ColumnNames(tbl)),
    Result = if CountColumnName = null then ReorderColumns else Table.RenameColumns(ReorderColumns,{{CountColumnName, "Count"}})
in
    Result

I was just wondering if there are any improvements that could be made.


EDIT: (Sorry Column1 was inserted when I loaded the totals, that column shouldn't be there).

 

[Ken Puls]

Hi Nick,

Just had a quick look - not enough time to recommend any improvements - I was just curious to see what you had. I think I'd need to spend more time carefully examining all the list management you have in there to really get a grasp on whether it could be improved much.

One note... you could save one step as your "tbl" step just references the previous Changed Type step (just rename the Changed Type step and it should work the same.)

 

[Nick Burns]

Hi Nick,

Just had a quick look - not enough time to recommend any improvements - I was just curious to see what you had. I think I'd need to spend more time carefully examining all the list management you have in there to really get a grasp on whether it could be improved much.

One note... you could save one step as your "tbl" step just references the previous Changed Type step (just rename the Changed Type step and it should work the same.)

Ah, you must be talking about the FunctionSteps query in the file.
That was my "wire-framing" of the actual function (fnDynamicGroup) - hardcoding variables and such. fnDynamicGroup is not derived from FunctionSteps.

 

[Nick Burns]

I did another take on it, though I'm not sure if it's "better", but it is less steps and it maintains the original Column Types, unless the Count column is provided.

(tbl as table, GroupByColumnList as list, optional CountColumnName as nullable text) as table =>
let
    SelectedColumns = Table.SelectColumns(tbl,List.Difference(Table.ColumnNames(tbl),GroupByColumnList)),
    TableSchema = Table.SelectColumns(Table.Schema(SelectedColumns),{"Name","Kind"}),
    AddCustom = Table.AddColumn(TableSchema, "Custom", 
                                                      each {[Name], 
                                                      if [Name] = CountColumnName then each Table.RowCount(_) else if [Kind] = "number" then (Partition) => List.Sum(Table.Column(Partition,[Name])) else each null,  
                                                      if [Name] = CountColumnName then type nullable number else Type.TableColumn(Value.Type(tbl),[Name])}),
    DynamicGroup = Table.Group(tbl, GroupByColumnList, AddCustom[Custom]),
    ReorderColumns = Table.SelectColumns(DynamicGroup,Table.ColumnNames(tbl)),
    Result = if CountColumnName = null then ReorderColumns else Table.RenameColumns(ReorderColumns,{{CountColumnName, "Count"}})
in
    Result

 

[Nick Burns]

OK, here's my final stab at this.
This may be best I can do, it only deals with the Column List and the meta data of the columns. No interim tables.

(tbl as table, GroupByColumnList as list, optional CountColumnName as nullable text) as table =>
let
    AggregateColumns = List.Transform(
          List.Difference(Table.ColumnNames(tbl),GroupByColumnList), 
              each {_, 
              if _ = CountColumnName then each Table.RowCount(_) else if Type.Is(Type.TableColumn(Value.Type(tbl),_),type nullable number) then (T) => List.Sum(Table.Column(T,_)) else each null,
              if _ = CountColumnName then type nullable number else Type.TableColumn(Value.Type(tbl),_)}),
    DynamicGroup = Table.Group(tbl, GroupByColumnList, AggregateColumns),
    ReorderColumns = Table.SelectColumns(DynamicGroup,Table.ColumnNames(tbl)),
    Result = if CountColumnName = null then ReorderColumns else Table.RenameColumns(ReorderColumns,{{CountColumnName, "Count"}})
in
    Result