0
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

Power Queryでスマートに連番を振るカスタム関数

Last updated at Posted at 2023-03-11

Power Queryでスマートに連番を振るカスタム関数

ほとんどの Power Query エンジニアは、グループ毎に連番を振るときに必須となる Table.Group() 関数の仕様が入れ子テーブルを含めてテーブル全体の順序を保証しない事を知りません。
少なくともこの記事を投稿している時点では、このカスタム関数がグループ毎に正確な連番を振ることが出来る唯一の方法だと思います。

https://github.com/takeyamajp/PowerQuery-IndexRowsForEachGroup
https://github.com/takeyamajp/PowerQuery-IndexRowsForEachGroup/blob/main/README.ja.md (日本語説明)

このライブラリーでは、正確に思った通りの並び順で連番を振ることが出来ます。
さらに、このライブラリーは列の型情報を完全に保持します。

カスタム関数の説明

  • IndexRowsForEachGroup(table as table, key as anynonnull, comparisonCriteria as anynonnull, optional newColumnName as nullable text) as table
    グループ毎に、指定した並び順で連番を振ります。

  • CountRowsForEachGroup(table as table, key as anynonnull, optional newColumnName as nullable text) as table
    グループ毎の行数をカウントします。

  • IndexGroups(table as table, key as anynonnull, comparisonCriteria as anynonnull, optional newColumnName as nullable text) as table
    指定した並び順でグループに連番を振ります。

  • CountGroups(table as table, key as anynonnull, optional newColumnName as nullable text) as table
    グループ数をカウントします。

  • IndexRows(table as table, comparisonCriteria as anynonnull, optional newColumnName as nullable text) as table
    各行に連番を振ります。
    (標準関数のTable.Sort()とTable.AddIndexColumn()を組み合わせただけのオマケ関数です。)

  • CountRows(table as table, optional newColumnName as nullable text) as table
    行数をカウントします。
    (標準関数のTable.RowCount()とTable.AddColumn()を組み合わせただけのオマケ関数です。)

コードサンプル

let
    Source = #table(type table[Region = text, Country = text], {
        {"Asia", "Australia"}, 
        {"Asia", "China"}, 
        {"Asia", "India"}, 
        {"Asia", "Japan"}, 
        {"Europe", "France"}, 
        {"Europe", "Germany"}, 
        {"Europe", "United Kingdom"}, 
        {"North America", "Canada"}, 
        {"North America", "United States"}
    }),
    Custom1 = IndexRowsForEachGroup(Source, "Region", {"Region", "Country"}, "INDEX1"),
    Custom2 = CountRowsForEachGroup(Custom1, "Region", "COUNT1"),
    Custom3 = IndexGroups(Custom2, "Region", {"Region", "Country"}, "INDEX2"),
    Custom4 = CountGroups(Custom3, "Region", "COUNT2"),
    Custom5 = IndexRows(Custom4, {"Region", "Country"}, "INDEX3"),
    Custom6 = CountRows(Custom5, "COUNT3")
in
    Custom6

image.png

0
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?