[Power BI] Power Query のエラー処理 Try-Catch

（この記事は2022年7月23日に開催された[「PBIJP Power Query 秘密特訓「虎の穴」炎の復活編 #16」](https://powerbi.connpass.com/event/252981/)で使用しました。）

https://powerquery.microsoft.com/en-us/blog/new-power-query-m-language-keyword-catch/

https://bengribaudo.com/blog/2020/01/15/4883/power-query-m-primer-part-15-error-handling

https://docs.microsoft.com/ja-jp/power-query/error-handling

　Power Queryのエラーについては、「[(Power BI) Mから始めよう #10 エラー処理とHTTPステータスコード](https://qiita.com/spumoni/items/cd5843e0104fed9b4874)」 で書きましたが、新たに Try & Catch が使えるようになり、エラー処理がわかりやすくなりました。

# 1.エラー処理をしない場合

　以下の式は、エラーとなります。

```text:ノルマル
let
    Source = "foo" + 1
in
    Source
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/a5fb469f-888e-dcfc-2c1a-0bc206b7e105.png)

# 2.これまでのエラーハンドリング

　これまでは、エラーが発生したかどうかを try & otherwise で処理していました。

```text:TryOtherwise
let
    // １を加算できなかったら０を返す
    Source = try "foo" + 1 otherwise 0
in
    Source // 0
```

　try を使うと以下のように、正常に終了た場合でもエラーが発生した場合でも　**HasError** にbool値が入り、 **Error** にはエラーの詳細がレコードで入ります。

```text:try正常
let
    Source = 
        try 0 + 1
in
    Source
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/ee320b1e-ab2f-3c46-c799-9359baa7a331.png)

```text:tryエラー
let
    Source = 
        try "foo" + 1
in
    Source
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/59a7b29f-adff-a971-f355-b360b3525af1.png)

　使ってエラー内容を取り出す場合は以下のように行っていました。

```text:正常時には評価結果を、エラー発生時にはエラーメッセージを返す
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 の処理を以下のように書き換えることができます。

```text:Catch識別子なし
let
    Source = 
        try "foo" + 1
        catch() => 0
in
    Source // 0
```

 識別子を指定すると、その中にエラーの詳細を使用することができます。（ここでは **e** を使用していますが、**r** でも **err** でも何を使っても構いません）

```text:Catch識別子あり
let
    Source = 
        try "foo" + 1
        catch(e) =>
            e[Message]
in
    Source // 演算子 + を型 Text および Number に適用で
きません。
```

# 4.構造化エラーメッセージ

https://bengribaudo.com/blog/2022/05/24/6753/new-m-feature-structured-error-messages



　catchを使用した場合は、エラーがない場合はその結果の値を返し、エラーが発生した場合はcatchに続く関数を実行するようになります。

```text:TryCatch0
let
    Source = 
        try "foo" + 1
        catch(e) =>
            e
in
    Source
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/fc2ac7fb-4d4c-15bf-fe69-48bef38238c5.png)

　エラーの原因は **Reason** に記述されます。これを使ってエラーの原因に応じた処理を記述することもできます。

```text:
let
    Source = 
        try "foo" + 1
        catch(e) =>
            if e[Reason] = "Expression.Error 
            then 0
            else -1 
in
    Source // 0
```

　**Message.Format** は、エラーメッセージのテンプレートを定義します。
　**Message.Parameters** は、「**構造化エラー・メッセージ**」と呼ばれます。これを使って、以下のような式を書くことができます。

```text:StructuredErrorMessages
let
    Source = 
        try "foo" + 1
        catch(e) =>
            Text.Format(e[Message.Format], e[Message.Parameters])
in
    Source // 演算子 + を型 Text および Number に適用できません。
```

　また、catchに続く関数はインラインで記述する必要があります。

# 5.catchできるもの
　キャッチできるエラーをいくつか取り上げてみます。

## エラー：列が見つからない
　参照する列が見つからない場合は、以下のようなエラーになります。

```text:列が見つからない
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
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/8e6df2cb-4731-5b7a-b145-7f0642e3ffd8.png)

## エラー：演算子が適用できない
　型が異なる者同士の演算で発生するエラーです。

```text:演算子が適用できない
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}"
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/108acb1b-fe2f-af66-b2dc-7c0bb069a7a5.png)

## エラー：型変換できない

　TransformColumnTypes で型変換ができないエラーです。

```text:型変換できない
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
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/504bbf0b-433c-46cf-373e-47eb6560e984.png)

## エラー：ファイルが見つからない
 File.Contentsに try をつけると、対象のファイルが存在しない場合発生するエラーがキャッチできず、HasErrorはFALSEになります。

```text:データソースエラー
let
    Source = try File.Contents("C:\\foo")   // ファイルの情報を取得
in
    Souce
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/b1a6979c-b72f-b4e4-ff73-4ee30d64e538.png)

　これは、File.Contentsの時点で見えているエラー表示はプレビュー機能から表示されているものであり、Power BIはファイルの情報を取得しているだけで、まだエラーと認識していません。これは遅延評価であり、以下のようにコンテンツをPower BIが使う時点で評価され、その箇所に try をつけてやるとエラーを捕まえる事ができます。

```text:データソースエラーを捕まえる
let
    Source = File.Contents("C:\\foo"),
    Value = try Csv.Document(Source)    // ファイルをPower BIが使おうとするところでエラーが発生
in
    Value
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/5cf75769-4cfe-e4cb-3c22-8848d2723a38.png)

## エラー：ファイルが使用中
　使用中のエクセルファイルを取り込むと、以下のようなエラーになります。

```text:ファイルが使用中
let
    Source = 
        try 
            Excel.Workbook(
                File.Contents("C:\Users\........\20220723.xlsx"), null, 
                true
            )    // DataSource.Error
in
    Source
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/6ff53f86-f47e-7f2e-4793-11d9f45fc685.png)

　try をつける場所は、先の例と同様に、File.Contentsの部分ではなく、Excel.Workbookにつけるようにします。

# 6.エラーのあるエクセルシートの取り込み
　エクセルのシート上では、以下のようなエラーが発生します。

|表示名|内容|
|:--:|:--|
|#DIV/0!|割り算の分母がゼロまたは空白セル|
|#NAME?|関数が間違っている|
|#VALUE!|引数が間違っている|
|#REF!|参照が間違っている|
|#N/A!|戻り値が間違っている|
|#NUM!|使用可能な数値やデータの範囲を越えている|

　以下のようなエクセルシートを取り込んでみます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/eedd0d22-5274-c915-7ba3-f98d24666a04.png)

```text:Excel表の取り込み
let
    Source = 
        Excel.Workbook(
            File.Contents("C:\Users\........\20220723.xlsx"), 
            true, 
            true
        ){[Item="Sheet1",Kind="Sheet"]}[Data]
in
    Source
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/b7afcbad-eaae-7e0c-8cce-f862f257e7cf.png)

　Value列に try を使い、新しい列を追加します。

```text: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
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/dfc1df79-0078-efa4-c9dc-665a03a05fb3.png)

　この列を展開すると、以下のようになり、**DataFormat.Error** が発生していることがわかります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/0cf811b0-2f61-0337-1b5f-90401d28ab1f.png)

　つまり、エラーの状態としては、以下のようになっています。

```text:Error
error [
    Reason = "DataFormat.Error", 
    Message = "セル値 '#DIV/0!' が無効です。"
]
```

　これは、Power BIが計算した結果のエラーではなく、エクセルで発生したエラーが持ち込まれたものです。Power Queryでは、0による除算はエラーになりません。10を0で割った計算は、以下のように無限大（Infinity）になります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/027df428-c8e4-315d-07a1-e177fb81ff54.png)

　エラーメッセージを取得したい場合は、以下のように書くことができます。

```text:catchを使わないExcelエラーの取得
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
```

```text:catchを使ったExcelエラーの取得
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
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/626e6d2d-8d93-841c-a6cd-b004690465fc.png)

# 7.ところでtry catchを使ったエラー処理は必要なのか

　Power BIは、データのエラーだけでなく、システム的なエラーにも対応できるようになっています。

　「[一貫性のない API 応答](https://docs.microsoft.com/ja-jp/power-query/handlingtransformations#inconsistent-api-responses)」で書かれているように、APIの応答に応じて処理をする手段として使うこともあると思います。

　本来であれば、エラーが発生するようなデータは品質が悪いのであり、データ側で品質を上げるようにするのが正解です。意味を理解して処理を記述しないとデータに誤りが混じってしまうことになりかねません。また、エラーが発生しないようシステムやインフラを設計することが望ましい場合もあります。

　tryを使ったエラー処理は便利なところももある半面、処理結果が客観的に正しいのか、きちんと考えることが必要です。使い所を十分検討した上で使用することをお勧めします。