Power Query workout - Table.SelectRows

行の選択は超基本で超重要。Table.SelectRows で超簡単だからといって疎かにしては超いけない。

Power Query

Table.SelectRows(table as table, condition as function) as table

Returns a table of rows from the table, that matches the selection condition.
選択 condition と一致する行のテーブルを table から返します。

理解すべきポイントは 3つ

• すべての行で評価を試みる
• クエリフォールディング
• ストリーミング

すべての行で評価を試みる

テーブルから条件に合う行を選択するのだから当然のこと。

Power Query

let
    Source = Table.FromColumns(
        {{1, 2, 3, 4, 5}}
    ),
    SelectedRows = Table.SelectRows(
        Source, each [Column1] > 0
    )
in
    SelectedRows

であるとき、conditionの引数である each [Column1] > 0 の式はソーステーブルの行数分 5 回評価する。conditionの引数が each [Column1] = 0 のとき、出力されるテーブルは 0 行のテーブルになるけれども、同じくソーステーブルの行数分 5 回評価する。
ここまでは想像するだけでも理解できるはず。しかし、ソーステーブルの行数分の評価ができないことがあるから、すべての行で評価を試みるなのだ。

Power Query

let
    Source = Table.FromColumns(
        {{1, 2, "3", 4, 5}}
    ),
    SelectedRows = Table.SelectRows(
        Source, each [Column1] > 0
    )
in
    SelectedRows

このクエリの評価結果は 3 行のテーブル。1行目、2行目の評価は期待通り。ただし、3行目の値が文字列であるから、conditionの評価結果は Expression.Error になる。Table.SelectRows は先頭行から評価を繰り返したが、errorが出現した時点で処理は中断。
これに気づいてないままだと、本来必要な行が得られていない可能性すらある。非構造化データソースを利用するとき、列のデータタイプの推測は既定の動作になっている理由のひとつだと思う。

評価を必要としない行の選択を検討

Table.SelectRows では任意の列の値を利用し行ごとで評価した結果から行を選択する。これはこれで必要。だが、値を参照や評価することなく行を選択することもできるのだ。要件を十分に満たすのであれば Table.SelectRows を使う必要はない。扱う行数が多ければクエリの評価パフォーマンスに大きく影響するし、値を評価を必要としない行の選択も必ず検討すべき。
Table.FirstN が顕著で効果的に機能するので例として挙げる。

conditionではなくcountOrConditionということがポイント

Power Query

let
    Source = Table.FromColumns(
        {{"One", "Two", "Three", "Four", "Five"}}
    ),
    SelectedFirstRows = Table.FirstN(
        Source, 2
    )
in
    SelectedFirstRows

このクエリはテーブルから評価を必要とせず先頭 2 行を選択する。3 行目以降について処理は発生しない。ソーステーブルの行数がどれだけ多くなっても評価パフォーマンスが変わらないのである。

Power Query

let
    Source = Table.FromColumns(
        {{"One", "Two", "Three", "Four", "Five"}}
    ),
    SelectedFirstRows = Table.FirstN(
        Source,
        each [Column1] <> "Three"
    )
in
    SelectedFirstRows

先頭からの行数ではなく行を選択する条件を使うこともできる。この場合、先頭行から[Column1] <> "Three"を評価するので、4 行目以降評価することがない。

など、テーブルの先頭行から処理される関数は、十分に要件を満たしつつパフォーマンスが向上する可能性が高いので見落としてはいけない。

クエリフォールディング

クエリフォールディングが利用できるデータソースに対する Table.SelectRows による行の選択は、データソースにその処理をプッシュする。例えば SQL Server の場合、T-SQL に変換される。

Power Query

let
    Items = {"飲料", "調味料"},
    Source = Sql.Database(
        ServerName, DatabaseName
    ),
    dbo_商品区分 = Source{[Schema="dbo",Item="商品区分"]}[Data],
    FilteredRows = Table.SelectRows(
        dbo_商品区分,
        each List.Contains(Items, [区分名])
    )
in
    FilteredRows

select [_].[区分ID],
    [_].[区分名],
    [_].[説明]
from [dbo].[商品区分] as [_]
where [_].[区分名] in (N'飲料', N'調味料')

これらリモート処理を仕掛けるしくみは Power Queryの特徴。なので充分に理解しておかなければならない。利用するデータソースによっては差異があるものの、Table.SelectRows についてはおおむね同様の動作をする。

とてもよくないのはこれら特徴的な動作を理解していないことだ。ネイティブクエリを文字列結合で構成したため SQL インジェクションが成立する可能性がある。うっかりじゃすまないかもしれない。気を付けてね。

Power Query

let
    // Items = {"飲料", "調味料"},
    Items = {"飲料", "調味料", "') OR ('' = '"},
    FormatItems = Text.Combine(
        List.Transform(
            Items,
            each Text.Format("N'#{0}'", {_})
        ),
        ","
    ),
    QueryString = Text.Format(
        "select * from dbo.商品区分 as t where t.区分名 IN (#{0});",
        {FormatItems}
    ),
    Source = Sql.Database(
        ServerName, DatabaseName,
        [Query = QueryString]
    )
in
    Source

select * from dbo.商品区分 as t where t.区分名 IN (N'飲料',N'調味料',N'') OR ('' = '');

なお、Table.FirstN の行数指定は TOP に変換される。

Power Query

let
    Source = Sql.Database(
        ServerName, DatabaseName
    ),
    dbo_商品区分 = Source{[Schema="dbo",Item="商品区分"]}[Data],
    SelectedFirstRows = Table.FirstN(dbo_商品区分, 2)
in
    SelectedFirstRows

select top 2
    [$Table].[区分ID] as [区分ID],
    [$Table].[区分名] as [区分名],
    [$Table].[説明] as [説明]
from [dbo].[商品区分] as [$Table]

ストリーミング

Power Queryのクエリ(テーブル)が評価されるとき、クエリに含まれる複数の処理は可能な限り行ごとで処理される。

Power Query

let
    Source = Table.FromColumns(
        {{1, 2, 3, 4, 5}}
    ),
    SelectedRows = Table.SelectRows(
        Source, each Number.IsOdd([Column1])
    ),
    MultipliedColumn = Table.TransformColumns(
        SelectedRows,
        {
            {"Column1", each _ * 10}
        }
    )
in
    MultipliedColumn

このクエリの評価結果は、

Column1
10
30
50

ただし、

Column1
1
3
5

というテーブルが出力されてからではなく、複数の変換が行ごとで処理されるということだ。
最初の行の評価から始まり

Column1	Number.IsOdd([Column1])
1	true

評価がtrueであれば、次の変換を処理

Column1	Number.IsOdd([Column1])	_ * 10
1	true	10

次の行で評価がfalseであれば、次の変換は処理しない

Column1	Number.IsOdd([Column1])	_ * 10
10
2	false

次の行で評価

Column1	Number.IsOdd([Column1])	_ * 10
10
3	true

評価がtrueであれば、次の変換を処理

Column1	Number.IsOdd([Column1])	_ * 10
10
3	true	30

次の行で評価がfalseであれば、次の変換は処理しない

Column1	Number.IsOdd([Column1])	_ * 10
10
30
4	false

次の行で評価

Column1	Number.IsOdd([Column1])	_ * 10
10
30
5	true

評価がtrueであれば、次の変換を処理

Column1	Number.IsOdd([Column1])	_ * 10
10
30
5	true	50

すべての行で変換は完了

Column1
10
30
50

なので、前項クエリフォールディング含め、できるだけ早い時期に行の選択を済ませておくことがベストプラクティス。

思ったこと🙄

どのように行を選択するかでどれだけ勉強したかがすぐわかるかも知れない。それくらい重要。

その他

• Power BI 勉強会 - connpass
• Power BI Desktop を使い始めるには
• Excel で Power Query を使い始めるには