Power Query エディター の クエリ フォールディング機能とは

Power BI Desktop / Excel でのETL作業に使用する Power Query エディターにはクエリ フォールディング機能がある。この機能は SQL Server や Office Accessなどリレーショナルデータベースをデータソースにするとき、可能な限りデータベースに発行する **クエリを折りたたみ** ます。データの抽出や変換時のパフォーマンスを低下しにくくすると同時に分析に必要なデータのみ取得することができる。

# 使いどころ
機能自体は内部的に行われるから対応するための特別な手続きをとる必要はないが、期待する結果や効果を得るためには意識しておく必要はある。クエリ フォールディングが終了すると以降の [適用するステップ] は読み込み済みのデータに対する処理となる。なので、必要最小限のデータ量を変換加工するため、最初や序盤に適用されるべきもの。

データソースに専用のビューを用意できればよいのだけど、そうもいかないときや用意されるまでにないものであれば有用かと。

# 機能
クエリ フォールディング(Query Folding)はサポートされるコネクターを使用する [適用するステップ] で自動的に実施されるので事前の準備や設定は不要。たとえば、

|商品ID|商品名|区分ID|
|:-:|:--|:-:|
|1|果汁100% オレンジ|1|
|2|ブルーベリーヨーグルト|2|
|3|ラズベリーヨーグルト|2|
|4|清涼スカッシュ|1|
というテーブル：商品から抽出しようとしていて、

## 列の除外
フィールド：[区分ID]は分析から除外とするとき、[他の列の削除]こと、[Table.SelectColumns | Power Query M function reference](https://msdn.microsoft.com/en-us/library/mt260828.aspx) で列を選択する。

```text:抽出するM
let
    Source = Source,
    _商品 = Source{[Schema="dbo",Item="商品"]}[Data],
    RemovedOtherColumns = 
        Table.SelectColumns(
            _商品,
           {"商品ID", "商品名"}
    )
in
    RemovedOtherColumns
```
データソースに対して発行されるクエリは

```sql:発行されるクエリ
select [商品ID],
    [商品名]
from [商品] as [$Table]
```

## 行の除外
フィールド：[区分ID] = 1 の行(record)のみを抽出したいとき、

```text:抽出するM
let
    Source = Source,
    _商品 = Source{[Schema="dbo",Item="商品"]}[Data],
    FilteredRows = 
        Table.SelectRows(
            _商品,
            each ([区分ID] = 1
        )
    ),
    RemovedOtherColumns = 
        Table.SelectColumns(
            FilteredRows,
            {"商品ID", "商品名"}
        )
in
    RemovedOtherColumns
```
データソースに対して発行されるクエリは

```sql:発行されるクエリ
select [_].[商品ID],
    [_].[商品名]
from [商品] as [_]
where [_].[区分ID] = 1 and [_].[区分ID] is not null
```
## クエリのマージ

| 区分ID | 区分名 |
|:-:|:--|
|1|飲料|
|2|乳製品|
テーブル：商品にテーブル：商品区分をマージするとき、

```text:抽出するM
let
    Source = Source,
    _商品 = Source{[Schema="dbo",Item="商品"]}[Data],
    ExpandRecordColumn = 
        Table.ExpandRecordColumn(
            _商品, 
            "商品区分",
            {"区分名"}, {"区分名"}
        )
in
    ExpandRecordColumn
```
データソースに対して発行されるクエリは

```sql:発行されるクエリ
select [$Outer].[商品ID] as [商品ID],
    [$Outer].[商品名] as [商品名],
    [$Outer].[区分ID2] as [区分ID],
    [$Inner].[区分名] as [区分名]
from 
(
    select [_].[商品ID] as [商品ID],
        [_].[商品名] as [商品名],
        [_].[区分ID] as [区分ID2]
    from [商品] as [_]
) as [$Outer]
left outer join [商品区分] as [$Inner] on ([$Outer].[区分ID2] = [$Inner].[区分ID])
```

### コネクターのオプション 
クエリ フォールディングをサポートするコネクターのオプション CreateNavigationProperties = true(既定値：true) であれば、マージするクエリを読み込んでおかなくても結合が可能。

## その他の動作
### グループ化

|区分名|カウント|
|:-:|--:|
|飲料|2|
|乳製品|2|

```text:抽出するM
let
    Source = Source,
    _商品 = Source{[Schema="dbo",Item="商品"]}[Data],
    ExpandRecordColumn = 
        Table.ExpandRecordColumn(
            _商品, 
            "商品区分",
            {"区分名"}, {"区分名"}
        ),
    GroupedRows = 
        Table.Group(
            ExpandRecordColumn, 
            {"区分名"}, 
            {
                {"カウント", each Table.RowCount(_), type number}
            }
        )
in
    GroupedRows
```

```sql:発行されるクエリ
select [rows].[区分名] as [区分名],
    count(1) as [カウント]
from 
(
    select [$Inner].[区分名]
    from 
    (
        select [_].[商品ID] as [商品ID],
            [_].[商品名] as [商品名],
            [_].[区分ID] as [区分ID2]
        from [dbo].[商品] as [_]
    ) as [$Outer]
    left outer join [dbo].[商品区分] as [$Inner] on ([$Outer].[区分ID2] = [$Inner].[区分ID])
) as [rows]
group by [区分名]
```

### 列のピボット
グループ集計されていれば [列のピボット] も対応

|飲料|乳製品|
|:-:|:-:|
|2|2|

```text:抽出するM
let
    Source = Source,
    _商品 = Source{[Schema="dbo",Item="商品"]}[Data],
    ExpandRecordColumn = 
        Table.ExpandRecordColumn(
            _商品, 
            "商品区分",
            {"区分名"}, {"区分名"}
        ),
    GroupedRows = 
        Table.Group(
            ExpandRecordColumn, 
            {"区分名"}, 
            {
                {"カウント", each Table.RowCount(_), type number}
            }
        ),
    PivotedColumn = 
        Table.Pivot(
            GroupedRows, 
            List.Distinct(GroupedRows[区分名]), 
            "区分名", 
            "カウント", 
            List.Sum
        )
in
    PivotedColumn
```

```sql:発行されるクエリ
select [$Table].[飲料],
    [$Table].[乳製品]
from 
(
    select [rows].[区分名] as [区分名],
        count(1) as [カウント]
    from 
    (
        select [$Outer].[商品ID],
            [$Outer].[商品名],
            [$Outer].[区分ID2],
            [$Inner].[区分名]
        from 
        (
            select [_].[商品ID] as [商品ID],
                [_].[商品名] as [商品名],
                [_].[区分ID] as [区分ID2]
            from [dbo].[商品] as [_]
        ) as [$Outer]
        left outer join [dbo].[商品区分] as [$Inner] on ([$Outer].[区分ID2] = [$Inner].[区分ID])
    ) as [rows]
    group by [区分名]
) [$Pivot] pivot (sum([カウント]) for [区分名] in ([飲料], [乳製品])) as [$Table]
```

### 列のマージ
[Text.Combine | Power Query M function reference](https://msdn.microsoft.com/en-us/library/mt253358.aspx) や [Combiner functions | Power Query M function reference](https://msdn.microsoft.com/en-us/library/mt267543.aspx) はサポートされていないので、&演算子を使用。

|商品名|区分名|結合済み| 
|:--|:--|:--|
|果汁100% オレンジ|飲料|果汁100% オレンジ(飲料)|
|ブルーベリーヨーグルト|乳製品|ブルーベリーヨーグルト(乳製品)|
|ラズベリーヨーグルト|乳製品|ラズベリーヨーグルト(乳製品)|
|清涼スカッシュ|飲料|清涼スカッシュ(飲料)|

```text:抽出するM
let
    Source = Source,
    _商品 = Source{[Schema="dbo",Item="商品"]}[Data],
    ExpandRecordColumn = 
        Table.ExpandRecordColumn(
            _商品, 
            "商品区分",
            {"区分名"}, {"区分名"}
        ),
    InsertedMergedColumn = 
        Table.AddColumn(
            ExpandRecordColumn, 
            "結合済み", 
            each  [商品名] & "(" & [区分名] & ")", 
            type text
        ),
    RemovedOtherColumns = 
        Table.SelectColumns(
            InsertedMergedColumn,
            {"商品名", "区分名", "結合済み"}
        )
in
    RemovedOtherColumns
```

```sql:発行されるクエリ
select [_].[商品名] as [商品名],
    [_].[区分名] as [区分名],
    (([_].[商品名] + '(') + [_].[区分名]) + ')' as [結合済み]
from 
(
    select [$Outer].[商品名],
        [$Inner].[区分名]
    from 
    (
        select [_].[商品ID] as [商品ID],
            [_].[商品名] as [商品名],
            [_].[区分ID] as [区分ID2]
        from [dbo].[商品] as [_]
    ) as [$Outer]
    left outer join [dbo].[商品区分] as [$Inner] on ([$Outer].[区分ID2] = [$Inner].[区分ID])
) as [_]
```

## 得られる効果
クエリ フォールディングがサポートされるデータソースからデータを抽出するとき、すべてのレコード/フィールドを読み込んでから変換加工とすることはないので、データモデルにロードされるまでのパフォーマンス向上が期待できる。

### ネイティブ クエリの表示
どのようなクエリに変換されるのかは、[適用したステップ]のコンテキストメニューから。このメニューが使用できないとき、SQLへの変換は行われていない状態にある。
![ネイティブ クエリの表示](https://qiita-image-store.s3.amazonaws.com/0/193509/178c742b-f699-fe38-03be-b62acde95664.png)

### クエリ フォールディングが終了するとき
- データ型の変換やパースなど クエリ エディター独自のステップが適用された
- データソースが異なるクエリとマージされた
- Table.Buffer が適用された

※ コネクターの オプション：Queryを使用した場合はクエリ フォールディングは機能しない。
![Query オプション](https://qiita-image-store.s3.amazonaws.com/0/193509/a1b92eb5-d051-848b-ecde-7604e870c102.png)

# 情報
[Query and Data Modeling Languages | MSDN](https://msdn.microsoft.com/en-us/library/mt244090.aspx)

# その他
- [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)