Power Query workout - Table.StopFolding

結論から言うと、クエリがどのような動作をしているか検証することが大事。データソースにその処理のほとんどもしくは一部をプッシュしたつもりになっているだけってこともある。クエリ診断機能を使うとよいし、利用するデータソースのプロファイラも使用するとさらによい。

https://docs.microsoft.com/en-us/power-query/querydiagnostics

Table.StopFolding がよい仕事をするっていう話。

https://docs.microsoft.com/en-us/powerquery-m/table-stopfolding
```text:Power&#x20;Query
Table.StopFolding(table as table) as table
```
> Prevents any downstream operations from being run against the original source of the data in `table`.
`table`のデータの元のソースに対して下流の操作が実行されないようにします。

なるほど、動作はわかった。では、使いどころはどのようなときかな🤔と思っていて、ちょうどよいケースがあった。状況をシンプルに調整し再現した。データソースは Azure SQL database。

Power Query で記述しているクエリが評価されるとき、データソースに対しては変換されたネイティブクエリで問い合わせが行われる。当然、T-SQL と SQL Server コネクタでサポートされている変換のみだ。

```text:Power&#x20;Query
let
    Source = Sql.Database(ServerName, DatabaseName),
    Data_Customer = Source{[Schema="Data",Item="Customer"]}[Data],
    RemovedOtherColumns = Table.SelectColumns(
        Data_Customer,
        {"CustomerKey", "Gender", "Age"}
    ),
    AddedToColumn = Table.TransformColumns(
        RemovedOtherColumns,
        {"Age", each _ + 1, type number}
    )
in
    AddedToColumn
```
クエリフォールディングが機能して次のネイティブクエリ(T-SQL)になる。
```sql
select [_].[CustomerKey] as [CustomerKey],
    [_].[Gender] as [Gender],
    [_].[Age] + 1 as [Age]
from 
(
    select [CustomerKey],
        [Gender],
        [Age]
    from [Data].[Customer] as [$Table]
) as [_]
```
T-SQL に翻訳できない Text.Proper 関数を後続の処理に追加した時、
```text:Power&#x20;Query
let
    Source = Sql.Database(ServerName, DatabaseName),
    Data_Customer = Source{[Schema="Data",Item="Customer"]}[Data],
    RemovedOtherColumns = Table.SelectColumns(
        Data_Customer,
        {"CustomerKey", "Surname", "Age"}
    ),
    AddedToColumn = Table.TransformColumns(
        RemovedOtherColumns,
        {"Age", each _ + 1, type number}
    ),
    CapitalizedEachWord = Table.TransformColumns(
        AddedToColumn,
        {"Surname", Text.Proper, type text}
    )
in
    CapitalizedEachWord
```
想像していたネイティブクエリ(T-SQL)と異なることがある。
```sql
select [$Ordered].[CustomerKey],
    [$Ordered].[Surname],
    [$Ordered].[Age]
from 
(
    select [CustomerKey],
        [Surname],
        [Age]
    from [Data].[Customer] as [$Table]
) as [$Ordered]
order by [$Ordered].[CustomerKey]
```
ネイティブクエリ(T-SQL)に翻訳可能なステップ(このクエリでは`AddedToColumn`ステップ)が利用されないことがある。
そこで Table.StopFolding を用いて明示的に境界を指定する。
```text:Power&#x20;Query
let
    Source = Sql.Database(ServerName, DatabaseName),
    Data_Customer = Source{[Schema="Data",Item="Customer"]}[Data],
    RemovedOtherColumns = Table.SelectColumns(
        Data_Customer,
        {"CustomerKey", "Surname", "Age"}
    ),
    AddedToColumn = Table.TransformColumns(
        RemovedOtherColumns,
        {"Age", each _ + 1, type number}
    ),
    StopFolding = Table.StopFolding(AddedToColumn),    // 👈ココ
    CapitalizedEachWord = Table.TransformColumns(
        StopFolding,
        {"Surname", Text.Proper, type text}
    )
in
    CapitalizedEachWord
```
後続のステップ`CapitalizedEachWord`はクエリフォールディングが検討される範囲から外れる。
```sql
select [_].[CustomerKey] as [CustomerKey],
    [_].[Surname] as [Surname],
    [_].[Age] + 1 as [Age]
from 
(
    select [CustomerKey],
        [Surname],
        [Age]
    from [Data].[Customer] as [$Table]
) as [_]
```
想像通りのネイティブクエリになる。めでたし。
Table.StopFolding ではなく Table.Buffer でも想像通りのネイティブクエリになる。ただし、データをすべてローカルキャッシュとする作用があるので、クエリの評価パフォーマンスに大きく影響する。
```text:Power&#x20;Query
let
    Source = Sql.Database(ServerName, DatabaseName),
    Data_Customer = Source{[Schema="Data",Item="Customer"]}[Data],
    RemovedOtherColumns = Table.SelectColumns(
        Data_Customer,
        {"CustomerKey", "Surname", "Age"}
    ),
    AddedToColumn = Table.TransformColumns(
        RemovedOtherColumns,
        {"Age", each _ + 1, type number}
    ),
    Buffer = Table.Buffer(AddedToColumn),    // 👈ココ
    CapitalizedEachWord = Table.TransformColumns(
        Buffer,
        {"Surname", Text.Proper, type text}
    )
in
    CapitalizedEachWord
```
```sql
select [_].[CustomerKey] as [CustomerKey],
    [_].[Surname] as [Surname],
    [_].[Age] + 1 as [Age]
from 
(
    select [CustomerKey],
        [Surname],
        [Age]
    from [Data].[Customer] as [$Table]
) as [_]
```
# 思ったこと🙄
Power Query だけでデータモミモミするよりも、データソースに View を用意する/その検討が必要。それが叶わない/そこまでの必要がないなら、どのようにデータソースのリソースを消費しているかを常に確認。Table.Buffer と ネイティブクエリの記述は最終秘密兵器。秘密のままにして使わないことが望ましい。
# その他
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)