Results 1 to 5 of 5

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

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Conjurer Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    138
    Articles
    0
    Excel Version
    Office 365

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

    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.

    Click image for larger version. 

Name:	DynamicGroup.PNG 
Views:	21 
Size:	58.7 KB 
ID:	10671

    Here's the function:
    Code:
    (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).
    Attached Files Attached Files
    Last edited by Nick Burns; 2021-10-22 at 06:51 AM.
    Oh... by the way, YOU'RE WELCOME!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •