Power Query workout - Table.Group

知っていれば知っているほど、できることが増える。ボタンポチポチにこだわるなら無理って話。

Syntax

Table.Group(
    table as table,
    key as any,
    aggregatedColumns as list,
    optional groupKind as nullable number,
    optional comparer as nullable function
) as table

• keyは text, {text} どちらも受け入れるので any
• aggregatedColumnsは Table.AddColumn 関数のパラメタ newColumnName, columnGenerator, columnTypeの list で {nullable list} も可
• groupKindは GroupKind.Global(既定値), GroupKind.Local のいずれか
• comparerはkey列の値を比較する関数

Example

Table.Group(
    SourceTable,
    {"区分名", "商品名"},
    {
        {"Count", each Table.RowCount(_), Int64.Type},
        {"Max", each List.Max([単価]), type nullable number}
    }
)

Example

let
    Source = Table.FromColumns(
        {
            {1,{1},type number,{1,2},1,{1,2},{1},type number}
        },
        type table [Column1 = any]
    ),
    GroupedRows = Table.Group(
        Source,
        {"Column1"},
        {"RowCount", each Table.RowCount(_), Int64.Type}
    )
in
    GroupedRows

動作を変更するパラメタ

groupKind

GroupKind.Global

• 既定値
• key列の値もしくはユニークな値の組み合わせで行をグループ化する(Buffering)
• 次の式は同じ結果になる

Table.Group(
    SourceTable,
    {"Column1","Column2"},
    {}
)

Table.Distinct(
    Table.SelectColumns(
        SourceTable,
        {"Column1","Column2"}
    ),
    {"Column1","Column2"}
)

GroupKind.Local

• 先頭行から順に出現するkey列の値もしくは値の組み合わせで行をグループ化する(Streaming)
• key列でソートが保証されているなら使うとよい

let
    Source = Table.FromColumns(
        {{1,3,3,3,2,2,1,1}},
        type table [Column1 = Int64.Type]
    ),
    GroupedTable = Table.Group(
        Source,
        {"Column1"},
        {"RowCount", each Table.RowCount(_), Int64.Type},
        GroupKind.Local
    )
in
    GroupedTable

Column1	RowCount
1	1
3	3
2	2
1	2

comparer

グループ化の基準となる行と現在の行を比較する関数(引数は2つ)

• 戻り値は整数のみ
• 0 : 一致する(基準となる行が維持される)
• 0 以外 : 一致しない(基準となる行が更新される)
• 参照できる列はkey列のみ

let
    Source = Table.FromColumns(
        {
            {1,3,4,3,2,2,3,1}
        },
        type table [Column1 = Int64.Type]
    ),
    GroupedRows = Table.Group(
        Source,
        {"Column1"},
        {
            "RowCount",
            each Table.RowCount(_),
            Int64.Type
        },
        GroupKind.Local,
        (ReferenceRow, CurrentRow) =>
            Number.From(
                CurrentRow[Column1] < ReferenceRow[Column1]
                or CurrentRow[Column1] - ReferenceRow[Column1] >= 3
            )
    )
in
    GroupedRows

Column1	RowCount
1	2
4	1
3	1
2	3
1	1

その他