複雑になりがちなネストされた 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 を使わなくてもなんとかなる。
- マトリクスはピボット解除し、条件の組合せを網羅する
- テーブルだけではなくリストを扱うことも有効