この章では、Power Queryでデータ処理を行う際に不可欠なエラーハンドリングとデバッグのテクニックについて学びます。クエリの実行中にエラーが発生した場合、その原因を特定し、適切に対処することで、データの整合性やクエリの安定性を保つことができます。特に、エラーが多発しやすい大規模なデータセットや複雑なクエリにおいて、エラーハンドリングとデバッグは重要なスキルです。

Power Queryには、大きく2種類のエラーがあります。

7.1. ステップレベルのエラー

7.1.1 エラーの表示

ステップレベルのエラーが発生すると、エラーの内容が黄色で表示され、後続のステップの処理は行われません。

let
    Source = "0",
    AddOne = Source + 1,
    Result = AddOne & "X"
in
    Result

この場合、原因は数値の１と文字の０を＋演算子で処理しようとしたためです。

エラーメッセージの右の「エラーに移動する」をクリックすると、エラーが発生しているステップに移動します。

7.1.2. 一般的なステップレベルのエラー

詳細エディタでコードを記述している段階で検出されるエラーがあります。

エラーが検出されない場合、「構文エラーが検出されませんでした」と表示されますが、詳細エディターが式のパターンや構造に問題を見つけられなかったというだけです。エラー検出の精度は高くありません。

その他、代表的なエラーには以下のようなものがあります。

DataSource.Error : ユーザーがデータソースにアクセスできない場合発生
識別子が認識できない：参照している識別子がみつからない
不明な関数：関数の綴りが間違っているか、存在しない関数が使われている
不明な列参照：参照する列名の綴りが間違っているか、既に削除・変更されている
不明なフィールド参照：指定されたフィールドの綴りが間違っているか、既に削除・変更されている
列挙内の要素が不足：{1,2,3}のリストから4番目のアイテムを抽出しようとするような場合
Formula.Firewall : 複数のデータを扱う時、データプライバシーレベルに関連してエラーが発生することがある
Expression.Error: キーがテーブルのどの行とも一致しませんでした

let
    Source = Table.FromRows(
        {
            {"YourTable", "Table", #table(2,{{1,2}}) },
            {"YourTable2", "Table", #table(2,{{1,2}}) }
        }, 
        {"Item", "Kind", "Data"}
    ),
    Navigation = Source{[Item="myTable", Kind="Table]"]}[Data] 
    //                        ~~~~~~~~~ 
    // Item列に myTable は存在しない。YourTable に変更すればエラーは発生しない
in
    Navigation

Expression.Error: キーがテーブルの複数の行に一致しました

let
    Source = Table.FromRows(
        {
            {"YourTable", "Table", #table(2,{{1,2}}) },
            {"YourTable2", "Table", #table(2,{{1,2}}) }
        }, 
        {"Item", "Kind", "Data"}
    ),
    Navigation = Source{[Kind="Table"]}[Data]
    //                   ~~~~~~~~~~~~
    // Kind列に Table は複数存在する
in
    Navigation

Expression.Error: 評価でスタックオーバーフローが発生したため、続行できません

// 再帰関数に終了条件がないため、利用可能メモリが不足した
let
    NoEnd = (x) => @NoEnd (x + 1)
in
    NoEnd(0)

7.2. セルレベルのエラー

7.2.1. エラーの表示

セルレベルのエラーが発生するとセルにErrorと表示されます。しかし、続くステップの処理は継続されます。つまり、エラーが発生した場合にできるだけトップレベルの士気に伝搬せず、その影響を限定的な範囲内にとどめる エラーの包含（Error Containment） が行われます。

let
    // サンプルデータの作成
    Source = Table.FromRows(
        {
            {"1", "123"},
            {"2", "ABC"},
            {"3", "456"}
        },
        {"ID", "Value"}
    ),

    // データ型を設定
    ChangedType = 
        Table.TransformColumnTypes(
            Source, 
            {
                {"ID", Int64.Type}, 
                {"Value", Int64.Type}
            }
        ),
    #"Added Index" = Table.AddIndexColumn(ChangedType, "インデックス", 1, 1, Int64.Type)
in
    #"Added Index"

Errorと表示されたセルをクリックすると、エラーメッセージを確認することができます。

7.2.1. データプロファイルツール

データプロファイルツールを使用すると、列の品質機能でセルレベルのエラーを識別できます。

列の品質

「表示」メニューから「列の品質」のチェックボックスをオンにすると、データの列名の下にデータの品質に関する情報が表示されます。品質は、「有効」「エラー」「空」の3種類に分類され、それぞれ何パーセントあるか表示されます。

列の分布

「列の分布」をオンにすると、各列の値の頻度と分布を示すビジュアルが列名の下に表示されます。表示は、頻度が高い値から降順に並び替えされています。

ビジュアルの上にカーソルを置くと、更に情報が表示されます。

列のプロファイル

「列のプロファイル」をオンにすると、列内のデータの詳細を確認できます。「列の品質」「列の分布」の情報に加え、詳しい統計情報がデータプレビューセクションの下に表示されます。

値分布グラフをクリックし、値をフィルターするステップを追加することができます。

右クリックして、更に詳しい処理を追加することもできます。

値分布グラフの右上隅にある省略記号ボタンをクリックすると、「コピー」と「グループ化」を選択することができます。

ただし、このプロファイリングは、データの上位1000行に対してのものです。パフォーマンスを犠牲にしてもデータ全体のプロファイリングを見たい場合は、左下の「上位1000行に基づく列のプロファイリング」をクリックし、「データセット全体に基づく列のプロファイリング」に変更します。

7.3. エラーハンドリングの基本テクニック

エラーが発生した際に、適切に対処するためのエラーハンドリングテクニックを学びます。これにより、エラー発生時のクエリの挙動をコントロールし、データ処理をスムーズに進めることができます。

7.3.1. `try`と`otherwise`の活用

tryを用いることで、特定の処理でエラーが発生した場合にエラー内容をキャプチャし、otherwiseで設定したデフォルト値を使って処理を続行できます。これにより、エラー発生時の処理を制御可能になります。

以下の例では、日付変換に失敗した場合、デフォルトの日付（例: "2024-01-01"）を設定することで、データの一貫性を保ちながらエラーハンドリングを行います。

let
    Source = 
        Text.Combine(
            {
                "ID,Name,Date",
                "1,Alice,2024-11-25",
                "2,Bob,2024-8-5",
                "3,Charlie,2024/6/2",
                "4,Chris,""25th November 2024"""
            },
            "#(cr)#(lf)"
        ),
    CsvData = Csv.Document(Source),
    #"Promoted Headers" = Table.PromoteHeaders(CsvData, [PromoteAllScalars=true]),
    AddColumn = 
        Table.AddColumn(
            #"Promoted Headers",
            "Change Type",
            each 
            // try - otherwise を使った処理
                try
                    Date.FromText(_[Date])
                otherwise
                    #date(2024,1,1),
            type date
        )
in
    AddColumn

7.3.2. `try` と `catch` の活用

catch を使うと、エラー情報を更に詳細に捕まえることができます。

catchの後に続くカッコ内に識別子を持つ場合と、持たない場合の２通りの書き方があります。識別子を持たない場合、上記 try otherwise と同様の処理になります。

let
    Source = 
        try "foo" + 1
        catch() => 0
in
    Source

文字列と数値を足し算しようとしてエラーになりますが、catch() => で設定された値 0 が返されます。

catchを使って、エラーの情報を取得したい場合は、以下のように書きます。

let
    Source = 
        try "foo" + 1
        catch(e) =>
            e[Message]
in
    Source

結果は「演算子 + を型 Text および Number に適用できません。」と表示されます。

7.3.3 HasErrorを使ってエラーを検知する

エラーになるかどうかを判定するにはHasErrorを使って調べます。

以下のコードでは、文字列を数値に変換を行いますが、4番目がエラーになります。

let
    Source = {"1", "2", "3", "a"},
    Result = List.Transform(
        Source,
        Number.FromText
    )
in
    Result

値が数値かどうか判定する処理は、以下の様になります。

let
    Source = {"1", "2", "3", "a"},
    Result = List.Transform(
        Source,
        each (try Number.FromText(_) )[HasError]
    )
in
    Result

7.3.4 エラーメッセージの表示とカスタムエラーメッセージ

エラー時にメッセージを表示する

クエリ内でエラーが発生した場合に、標準のエラーメッセージをカスタマイズして表示することで、原因の特定が容易になります。

let
    Source = Table.FromRows(
        {
            {"1", "2024/11/1"},
            {"2", "2024/12/1"},
            {"3", "2024/13/1"}
        },
        type table [ID = text, Date = text]
    ),
    Result = Table.TransformColumns(
        Source,
        {
            {
                "Date",
                each try Date.FromText(_) otherwise "日付に変換できません"
            }
        }
    )
in
    Result

上記は、エラーが発生した理由を文字列で返すようになっています。

`Error.Record` 関数を使う

次の例は、Error.Record関数を使用し、カスタムエラーメッセージを含むエラーオブジェクトを生成します。

let
    Source = Table.FromRows(
        {
            {"1", "2024/11/1"},
            {"2", "2024/12/1"},
            {"3", "2024/13/1"}
        },
        type table [ID = text, Date = text]
    ),
    Result = Table.TransformColumns(
        Source,
        {
            {
                "Date",
                each 
                    try Date.FromText(_) 
                    otherwise 
                        Error.Record(
                            "カスタムエラー", 
                            "日付に変換できません", 
                            [Details = "詳細情報：日付書式エラー"]
                        )
            }
        }
    )
in
    Result

7.3.5 意図的にエラーを発生させる

error を呼び出し、表示したエラーメッセージをつけます。

let
    Source = Table.FromRows(
        {
            {"1", "2024/11/1"},
            {"2", "2024/12/1"},
            {"3", "2024/13/1"}
        },
        type table [ID = text, Date = text]
    ),
    Result = Table.TransformColumns(
        Source,
        {
            {
                "Date",
                each 
                    try Date.FromText(_) 
                    otherwise error "日付に変換できません"
            }
        }
    )
in
    Result

errorは、実際にエラーが発生しない条件においてもエラーを発生させることができます。

let
    Source = Table.FromRows(
        {
            {"1", "2024/11/1"},
            {"2", "2024/12/1"},
            {"3", "2024/12/24"}
        },
        type table [ID = text, Date = text]
    ),
    Result = Table.TransformColumns(
        Source,
        {
            {
                "Date",
                each 
                    if _ = "2024/12/24"
                    then error "クリスマス"
                    else Date.FromText(_)
            }
        }
    )
in
    Result

また、エラー記録には Reason Message Detail などの詳細情報を含めることができます。

let
    Source = Table.FromRows(
        {
            {"1", "2024/11/1"},
            {"2", "2024/12/1"},
            {"3", "2024/12/24"}
        },
        type table [ID = text, Date = text]
    ),
    Result = Table.TransformColumns(
        Source,
        {
            {
                "Date",
                each 
                    if _ = "2024/12/24"
                    then error 
                        [
                            Reason = "クリスマス",
                            Message = "クリスマスを指定してはいけません",
                            Detail = [Date = "2024/12/24"]
                        ]
                    else Date.FromText(_)
            }
        }
    )
in
    Result

更に詳しい内容は、以下も参照してください。

7.4. デバッグツールの活用方法

Power Queryには、クエリのステップごとに処理結果を確認し、エラーの原因を特定するためのデバッグツールが備わっています。

7.4.1. ステップごとのエラー確認

Power Queryエディター内のステップバーで、各ステップの処理結果を確認し、エラーが発生した場所を特定できます。複雑な処理を複数のステップに分割し、処理結果をステップごとに確認することで、エラー原因を細かく調査します。

ステップの名前を変更する

ステップが作成されると、デフォルトで汎用的な名前が割り当てられます。この名前は、そのステップが具体的に何をしているのかわかりにくいものになっています。より説明的な名前に変更することで、全体的な処理の流れをつかむことができ、コードの読みやすさと理解しやすさが向上します。

将来クエリを修正する必要がある場合、ステップが適切に命名されていると、修正箇所を見つけるのがずっと簡単になります。

ステップにコメントをつける

各ステップにコメントをつけておくと、問題の解決が速くなります。

例えば、ステップのプロパティを設定することで以下のようにコードにコメントを付けることができます。

let
    Source = 1,
    // 1を加える
    Second = Source + 1,
    // 2を加える
    Third = Second + 2,
    // 結果
    Result = Third
in
    Result

詳細エディターで直接記載することもでき、書かれたコメントは以下のようにステップ名右のアイコンにカーソルを置くと表示されます。

複雑な式を管理しやすい単位に分解する

M言語は、関数型の言語なので、複雑なネストした式を作成することができます。これは、非常に強力な機能ですが、問題が発生したときにデバッグするのが難しい場合があります。

複雑な式は、各部分を個別に隔離し、テストできるようにしておくと、問題の根本原因を特定しやすくなります。

その方法としては、別のクエリで関数を作成したり、同一コード中に関数を作成するなどの方法があります。

let
    // カスタム関数：文字列を数値に変換して＋１する
    MyFunction = (n as text) as number =>
        let
            Value = Int64.From(n),
            Result = Value + 1
        in
            Result,

    // テーブルを作成
    Source = Table.FromRows(
        {
            {"1", "123"},
            {"2", "456"},
            {"3", "789"}
        },
        type table [ID = text, Value = text]
    ),

    // テーブルの Value 列にカスタム関数を適用
    TableTransform = 
        Table.TransformColumns(
            Source,
            {
                {
                    "Value",
                    each MyFunction(_)
                }
            }
        )
in
    TableTransform

7.4.2. 「データのプレビュー」を使ったデバッグ

データのプレビューを使用して、クエリの各ステップでどのような変換が行われたかを視覚的に確認します。これにより、データの変換が期待通りに行われているかを検証します。

データのプレビューでエラーデータを迅速に見つけ出し、詳細なデバッグが可能です。

7.4.3. クエリの実行時間の確認

クエリの実行時間を確認し、処理が重いステップを特定することで、パフォーマンス最適化の候補を見つけます。

複数の集計処理が行われるクエリで、特に時間がかかるステップを特定し、処理の順序を再編成して最適化します。

7.5. エラーログの作成と管理

Power Queryで発生したエラーを記録し、後で確認できるようにするためにエラーログを作成します。エラーログを用いることで、定期的にエラーログを確認し、繰り返し発生するエラーの修正やクエリの改善を行います。

また、必要に応じてエラーログを整理し、古いエラー記録を削除することで、ログの可読性と管理性を向上させます。

7.5.1. エラーログ用のクエリ作成

let
    // サンプルデータ（数値として変換できない値を含む）
    Source = Table.FromRows({
        {"123"},
        {"45.67"},
        {"abc"},   // 数値に変換できない値
        {"789"},
        {"1.23e5"},
        {"error"}  // 数値に変換できない値
    }, {"Input"}),

    // 文字列を数値に変換する関数
    ConvertToNumber = (input as text) =>
        try Number.FromText(input) 
        otherwise Error.Record("Conversion Error", "数値に変換できません", [Value = input]),

    // 数値に変換する列を追加
    AddConvertedColumn = Table.AddColumn(Source, "Converted", each ConvertToNumber([Input])),

    // エラー行のみを抽出
    ErrorRows = Table.SelectRows(AddConvertedColumn, each Value.Is([Converted], type record)),

    // エラーログを整形
    ErrorLog = Table.TransformColumns(ErrorRows, {"Converted", each Record.ToTable(_)})
in
    ErrorLog

7.5.2. エラーの種類に応じたログ分類

エラーログ内でエラーを種類別に分類し、分析や原因特定を効率化します。たとえば、型エラーや参照エラーなど、発生原因ごとに分類します。

let
    // サンプルデータ
    Source = Table.FromRows({
        {"123"},   // 正常データ
        {"abc"},   // 数値変換エラー
        {null},    // NULLデータエラー
        {"45.67"}, // 正常データ
        {"error"}, // 数値変換エラー
        {"1.2e3"}  // 正常データ
    }, {"Input"}),

    // カスタムエラーチェック関数
    ProcessData = (input as nullable text) as any =>
        try
            if input = null then 
                Error.Record("Null Value Error", "値がNULLです", [Value = input])
            else if not Text.Contains(input, "error") and Number.FromText(input) = null then
                Error.Record("Conversion Error", "数値に変換できません", [Value = input])
            else
                Number.FromText(input)
        otherwise 
            Error.Record("General Error", "予期しないエラー", [Value = input]),

    // データを変換しつつエラー処理
    ProcessedTable = Table.AddColumn(Source, "Processed", each ProcessData([Input])),

    // エラー行を抽出
    ErrorRows = Table.SelectRows(ProcessedTable, each Value.Is([Processed], type record)),

    // エラー分類列を追加
    ErrorWithType = Table.AddColumn(ErrorRows, "ErrorType", each [Processed][Reason]),

    // エラーログを整形
    ErrorLog = Table.TransformColumns(ErrorWithType, {"Processed", each Record.ToTable(_)})
in
    ErrorLog