Power Query workout - Table.SelectRows

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

https://docs.microsoft.com/en-us/powerquery-m/table-selectrows

```text:Power&#x20;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つ

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

# すべての行で評価を試みる
テーブルから条件に合う行を選択するのだから当然のこと。
```text:Power&#x20;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 回評価する。
ここまでは想像するだけでも理解できるはず。しかし、ソーステーブルの行数分の評価ができないことがあるから、**すべての行で評価を試みる**なのだ。

```text:Power&#x20;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 が顕著で効果的に機能するので例として挙げる。

https://docs.microsoft.com/en-us/powerquery-m/table-firstn

`condition`ではなく`countOrCondition`ということがポイント
```text:Power&#x20;Query
let
    Source = Table.FromColumns(
        {{"One", "Two", "Three", "Four", "Five"}}
    ),
    SelectedFirstRows = Table.FirstN(
        Source, 2
    )
in
    SelectedFirstRows
```
このクエリはテーブルから評価を必要とせず先頭 2 行を選択する。3 行目以降について処理は発生しない。ソーステーブルの行数がどれだけ多くなっても評価パフォーマンスが変わらないのである。
```text:Power&#x20;Query
let
    Source = Table.FromColumns(
        {{"One", "Two", "Three", "Four", "Five"}}
    ),
    SelectedFirstRows = Table.FirstN(
        Source,
        each [Column1] <> "Three"
    )
in
    SelectedFirstRows
```
先頭からの行数ではなく行を選択する条件を使うこともできる。この場合、先頭行から`[Column1] <> "Three"`を評価するので、4 行目以降評価することがない。

https://docs.microsoft.com/en-us/powerquery-m/table-skip

https://docs.microsoft.com/en-us/powerquery-m/table-removefirstn

https://docs.microsoft.com/en-us/powerquery-m/table-alternaterows

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

# クエリフォールディング
クエリフォールディングが利用できるデータソースに対する Table.SelectRows による行の選択は、データソースにその処理をプッシュする。例えば SQL Server の場合、T-SQL に変換される。
```text:Power&#x20;Query
let
    Items = {"飲料", "調味料"},
    Source = Sql.Database(
        ServerName, DatabaseName
    ),
    dbo_商品区分 = Source{[Schema="dbo",Item="商品区分"]}[Data],
    FilteredRows = Table.SelectRows(
        dbo_商品区分,
        each List.Contains(Items, [区分名])
    )
in
    FilteredRows
```

```sql
select [_].[区分ID],
    [_].[区分名],
    [_].[説明]
from [dbo].[商品区分] as [_]
where [_].[区分名] in (N'飲料', N'調味料')
```
これらリモート処理を仕掛けるしくみは Power Queryの特徴。なので充分に理解しておかなければならない。利用するデータソースによっては差異があるものの、Table.SelectRows についてはおおむね同様の動作をする。

とてもよくないのはこれら特徴的な動作を理解していないことだ。ネイティブクエリを文字列結合で構成したため SQL インジェクションが成立する可能性がある。うっかりじゃすまないかもしれない。気を付けてね。
```text:Power&#x20;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
```
```sql
select * from dbo.商品区分 as t where t.区分名 IN (N'飲料',N'調味料',N'') OR ('' = '');
```

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

```:Power&#x20;Query
let
    Source = Sql.Database(
        ServerName, DatabaseName
    ),
    dbo_商品区分 = Source{[Schema="dbo",Item="商品区分"]}[Data],
    SelectedFirstRows = Table.FirstN(dbo_商品区分, 2)
in
    SelectedFirstRows
```

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

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

```:Power&#x20;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|

https://docs.microsoft.com/en-us/power-query/best-practices#filter-early

なので、前項クエリフォールディング含め、できるだけ早い時期に行の選択を済ませておくことがベストプラクティス。
# 思ったこと🙄
どのように行を選択するかでどれだけ勉強したかがすぐわかるかも知れない。それくらい重要。
# その他
https://powerbi.connpass.com/

- [Power BI 勉強会 - connpass](https://powerbi.connpass.com/)
- [Power BI Desktop を使い始めるには](http://qiita.com/PowerBIxyz/items/6c4735cb5b5712e70981)
- [Excel で Power Query を使い始めるには](http://qiita.com/PowerBIxyz/items/52e563037b855a099ca9)