Edited at

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

More than 1 year has 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 を使わなくてもなんとかなる。


  • マトリクスはピボット解除し、条件の組合せを網羅する

  • テーブルだけではなくリストを扱うことも有効


その他