Power Query のネストしがちな if-then-else を整理するには

複雑になりがちなネストされた if-then-else は、データ化した決定表(デシジョンテーブル)を用いたロジックで構成するとメンテナンスや更新に手間がかからず非常によい。Excel や Power BI Desktop の クエリ エディターでは設定できない操作は 詳細エディタで Power Query を記述することにはなるけれども、さほど難度が高いものではないのでぜひともお試しいただきたい。

# if-then-else
```text:構文
if <logicalTest> then <trueResult> else <falseResult> 
```

```text:例
let
    x = Number.Round( Number.Random() * 10, 0)
in
    if
        Number.IsOdd(x)
    then
        "奇数"
    else
        "偶数"
```

特殊な点はない。そして elseif はない。

# よくある if‐then-else の ネスト
例えば、運賃マトリクスがあり製品と配送先により運賃を定めるとき

||運賃|
|:--|--:|
|A地区|500|
|B地区|1000|
|その他|1200|

運賃を配送先ごとにルックアップしたい。

|配送ID|配送先|運賃|
|:--:|:--|--:|
|1|A地区|?|
|2|B地区|?|
|3|C地区|?|
|4|D地区|?|

```text:例
let
    配送Table = #table(
        type table [配送ID = Int64.Type, 配送先 = Text.Type],
        {
            {1, "A地区"}, {2, "B地区"}, {3, "C地区"}, {4, "D地区"}
        }
    ),
    運賃列の追加 = Table.AddColumn(
        配送Table,
        "運賃",
        each if [配送先] = "A地区"
             then 500
             else if [配送先] = "B地区" 
                  then 1000
                  else 1200
        , Currency.Type
    )
in
    運賃列の追加
```
これくらいのネストであればよいのだけど、もっと要素が多くなった時どうするとよいのか考えた。

|地区\商品| 商品A|商品B|商品C|
|:--|--:|--:|--:|
|A地区|500|600|200|
|B地区|1000|800|500|
|C地区|1200|1000|800|

|地区\商品|その他の商品|
|:--|--:|
|その他の地区|1500 一律|

# if-then-else を使わない
いくらかの工夫をすれば if-then-else のネストが少なくなることが考えられるが、必要に迫られた更新が結構大変。なので、運賃テーブルからのルックアップに if-then-else を使わない方法を用いる。

## 決定表をテーブルにする

```text:運賃テーブル
let
    運賃マトリクス = #table(
        type table [
            地区 = Text.Type
           ,商品A = Currency.Type
           ,商品B = Currency.Type
           ,商品C = Currency.Type
        ],
        {
            {"A地区",  500,  600,  200}
           ,{"B地区", 1000,  800,  500}
           ,{"C地区", 1200, 1000,  800}
        }
    ),

    // ピボット解除
    運賃テーブル = Table.UnpivotOtherColumns(
        運賃マトリクス,
        {"地区"},
        "商品", "運賃"
    ) 
in
    運賃テーブル
```

運賃テーブルができた。

|地区|商品|運賃|
|:--|:--|--:|
|A地区|商品A|500|
|A地区|商品B|600|
|A地区|商品C|200|
|B地区|商品A|1000|
|B地区|商品B|800|
|B地区|商品C|500|
|C地区|商品A|1200|
|C地区|商品B|1000|
|C地区|商品C|800|

## 条件に合致する値を参照する手段
### アイテムアクセス
Power Query の テーブルもしくはリストのアイテム(テーブルならレコード)を参照するには、"{ }"(波括弧)で修飾する。

```text
// index number(0-base)で
運賃テーブル{0} //運賃テーブルの1行め 
運賃テーブル{3} //運賃テーブルの4行め

// フィールドとその値で
運賃テーブル{[地区 = "A地区", 商品 = "商品B"]} //A地区 商品B の行
運賃テーブル{[地区 = "C地区", 商品 = "商品C"]} //C地区 商品C の行
```

行を特定できない値や条件を与えた場合、Expression.Error が返される。

- index number が負の値
- index number が行数 - 1 を超える値
- 各フィールドに存在しない、値 もしくは 値の組み合わせを指定

### フィールドルックアップ
テーブルからルックアップされたアイテムはレコード(record)になるので、"[ ]"(角括弧)でフィールドを指定してルックアップする。

```text
//運賃テーブルの1行め の運賃
運賃テーブル{0}[運賃]

//C地区 商品C の運賃
運賃テーブル{[地区 = "C地区", 商品 = "商品C"]}[運賃] 
```

### カスタム列の追加
前項 のフィールドルックアップを踏まえ、ルックアップした運賃をカスタム列としてテーブルに追加する。

|配送ID|配送先|配送商品|配送運賃|
|:--:|:--|:--|--:|
|1|A地区|商品C|?|
|2|B地区|商品A|?|
|3|C地区|商品B|?|
|4|X地区|商品B|?|
|5|A地区|商品Z|?|

```text:配送テーブル
let
    配送テーブル = #table(
        type table [
            配送ID = Int64.Type
           ,配送先 = Text.Type
           ,配送商品 = Text.Type
        ],
        {
            {1, "A地区", "商品C"}
           ,{2, "B地区", "商品A"}
           ,{3, "C地区", "商品B"}
        }
    )
in
    配送テーブル
```

配送テーブルに運賃列を追加する。

```text:配送テーブル(運賃追加)
let
    追加されたカスタム = Table.AddColumn(
        配送テーブル, 
        "運賃",
        each
            運賃テーブル{
                [地区 = _[配送先], 商品 = _[配送商品]]
            }[運賃]
       ,Currency.Type
    )
in
    追加されたカスタム
```

each キーワードとともに使用している "_"(アンダースコア)はカレント行を示していて、テーブルの行なのでレコード型の値となる。アンダースコアによる明示した記述はここでは省略可能。

|配送ID|配送先|配送商品|配送運賃|
|:--:|:--|:--|--:|
|1|A地区|商品C|200|
|2|B地区|商品A|1000|
|3|C地区|商品B|1000|
|4|X地区|商品B|Error|
|5|A地区|商品Z|Error|

### 例外処理
決定表の例外に該当する行を追加してもよいが、運賃テーブルで運賃を指し示せない地区や商品についての例外処理を検討する。

ここでは try otherwise を利用

```text:構文
try <tryExpression> otherwise <alternateExpression>

tryExpression : 評価する式
alternateExpression : tryExpression の結果がエラーとなるときに評価される式
```

```text:配送テーブル(例外運賃追加)
let
    範囲外運賃 = 1500,
    運賃列の追加 = Table.AddColumn(
        配送テーブル, 
        "運賃",
        each
            try
                運賃テーブル{
                    [地区 = _[配送先], 商品 = _[配送商品]]
                }[運賃]
            otherwise
                範囲外運賃
       ,Currency.Type
    )
in
    運賃列の追加
```

|配送ID|配送先|配送商品|配送運賃|
|:--:|:--|:--|--:|
|1|A地区|商品C|200|
|2|B地区|商品A|1000|
|3|C地区|商品B|1000|
|4|X地区|商品B|1500|
|5|A地区|商品Z|1500|

# しきい値が使用される決定表の場合

|数量\商品| 商品A|商品B|商品C|
|:--:|--:|--:|--:|
|10 まで|500|600|-|
|25 まで|-|700|-|
|30 まで|-|-|200|
|50 まで|1000|-|-|
|51 以降|1200|-|-|
|100 まで|-|800|500|
|101 以降|-|1000|800|

と、いうような しきい を利用する判断で なにか が決定される場合。
まず、上限値を超える場合は一律の結果になるのでまとめる。

|数量しきい値| 商品A|商品B|商品C|
|:--:|--:|--:|--:|
|10|500|600|-|
|25|-|700|-|
|30|-|-|200|
|50|1000|-|-|
|100|-|800|500|
|∞|1200|1000|800|


```text:なにかのテーブル
let
    なにかのマトリクス = #table(
        {"数量しきい値", "商品A","商品B","商品C"},
        {
            {10, 500, 600, "-"}
           ,{25, "-", 700, "-"}
           ,{30, "-", "-", 200}
           ,{50, 1000, "-", "-"}
           ,{100, "-", 800, 500}
           ,{Number.PositiveInfinity, 1200, 1000, 800} 
        }
    ),
    置き換えられた値 = Table.ReplaceValue(
        なにかのマトリクス,
        "-",null,Replacer.ReplaceValue,
        {"商品A", "商品B", "商品C"}
    ),
    並べ替えられた行 = Table.Sort(
        置き換えられた値,
        {
            {"数量しきい値", Order.Ascending}
        }
    ),
    上方向へコピー済み = Table.FillUp(
        並べ替えられた行,
        {"商品A", "商品B", "商品C"}
    ),
    下方向へコピー済み = Table.FillDown(
        上方向へコピー済み,
        {"商品A", "商品B", "商品C"}
    ),
    ピボット解除された他の列 = Table.UnpivotOtherColumns(
        下方向へコピー済み,
        {"数量しきい値"},
        "商品", "なにかの値"
    )
in
    ピボット解除された他の列
```

行数がさほど発生しないので、ルックアップしやすいことを優先。しきい値を網羅させた。決定条件が折りたためるようになるかもしれないので。整理にもちょうどよいかな。

|数量しきい値|商品|なにかの値|
|:--:|:--|--:|
|10|商品A|500|
|10|商品B|600|
|10|商品C|200|
|25|商品A|1000|
|25|商品B|700|
|25|商品C|200|
|30|商品A|1000|
|30|商品B|800|
|30|商品C|200|
|50|商品A|1000|
|50|商品B|800|
|50|商品C|500|
|100|商品A|1200|
|100|商品B|800|
|100|商品C|500|
|Infinity|商品A|1200|
|Infinity|商品B|1000|
|Infinity|商品C|800|


このテーブルから なにかの値 をルックアップする。ポイントは対応するしきい値をどのように求めるか。方針としては、しきい値のリストうち、検査対象の"数量"以上で かつ そのなかで最小としてみる。

|商品|数量|なにかの値|
|:--|--:|--:|
|商品A|150|?|
|商品B|101|?|
|商品C|29|?|
|商品A|51|?|


```text:なにかの値が必要な表(なにかの値列追加)
let
    なにかの値が必要な表 = #table(
        type table [商品 = text, 数量 = number],
        {
            {"商品A", 150}
           ,{"商品B", 101}
           ,{"商品C", 29}
           ,{"商品A", 50}
        }
    ),
    追加されたカスタム = Table.AddColumn(
        なにかの値が必要な表,
        "なにかの値",
        each なにかのテーブル{[
                商品 = _[商品],
                数量しきい値 = 
                    let
                        value = _[数量]
                    in
                        List.Min(  // 最小を抽出
                            List.Select(  // 対象のリストのうち
                                 なにかのテーブル[数量しきい値]
                                ,each _ >= value  // 数量以上を選択
                            )
                        )
            ]}[なにかの値]
            , type number
    )
in
    追加されたカスタム
```


# ポイント
実際の要件にはもっと適した方法があるはずなのでいろいろ試してみるとよいと思う。if-then-else を使わなくてもなんとかなる。

- マトリクスはピボット解除し、条件の組合せを網羅する
- テーブルだけではなくリストを扱うことも有効

# その他

- [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)