目次
1. はじめに
2. nullと空文字と0
3. null の処理方針
4. 対応策３つ
5-1. 対応策① : null変換＋ラベル
5-2. 対応策② : null保持＋try構文
5-3. 対応策③ : 構造的分離と再統合
6. おわりに
1.はじめに
ある時、nullの入ってる列でnullを0に変換したら、もともと0は「仮単価」という意味を持っていて、nullは実は「未設定」という意味で、後で仮単価を抽出しようとして出来なくなったことがありました。
異なる意味を持つ null と 0 を同じように扱ってしまったことで、後の分析に影響することがあると、気づきました。
そこで、早速Copilot先生に「nullってどう扱ったらいいの？」「その対応策は？」と色々聞いてみたところ、思った以上に奥深くて驚きました。
今回は、1週間かけてじっくりと理解した内容を整理してご紹介します。
※QiitaではPower Queryのハイライトが未対応のため、コード部分はjavaで代用しています。関数名などが少しでも見やすくなるよう工夫しています。
2.nullと空文字と0
もうね、nullってExcelではほとんど出てこないし、概念理解に苦労しました。
ここでも、比喩で理解を深めました。
比喩理解
今からインタビューします。
今回、インタビューするのは、このテーブル500行でテーマは([列名]=単価)です。
インタビュー後、回答者の名前が書いている箱(セル)にレポート用紙(値)を入れます。
結果は以下の通り
| 回答者 | 箱の中 | 記載内容 |
|---|---|---|
| 1行目さん | レポート有 | 13.5 |
| 2行目さん | レポート有 | "" |
| 3行目さん | レポート有 | 0 |
| 4行目さん | レポート無 | null |
1～3行目さんはインタビューに応じてくれてレポート用紙を書いて箱に入れることができました。
沈黙した人もいましたが、それも答えの一つとして、何も書いてないレポートを箱に入れます。
4行目さんは不在のため、インタビューに応じることができませんでした。
その為、まだレポート用紙を書いていないし、箱に入れていない状態です。
この状態が、nullです。
だから、[販売数]=[単価]×[数量] の列は単価がnullの場合、そもそも[単価]の列のレポート用紙がないから計算できなくて、[販売数]の箱にもレポート用紙を入れることはできない。だから、null。
Table.Groupで集計する場合も、nullは計算に含まれない。レポート用紙がないから、計算したくても出来ない。
一方で、0はレポート用紙に「0」と明記されているため、0×数量=0 と計算が可能です。
つまり、こういう違いがある。
nullは、"計算できない"
0は、"計算して0になる"
3.null の処理方針
上記の比喩で理解を深めたように、nullは「回答者不在で、レポート用紙の提出を待っている箱」のような状態ということが分かりました。
つまり、nullがある場合、こう問われます。
この箱は空(null)のままでいいのか？ それとも、
意味を考えてレポート用紙を書いて箱に入れるべきか？
この問いに答え、判断して関数での処理をする必要があります。
この対応が、いわゆる「nullの処理」に当てはまります。
では、nullの処理をする時の手順を具体的に書いてみます。
3-1.テーブルを俯瞰で見て、null,0,空文字の存在を確認し、意味を確認する
→使用関数：Table.Profile
3-2.不要な列は除外する
→使用関数：Table.SelectColumns か Table.RemoveColumns
3-3.nullの意味と処理方針
→以下によくあるnullの意味と対応をまとめました。
| nullの意味 | 処理方針 | 0,空文字 混在時の注意点 |
|---|---|---|
| 欠損値 | 仮値に変換 | 0,空文字以外の仮値にする |
| 意図的な空白 | 空文字に変換 +ラベル補足 |
空文字が既にある場合はラベル補足 |
| 意味のある沈黙 | nullのまま保持 +try構文でラベル補足) |
空文字・0と混在しやすい |
| 一時的な不在 | nullのまま保持 +ラベル補足 |
状況によっては仮値も検討 |
4.対応策３つ
上記までで、nullの処理方針を決めました。
nullの意味を見極め、nullを変換するか保持するかを決めるところまでですね。
なので、対応策は、大きく次の３つに整理できます。
① null変換 + ラベル補足
→ nullを仮値や0に変換し、意味を補足する列を追加する
② null保持 + try構文
→ nullをそのまま残し、計算時にtry構文で安全に処理する
③ 構造的分離と再結合
→ nullを含む行を分離し、処理可能な部分だけ集計した後に再統合する
次に、実際のコードを書いてみます。
5-1.対応策① null変換＋ラベル
あらかじめ、列追加してその列のnullの意味を記載し、その後nullを変換する。
今回、0に変換する場合のコードです。
m
ラベル追加=Table.AddColumn(
元のテーブル,
"単価設定状態",
each if [単価] = null then "未設定"
else if [単価] = 0 then "仮単価"
else "設定済"
変換後= Table.TransformColumns(
ラベル追加,
{{"単価", each if _ = null then 0 else _, type number}}
)
5-2.対応策② null保持＋try構文
nullを保持すると決めた場合で、とりあえず動かしたい時は、try構文が有効。
ただし、nullと0の意味は混ざるので、混ぜたくない場合は対応策③のほうが良い。
m
販売金額追加 = Table.AddColumn(
元のテーブル,
"販売金額",
each try [単価] * [数量] otherwise null,
type nullable number
)
5-3.対応策③ 構造的分離と再統合
nullが0や他の意味を持つ場合、集計を分割して行い、最後にTable.Combineなどで結合する方法があります。
以下のコードは単価が、0（仮）,null（未設定）,数値（通常）と意味ごとにテーブルを分けて集計し、最後に結合しています。
これだと、nullと0が混ざらないので、後で仮単価や未設定を調べることができる。
m
// 仮単価（0）
仮値テーブル = Table.SelectRows(元のテーブル, each [単価] = 0)
仮値処理 = Table.AddColumn(仮値テーブル, "販売金額", each 0, type number)
// 未設定（null）
未設定テーブル = Table.SelectRows(元のテーブル, each [単価] = null)
未設定処理 = Table.AddColumn(未設定テーブル, "販売金額", each null, type nullable number)
// 通常単価
通常テーブル = Table.SelectRows(元のテーブル, each [単価] <> null and [単価] <> 0)
通常処理 = Table.AddColumn(通常テーブル, "販売金額", each [単価] * [数量], type number)
// Combineで再統合
再統合 = Table.Combine({仮値処理, 未設定処理, 通常処理})
＊Copilot先生から注意点
Combineや分岐処理が複雑になると、遅延評価による意図しない挙動が起きることがあるため、「null分岐＋Combine」の設計では、必要に応じて
Table.Bufferを使うべき場面がある。
Table.Bufferについて、私自身は現在学習中のため、詳しい解説は後日追記予定です。
6.おわりに
nullってなに？から始まり、nullをどう扱うべきなのか考える良い機会になりました。
結局、nullの処理に一定の正解なんてなくて、文脈だったり、後のステップでどう影響するかを見極めて、自分が決めないといけないのだ、と気づきました。
まるで、インタビューの日に回答者が現れず、それは、交通機関が乱れて到着出来なかったのか、語ることが無かったのか、語る意思がなかったのか、インタビューのテーマと関係ない人だったのか、その不在の意味を見極めて、レポート用紙に自分が記載して箱に提出するようなもの、のように感じます。
この記事を書いてる時に、Power Queryの自動型変換についても記録しようと思ったのですが、それについては、また別の記事でじっくりと記録していく予定です。