Table.AggregateTableColumn()

Table.AggregateTableColumn()可以抽象地概括为function(table as table, column as text, aggregations as list) as table,意思为该函数的第一个参数为表格,第二个参数需要通过文本指定第一参数中数据类型为表格的列,第三个参数通过串列的形式指定如何为第二个参数指定的表格列进行聚合运算。需要这个函数登场的情况比较少,一般应用于一对多关系的一边(one side)的聚合运算。

笔者刚接触Power Query时,需要对一个表格(DB)进行加工,详情如下:

该表格共有三列,第一列为产品的名称,第二列为产品在某次交易中的出售数量,第三列为交易日期。

以上表格需要以产品名称为依据进行分组并在每一组的首行插入空行,分组后需要按交易日期和出售数量进行组内的升序排列。最后,表格需要按照总共的交易次数升序排列。加工后的效果大致如下:

要完成以上所要求的加工,其中一种办法就是为上图的每一行计算出对应的行号,然后再以行号为依据进行升序排列。所有的空行的行号与其他行的空行的行号需要分开来单独进行计算,因为计算公式有所不同。对于任意空行,对应的行号等于累计的空行行数(不包括当前空行)+累计的其他行行数+1。对于任意其他行,对应的行号等于累计的空行行数+累计的非当前组的其他行行数+对应的组内次序(按日期与交易数量升序排序后)。按照这种思路,首先需要构造一个辅助表(InterTable):

辅助表需要满足与原表相同的列的内容为null,行数与产品的种类数相同,并且拥有一枚举了所有种类产品的列。构造好辅助表后,需要以下一系列的操作,方能得到空行正确的行数:

let
    
    InnerJoin=
        Table.NestedJoin(
            InterTable,
            { "Code" },
            DB,
            { "Product" },
            "NTable",
            JoinKind.Inner
        ),

    Aggregation=
        Table.AggregateTableColumn(
            InnerJoin,
            "NTable",
            { { "Product", List.Count, "RowCount"} }
        ),
        
    Sorting = Table.Sort( Aggregation, "RowCount" ),

    AddIndex1 = Table.AddIndexColumn( Sorting, "Indexing", 0 ),

    Buffering = List.Buffer( AddIndex1[RowCount] ),

    AddCol2=
        Table.AddColumn(
            AddIndex1,
            "CSum",
            each
                List.Sum(
                    List.Range(
                        Buffering,
                        0,
                        [Indexing]
                    )
                ),
            type number
        ),
        
    Sum=
        Table.AddColumn(
            AddCol2,
            "RowNo",
            each List.Sum( { [Indexing], [CSum] } ),
            type number
        ),

    RemoveCols=
        Table.SelectColumns(
            Sum,
            {
                "Product",
                "Quantity",
                "Date",
                "RowNo"
            }
        )
in
    RemoveCols

InnerJoin步骤通过Table.NestedJoin()完成了辅助表(左表)与原表(右表)的内连接,得到名为NTable的表格列。之后的Aggregation步骤,使用了Table.AggregateTableColumn()获得每一种产品的交易次数。由于要求分组后的表格按照总共的交易次数升序排列,之后需要按交易次数进行一次升序排序。AddIndex1通过添加从零开始的指数列得到累计的空行行数,然后使用List.Range()完成通过Table.AggregateTableColumn()得到的交易次数的累加,累加的结果与累计的空行行数相加就得到正确的行号。

要计算非空行的行号,需要以下代码:

let
    Source=
        Table.Group(
            DB,
            "Product",
            {
                {
                    "GTable",
                    each
                        Table.AddIndexColumn(
                            Table.Sort(
                                _,
                                { "Date", "Quantity" }
                            ),
                            "Indexing",
                            1
                        )
                },
                { "RowCount", each Table.RowCount( _ ) }
            }
        ),

    Sorting = Table.Sort( Source, "RowCount" ),
    
    AddIndex = Table.AddIndexColumn( Sorting, "Index", 0, 1 ),

    Buffering = List.Buffer( AddIndex[RowCount] ),

    CSum=
        Table.AddColumn(
            AddIndex,
            "CRowCount",
            each
                List.Sum(
                    List.Range(
                        Buffering,
                        0,
                        [Index]
                    )
                ),
                type number
        ),

    Sum=
        Table.AddColumn(
            CSum,
            "Addition",
            each
                List.Sum(
                    {
                        [Index],
                        [CRowCount]
                    }
                ),
            type number
        ),

    Expansion=
        Table.ExpandTableColumn(
            Sum,
            "GTable",
            {"Quantity", "Date", "Indexing"},
            {"Quantity", "Date", "Indexing"}
        ),

    RowNo=
        Table.AddColumn(
            Expansion,
            "RowNo",
            each
                List.Sum(
                    {
                        [Indexing],
                        [Addition]
                    }
                ),
            type number
        ),

    RemoveCols=
        Table.SelectColumns(
            RowNo,
            {
                "Product",
                "Quantity",
                "Date",
                "RowNo"
            }
        )
in
    RemoveCols

在Source步骤,Table.Group()完成了分组与组内排序的要求,并为每一组添加了指数列用于之后的行号计算。由于要求按总交易次数升序排序,Sorting步骤按上一步骤计算所得的行数进行了升序排序。排序之后,通过Table.AddIndexColumn()得到累计的空行行数。之后,通过List.Range()计算好累计的非当前组的非空行行数之和。至此,非空行的行号所需要的三个部分都完成了计算,之后加总这三部分就得到了需要的行号。

完成了行号的计算后,只要把完成行号计算的辅助表与原表进行纵向合并,再按行号进行升序排序就完成了原表的加工,对应的代码为:

let
    Source = Table.Combine( { NullRows, DB_RowNo } ),

    Sorting = Table.Sort( Source,"RowNo"),

    RemoveCol = Table.RemoveColumns( Sorting, {"RowNo"} )
in
    RemoveCol

如果不使用计算行数的方法,绕开Table.AggregateColumnTable(),可以大幅度化简以上代码:

let
    Grouping=
        Table.Group(
            DB,
            "Product",
            {
                "GTable",
                each
                    Table.Combine(
                        {
                            #table(
                                Table.ColumnNames( DB ),
                                {
                                    List.Repeat(
                                        { null },
                                        Table.ColumnCount( DB )
                                    )
                                }
                            ),
                            Table.Sort( _, { "Date", "Quantity" } )
                        }
                    )
            }
        ),

    UnionAll=
        Table.Combine(
            List.Sort(
                Grouping[GTable],
                each Table.RowCount( _ )
            ),
            type table [ Product = text, Quantity = Int64.Type, Date = date ]
        )
in
    UnionAll

以上代码的思路更为直接,每一组首行插入空行的步骤是在完成分组与组内排序之后进行的,并利用了List.Sort可以对以第一参数的元素为自变量的函数结果进行排序的特点完成了需要按照总共的交易次数进行升序排序的要求。

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注