パワークエリ１年生の苦悩シリーズ。
エクセル関数・VBA使いが、パワークエリをやってみて、文化の違いに苦戦しつつ七転八倒の末なんとか中の下レベルまではマスター。（したと思っている）
カスタム列でif式を組もうとして、関数やVBAでは複雑な入れ子のif式もサッサカできるのに、なんかパワークエリだと勝手が違う。
関数だと()を使うから始めと終わりが見分けやすい、VBAだとインデントで入れ子が見分けやすいし、end if で終わりを明示できる。
パワークエリってif式の終わりがわかりにくい。。。？
１．パワークエリの if 式（関数、VBAとの比較）
（１）関数、VBA、パワークエリでif式を書いてみる
もし、[性別] が男だったら1、それ以外だったら 2 の場合
関数だと
=IF([@性別]=”男”,1,2)
VBAだと
if Range(“A1”).Value = ”男” then
MsgBox “1”
else
MsgBox “2”
end if
みたいに書きますよね。
パワークエリだと（列の追加＞カスタム列でif式手入力の場合）
if [性別] = “男” then 1 else 2
です。
（２）関数、VBA、パワークエリでif式を書いてみる～elseで何もしたくない場合
もし、[性別] が男だったら1、それ以外だったら 何もしない の場合
関数だと
=IF([@性別]=”男”,1,””)
または =IF([@性別]=”男”,1) で、条件が合致しない場合は セルに false が出力される
VBAだと
if seibetu = ”男” then
MsgBox “1”
end if
パワークエリだと（カスタム列で手入力の場合）
if [性別] = “男” then 1 else null
です。
パワークエリだと、elseなしは許されないらしい。怒られる。
つまり、else句を入れることで、終わりを明示するのだな、と理解。
（３）関数、VBA、パワークエリでif式を書いてみる～3つ以上の分岐
[性別] が男だったら1、女だったら 2、それ以外だったら0 の場合
関数だと
=IF([@性別]=”男”,1,if([@性別]=”女”,2,0))
VBAだと
if Range(“A1”).Value = ”男” then
MsgBox “1”
else if seibetu = ”女” then
MsgBox “2”
else MsgBox “0”
end if
パワークエリだと（カスタム列で手入力の場合）
if [性別] = “男” then 1
else if [性別] = “女” then 2
else 0
です。
なーんか、パワークエリのif式って終わりがわかりにくいというかぼんやりしてるんだよなー。かっこで閉める、end if で締めるに慣れているから。。。
３．パワークエリでifを使用する方法
話は前後しますが、パワークエリでifで分岐したい場合、カスタム列でif式を書いていく、条件列でif式を組み立てる、と主に二通りの方法があります。
（１）条件列でif式を組み立てる
列の追加＞条件列 で指定します。
私は「カスタム列」が好きなので、ここで詳しくは説明はしません。
入れ子やand、orを使いたいときなどは「条件列」では無理です。（たぶん無理だと思う）
（２）カスタム列でif式を入力する
カスタム列ではif式に限らずいろいろな式を入力することができます。
列の追加＞カスタム列 で入力ボックスを開き、式を入力していきます。
４．エラー回避は try otherwise が便利
年齢が18以上で「成人」、18未満で「未成年」、それ以外は（不明やnull値）はnullとしたいとき。
以下のif式では年齢がnull値がエラーとなってしまいます。数値が来ると思ってたところに数値ではないnullが入ってくるとエラーになっちゃうんですかね。
で、その対応として、nullの場合はほにゃらら、と条件式を追加するより、try otherwiseでエラー回避したほうがスマート。
普通にif式を書いた場合（[年齢]のところがnullだとエラーになる。上の図）
if [年齢] >= 18 then "成人"
else if [年齢] < 18 then "未成年"
else nullnull対策でもう一つ 条件式 を追加した場合
if [年齢] = null then null
else if [年齢] >= 18 then "成人"
else "未成年"ltry otherwise でエラー回避する場合
try
if [年齢] >= 18 then "成人"
else "未成年"
otherwise
null
try やってみる式 otherwise エラーが返される場合に返す値
と書きます。
もちろん if [年齢] = null then null とやってもいいんです。でもなんか try とかやったほうがデキル人っぽいのであえて使いたい。
try catch というのもあるみたいだけどこちらはまだ使ったことがない。
５．ifの入れ子
ifを入れ子にする場合。これに苦労しちゃった。
（１）入れ子のif式
例題
[種別]列が「大型犬」の場合、[体重]が25KG以上でL、25KG未満でM。[種別]列が「大型犬」以外の場合、[体重]が５KG以上でL、5KG未満でM。
関数だと
=IF([@種別]=”大型犬”,IF([@体重]>=25,”L”,”M”),IF([@体重]>=5,”L”,”M”))
VBAだと
If Range("A2").Value = "大型犬" Then
If Range("B2").Value >= 25 Then
Range("C2").Value = "L"
Else
Range("C2").Value = "M"
End If
Else
If Range("B2").Value >= 5 Then
Range("C2").Value = "L"
Else
Range("C2").Value = "M"
End If
End Ifパワークエリだと（カスタム列を使った場合）
if [種別] = "大型犬" then
if [体重] >= 25 then "L"
else "M"
else
if [体重] >= 5 then "L"
else "M"またはANDを使って
if [種別] = "大型犬" and [体重] >= 25 then "L"
else if [種別] = "大型犬" then "M"
else if [種別] = "小型犬" and [体重] >= 5 then "L"
else "M"とか
（２）条件が３つ以上に分かれるif式
例題
[種別]列が「大型犬」の場合、[体重]が25KG以上でL、15KG以上25KG未満でM、15KG未満でS。
[種別]列が「中型犬」以外の場合、[体重]が10KG以上でL、7KG以上10KG未満でM、7KG未満でS。
[種別]列が「小型犬」以外の場合、[体重]が5KG以上でL、3KG以上5KG未満でM、3KG未満でS。
関数の場合
=IF([@種別]=”大型犬”,IF([@体重]>=25,”L”,IF([@体重]>=15,”M”,”S”)),IF([@種別]=”中型犬”,IF([@体重]>=10,”L”,IF([@体重]>=7,”M”,”S”)),IF([@体重]>=5,”L”,IF([@体重]>=3,”M”,”S”))))
やな感じですね。ですが、関数には「慣れ」があるので、入力にはさほど戸惑いません。
VBAの場合
If Range("A2").Value = "大型犬" Then
If Range("B2").Value >= 25 Then
Range("C2").Value = "L"
ElseIf Range("B2").Value >= 15 Then
Range("C2").Value = "M"
Else
Range("C2").Value = "S"
End If
ElseIf Range("A2").Value = "中型犬" Then
If Range("B2").Value >= 10 Then
Range("C2").Value = "L"
ElseIf Range("B2").Value >= 7 Then
Range("C2").Value = "M"
Else
Range("C2").Value = "S"
End If
Else
If Range("B2").Value >= 5 Then
Range("C2").Value = "L"
ElseIf Range("B2").Value >= 3 Then
Range("C2").Value = "M"
Else
Range("C2").Value = "S"
End If
End Ifながいけれども、わかりやすくはある。
パワークエリの場合（カスタム式）
if [種別] = "大型犬" then
if [体重] >= 30 then "L"
else if [体重] >= 15 then "M"
else "S"
else if [種別] = "中型犬" then
if [体重] >= 10 then "L"
else if [体重] >= 7 then "M"
else "S"
else
if [体重] >= 5 then "L"
else if [体重] >= 3 then "M"
else "S"どちらかというとVBAに近いですね。VBAと違うのは if を入れたら必ず対応する else 句を入れなければいけないこと、end if は入れないこと、ですね。
（３）もうちょっと複雑な if 式
上記と同じ条件だが、
[種別]および[体重]が空欄の場合は空欄。
[種別]が「規格外」の場合は「規格外」その他想定していない[種別]が出てきた場合は「対象外」とする。
関数の場合
=IF(OR([@種別]=””,[@体重]=””),””,IF([@種別]=”規格外”,”規格外”,IF([@種別]=”大型犬”,IF([@体重]>=30,”L”,IF([@体重]>=15,”M”,”S”)),IF([@種別]=”中型犬”,IF([@体重]>=10,”L”,IF([@体重]>=7,”M”,”S”)),IF([@種別]=”小型犬”,IF([@体重]>=5,”L”,IF([@体重]>=3,”M”,”S”)),”対象外”)))))
ふはははは
IFSを使った場合
=IFS(OR([@種別]=””,[@体重]=””),””,[@種別]=”規格外”,”規格外”,[@種別]=”大型犬”,IF([@体重]>=30,”L”,IF([@体重]>=15,”M”,”S”)),[@種別]=”中型犬”,IF([@体重]>=7,”L”,IF([@体重]>=5,”M”,”S”)),[@種別]=”小型犬”,IF([@体重]>=5,”L”,IF([@体重]>=3,”M”,”S”)),TRUE,”対象外”)
あんまり変わらないな。。。
VBAの場合
For i = 2 To 11
If Range("A" & i).Value = "" Or Range("B" & i).Value = "" Then
Range("C" & i).Value = ""
ElseIf Range("A" & i).Value = "規格外" Then
Range("C" & i).Value = "規格外"
ElseIf Range("A" & i).Value = "大型犬" Then
If Range("B" & i).Value >= 25 Then
Range("C" & i).Value = "L"
ElseIf Range("B" & i).Value >= 15 Then
Range("C" & i).Value = "M"
Else
Range("C" & i).Value = "S"
End If
ElseIf Range("A" & i).Value = "中型犬" Then
If Range("B" & i).Value >= 10 Then
Range("C" & i).Value = "L"
ElseIf Range("B" & i).Value >= 7 Then
Range("C" & i).Value = "M"
Else
Range("C" & i).Value = "S"
End If
ElseIf Range("A" & i).Value = "小型犬" Then
If Range("B" & i).Value >= 5 Then
Range("C" & i).Value = "L"
ElseIf Range("B" & i).Value >= 3 Then
Range("C" & i).Value = "M"
Else
Range("C" & i).Value = "S"
End If
Else
Range("C" & i).Value = "対象外"
End If
Next iあー。やだやだ。
パワークエリの場合（カスタム列を使用）
if [種別] = null or [体重] = null then null
else if [種別] = "規格外" then "規格外"
else if [種別] = "大型犬" then
if [体重] >= 30 then "L"
else if [体重] >= 15 then "M"
else "S"
else if [種別] = "中型犬" then
if [体重] >= 10 then "L"
else if [体重] >= 7 then "M"
else "S"
else if [種別] = "小型犬" then
if [体重] >= 5 then "L"
else if [体重] >= 3 then "M"
else "S"
else "対象外"（４）自作関数を使用して複雑さを回避する
サイズ算出を関数化することもできます。あまりにも複雑になる場合は、一部を関数化してすっきりさせるのもおすすめです。
①大型犬サイズ算出、中型犬サイズ算出、小型犬サイズ算出の3つの関数を作成した場合。
関数の引数には[体重]をとります。
※自作関数の作成方法は後述します。
カスタム列のif文（大型犬サイズ算出、中型犬サイズ算出、小型犬サイズ算出という自作関数を使用している）
if [種別] = null or [体重] = null then null
else if [種別] = "規格外" then "規格外"
else if [種別] = "大型犬" then
大型犬サイズ算出([体重])
else if [種別] = "中型犬" then
中型犬サイズ算出([体重])
else if [種別] = "小型犬" then
小型犬サイズ算出([体重])
else "対象外"自作関数
大型犬サイズ算出
(weight) =>
let
サイズ = if weight >= 30 then "L"
else if weight >= 15 then "M"
else "S"
in
サイズ中型犬サイズ算出
(weight) =>
let
サイズ = if weight >= 10 then "L"
else if weight >= 7 then "M"
else "S"
in
サイズ小型犬サイズ算出
(weight) =>
let
サイズ = if weight >= 5 then "L"
else if weight >= 3 then "M"
else "S"
in
サイズ②一つのサイズ算出の関数を作成した場合
[種別]と[体重]の二つの引数をとり、サイズを返します。
カスタム式（サイズ算出という関数を使用している）
if [種別] = null or [体重] = null then null
else if [種別] = "規格外" then "規格外"
else サイズ算出([種別],[体重])自作関数：サイズ算出
(syubetu,weight) =>
let
サイズ = if syubetu = "大型犬" then
if weight >= 30 then "L"
else if weight >= 15 then "M"
else "S"
else if syubetu = "中型犬" then
if weight >= 10 then "L"
else if weight >= 7 then "M"
else "S"
else if syubetu = "小型犬" then
if weight >= 5 then "L"
else if weight >= 3 then "M"
else "S"
else "対象外"
in
サイズ③ ②の別解
関数「サイズ算出」を変形しました。
なお、大型犬でも中型犬でも小型犬でもない場合はエラーを返しますので、try otherwise 構文を使用して「対象外」を返すようにしています。
カスタム式
try
if [種別] = null or [体重] = null then null
else if [種別] = "規格外" then "規格外"
else サイズ算出([種別],[体重])
otherwise
"対象外"関数：サイズ算出
(syubetu,weight) =>
let
lsize = if syubetu = "大型犬" then 30
else if syubetu = "中型犬" then 10
else if syubetu = "小型犬" then 5
else null
,
msize = if syubetu = "大型犬" then 15
else if syubetu = "中型犬" then 7
else if syubetu = "小型犬" then 3
else null
,
サイズ = if weight >= lsize then "L"
else if weight >= msize then "M"
else "S"
in
サイズVBAチックに変数 lsize,msize に大型犬、中型犬、小型犬の分岐体重を最初にセットして、サイズ算出のif式は一つにした。
（５）自作関数の作成方法
上記①の例でやってみます。
パワークエリのエディタ画面で
１）ホーム＞新しいソース＞その他＞空のクエリ
２）新しいクエリが作成されています。作成されたクエリを選択し「詳細エディター」をクリックします。
３）詳細エディターの中身を書き換えます。
ポイント
(weight) =>
の部分は引数（パラメーター）を受け取る部分です。受け取った引数を後続の式の中で利用できます。
４）「完了」ボタンをクリックして詳細エディターを閉じると下図のような表示となります。
ためしに weight のボックスに45と入力して「呼び出し」をクリックしてみます。
すると「呼び出しされた関数」に「L」と表示されました。
（この「呼び出しされた関数」は削除しておきましょう）
５）関数名を変更する
関数名（クエリ名）をわかりやすいものに変更しておきましょう。
この関数名（クエリ名）でほかのクエリ内からこの関数を呼び出しして使用します。
６）ほかのクエリから関数を呼び出しする
テーブル１のクエリで自作関数を利用してみます。
列の追加＞カスタム列
自作関数「大型犬サイズ算出」を入力し () 内に引数 [体重] 列を指定し、体重の値をインプットし対応するサイズを出力するようにしました。
６．andやorを使う。カッコの使いどころ
関数やVBAではif式で and や or を使ったりしますよね。また、かっこで優先順位を明示したりしますよね。パワークエリでもできます。
例題①[性別]が女 かつ [年齢]が20以上 の場合「A」、それ以外なら「B」
if [性別] = "女" and [年齢] >= 20 then "A" else "B"
例題②[性別]が女 または [年齢]が20以上 の場合「A」、それ以外なら「B」
if [性別] = "女" or [年齢] >= 20 then "A" else "B"例題③[性別]が女 かつ [年齢]が20以上 または [性別]が男 かつ [年齢]が50以上 の場合「A」、それ以外なら「B」
if ([性別] = "女" and [年齢] >= 20) or ([性別] = "男" and [年齢] >= 50) then "A" else "B"andがorより優先されるため実はこれはかっこ無しでも行けますが、かっこがあったほうがまとまりがあってわかりやすいような気がするのでかっこを付けています。
例題④[出身地]が日本以外 かつ [性別]が女 または [出身地]が日本以外 かつ[年齢]が50以上 の場合「A」、それ以外なら「B」
if [出身地] <> "日本" and ([性別] = "女" or [年齢] >= 50) then "A" else "B"
かっこをとると、違う答えになります。
if [出身地] <> "日本" and [性別] = "女" or [年齢] >= 50 then "A" else "B"
７．最後に
長くなってしまいました。パワークエリで複雑な if式 を入れようとしたらおたおたしたので、エクセル関数やVBAの場合と比較してみてやってみました。ガチャガチャやっているうちに、慣れてきて、普通に入れられるようになりました。しかし、無理に複雑な式を組もうとせず、作業列を使ったりしてできるだけ単純明快にするほうがいいですね。関数も同じですが。
お読みいただきありがとうございました！
８.参考文献、記事
PowerQuery if文でカスタム列を作成する｜複数条件とネスト
if関数を使って複数条件に応じた値を出力するカスタム列を作る（if then else）-PowerQuery基礎-
Learn Power Platform Power Query エラー処理
内部リンク：
【PowerQuery】ふんわりと理解するM言語
【PowerQuery】パワークエリ1年生～これは便利！と思ったもの
【PowerQuery】関数だと簡単にできるのにパワークエリではどうやるんだ?を集めた①文字列編
【PowerQuery】関数だと簡単にできるのにパワークエリではどうやるんだ?を集めた②日付編
【PowerQuery】一つ前の行のデータを利用したい
【PowerQuery】if式に悩む
【PowerQuery】二つの表のあるなしを比較する
【PowerQuery】生年月日から年齢を算出
【PowerQuery】○○を含む行を抽出、含まない行を抽出を複数の条件でやりたい
パワークエリはこちらの書籍で勉強しました。お勧めです！
コメント