Help us understand the problem. What is going on with this article?

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

More than 3 years have passed since last update.

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

if-then-else

構文
if <logicalTest> then <trueResult> else <falseResult> 
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地区 ?
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 を使わない方法を用いる。

決定表をテーブルにする

運賃テーブル
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 の テーブルもしくはリストのアイテム(テーブルならレコード)を参照するには、"{ }"(波括弧)で修飾する。

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

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

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

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

フィールドルックアップ

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

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

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

カスタム列の追加

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

配送ID 配送先 配送商品 配送運賃
1 A地区 商品C ?
2 B地区 商品A ?
3 C地区 商品B ?
4 X地区 商品B ?
5 A地区 商品Z ?
配送テーブル
let
    配送テーブル = #table(
        type table [
            配送ID = Int64.Type
           ,配送先 = Text.Type
           ,配送商品 = Text.Type
        ],
        {
            {1, "A地区", "商品C"}
           ,{2, "B地区", "商品A"}
           ,{3, "C地区", "商品B"}
        }
    )
in
    配送テーブル

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

配送テーブル(運賃追加)
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 を利用

構文
try <tryExpression> otherwise <alternateExpression>

tryExpression : 評価する式
alternateExpression : tryExpression の結果がエラーとなるときに評価される式
配送テーブル(例外運賃追加)
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
なにかのテーブル
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 ?
なにかの値が必要な表(なにかの値列追加)
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 を使わなくてもなんとかなる。

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

その他

PowerBIxyz
#PowerBI とか #PowerQuery とか Microsoft MVP for Data Platform (2016,2017-2018,2018-2019,2019-2020,2020-2021), Access (2011-2015)
https://powerbi.connpass.com/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away