（この記事は2022年7月23日に開催された「PBIJP Power Query 秘密特訓「虎の穴」炎の復活編 #16」で使用しました。）
Power Queryのエラーについては、「(Power BI) Mから始めよう #10 エラー処理とHTTPステータスコード」 で書きましたが、新たに Try & Catch が使えるようになり、エラー処理がわかりやすくなりました。
1.エラー処理をしない場合
以下の式は、エラーとなります。
let
Source = "foo" + 1
in
Source
2.これまでのエラーハンドリング
これまでは、エラーが発生したかどうかを try & otherwise で処理していました。
let
// １を加算できなかったら０を返す
Source = try "foo" + 1 otherwise 0
in
Source // 0
try を使うと以下のように、正常に終了た場合でもエラーが発生した場合でも HasError にbool値が入り、 Error にはエラーの詳細がレコードで入ります。
let
Source =
try 0 + 1
in
Source
let
Source =
try "foo" + 1
in
Source
使ってエラー内容を取り出す場合は以下のように行っていました。
let
Source = try "foo" + 1,
// エラーが発生していたらエラーメッセージを返す
ErrorHandling =
if Source[HasError]
then Source[Error][Message]
else Source
in
ErrorHandling // 演算子 + を型 Text および Number に適用できません。
3.try catch を使用する
catchは、続くカッコ内に識別子を持つ場合と、持たない場合の２通りの書き方があります。識別子を持たない場合、上記 try otherwise の処理を以下のように書き換えることができます。
let
Source =
try "foo" + 1
catch() => 0
in
Source // 0
識別子を指定すると、その中にエラーの詳細を使用することができます。（ここでは e を使用していますが、r でも err でも何を使っても構いません）
let
Source =
try "foo" + 1
catch(e) =>
e[Message]
in
Source // 演算子 + を型 Text および Number に適用で
きません。
4.構造化エラーメッセージ
catchを使用した場合は、エラーがない場合はその結果の値を返し、エラーが発生した場合はcatchに続く関数を実行するようになります。
let
Source =
try "foo" + 1
catch(e) =>
e
in
Source
エラーの原因は Reason に記述されます。これを使ってエラーの原因に応じた処理を記述することもできます。
let
Source =
try "foo" + 1
catch(e) =>
if e[Reason] = "Expression.Error
then 0
else -1
in
Source // 0
Message.Format は、エラーメッセージのテンプレートを定義します。
Message.Parameters は、「構造化エラー・メッセージ」と呼ばれます。これを使って、以下のような式を書くことができます。
let
Source =
try "foo" + 1
catch(e) =>
Text.Format(e[Message.Format], e[Message.Parameters])
in
Source // 演算子 + を型 Text および Number に適用できません。
また、catchに続く関数はインラインで記述する必要があります。
5.catchできるもの
キャッチできるエラーをいくつか取り上げてみます。
エラー：列が見つからない
参照する列が見つからない場合は、以下のようなエラーになります。
let
Source =
Table.FromRecords(
{[No = 0], [No = 1], [No = 2], [No = 3], [No = 4], [No = 5]},
type table[No = Number.Type]
),
RaiseError =
try
Table.RenameColumns(
Source,
{{"Column", "New Column"}} // Expression.Error
)
in
RaiseError
エラー：演算子が適用できない
型が異なる者同士の演算で発生するエラーです。
let
Source =
Table.FromRecords(
{[No = "a"], [No = 1], [No = 2], [No = 3], [No = 4], [No = 5]},
type table[No = Number.Type]
),
RaiseError =
Table.TransformColumns(
Source,
{{"No", each try _ * 10}} // Expression.Error
),
#"Expanded {0}" = Table.ExpandRecordColumn(RaiseError, "No", {"HasError", "Error", "Value"}, {"HasError", "Error", "Value"})
in
#"Expanded {0}"
エラー：型変換できない
TransformColumnTypes で型変換ができないエラーです。
let
Source =
Table.FromRecords(
{[No = "a"], [No = 1], [No = 2], [No = 3], [No = 4], [No = 5]},
type table[No = Number.Type]
),
RaiseError =
try
Table.TransformColumnTypes(
Source,
{{"No", try Int64.Type}}
)
in
RaiseError
エラー：ファイルが見つからない
File.Contentsに try をつけると、対象のファイルが存在しない場合発生するエラーがキャッチできず、HasErrorはFALSEになります。
let
Source = try File.Contents("C:\\foo") // ファイルの情報を取得
in
Souce
これは、File.Contentsの時点で見えているエラー表示はプレビュー機能から表示されているものであり、Power BIはファイルの情報を取得しているだけで、まだエラーと認識していません。これは遅延評価であり、以下のようにコンテンツをPower BIが使う時点で評価され、その箇所に try をつけてやるとエラーを捕まえる事ができます。
let
Source = File.Contents("C:\\foo"),
Value = try Csv.Document(Source) // ファイルをPower BIが使おうとするところでエラーが発生
in
Value
エラー：ファイルが使用中
使用中のエクセルファイルを取り込むと、以下のようなエラーになります。
let
Source =
try
Excel.Workbook(
File.Contents("C:\Users\........\20220723.xlsx"), null,
true
) // DataSource.Error
in
Source
try をつける場所は、先の例と同様に、File.Contentsの部分ではなく、Excel.Workbookにつけるようにします。
6.エラーのあるエクセルシートの取り込み
エクセルのシート上では、以下のようなエラーが発生します。
| 表示名 | 内容 |
|---|---|
| #DIV/0! | 割り算の分母がゼロまたは空白セル |
| #NAME? | 関数が間違っている |
| #VALUE! | 引数が間違っている |
| #REF! | 参照が間違っている |
| #N/A! | 戻り値が間違っている |
| #NUM! | 使用可能な数値やデータの範囲を越えている |
以下のようなエクセルシートを取り込んでみます。
let
Source =
Excel.Workbook(
File.Contents("C:\Users\........\20220723.xlsx"),
true,
true
){[Item="Sheet1",Kind="Sheet"]}[Data]
in
Source
Value列に try を使い、新しい列を追加します。
let
Source =
Excel.Workbook(
File.Contents("C:\Users\........\20220723.xlsx"),
true,
true
){[Item="Sheet1",Kind="Sheet"]}[Data],
AddTry =
Table.AddColumn(
Source,
"Try",
each try [Value]
)
in
AddTry
この列を展開すると、以下のようになり、DataFormat.Error が発生していることがわかります。
つまり、エラーの状態としては、以下のようになっています。
error [
Reason = "DataFormat.Error",
Message = "セル値 '#DIV/0!' が無効です。"
]
これは、Power BIが計算した結果のエラーではなく、エクセルで発生したエラーが持ち込まれたものです。Power Queryでは、0による除算はエラーになりません。10を0で割った計算は、以下のように無限大（Infinity）になります。
エラーメッセージを取得したい場合は、以下のように書くことができます。
let
Source =
Excel.Workbook(
File.Contents("C:\Users\........\20220723.xlsx"),
true,
true
){[Item="Sheet1",Kind="Sheet"]}[Data],
AddError =
Table.AddColumn(
Source,
"ErrorMessage",
each
let
TryValue = try [Value]
in
if TryValue[HasError]
then TryValue[Error][Message]
else [Value]
)
in
AddError
let
Source =
Excel.Workbook(
File.Contents("C:\Users\........\20220723.xlsx"),
true,
true
){[Item="Sheet1",Kind="Sheet"]}[Data],
AddError =
Table.AddColumn(
Source,
"ErrorMessage",
each
try [Value]
catch (e)=> e[Message]
)
in
AddError
7.ところでtry catchを使ったエラー処理は必要なのか
Power BIは、データのエラーだけでなく、システム的なエラーにも対応できるようになっています。
「一貫性のない API 応答」で書かれているように、APIの応答に応じて処理をする手段として使うこともあると思います。
本来であれば、エラーが発生するようなデータは品質が悪いのであり、データ側で品質を上げるようにするのが正解です。意味を理解して処理を記述しないとデータに誤りが混じってしまうことになりかねません。また、エラーが発生しないようシステムやインフラを設計することが望ましい場合もあります。
tryを使ったエラー処理は便利なところももある半面、処理結果が客観的に正しいのか、きちんと考えることが必要です。使い所を十分検討した上で使用することをお勧めします。