Power Query 実務テンプレ | データ取込・更新系：取込失敗ログ作成

Excel VBA Power Query M Formula Language
スポンサーリンク

ゴールのイメージをそろえる

今回のテーマは「Power Query でフォルダ一括取込をしたときに、“うまく読めなかったファイルだけを一覧にした『取込失敗ログ』クエリを作る実務テンプレ」を身につけること」です。

ポイントはこうです。
本体クエリでは「成功したファイルだけ」を使う。
別クエリとして「失敗したファイルだけ」を一覧にしておく。

これができると、「どのファイルが壊れているのか」「どのファイルがロックされているのか」を、Excel 上でパッと確認できるようになります。

基本の考え方：try の結果を“捨てずに残す”

まずは「try で包んで HasError を見る」型を思い出す

以前やった「壊れたファイルを自動スキップ」と同じで、入口は try です。

try 式 の結果は、「HasError」「Value」「Error」などを持つレコードになります。

HasError … true ならエラー、false なら成功
Value … 成功したときの結果（テーブルなど）
Error … エラーの詳細

この「HasError」を使って、
成功した行だけを本体クエリに回し、
失敗した行だけを「ログクエリ」にする、という二段構えにします。

「成功だけ使う」と「失敗だけ一覧にする」を分けて考える

やることはシンプルで、次の二つです。

成功行だけを残して結合するクエリ（本体）。
失敗行だけを残して一覧にするクエリ（ログ）。

どちらも元は同じ「WithTry」テーブルから分岐させます。
つまり、「try までの処理は共通」「そこから先のフィルタだけ変える」という設計にすると、メンテナンスが楽になります。

例題1：フォルダ内 CSV 一括取込＋取込失敗ログ

想定する状況

C:\Data\DailyCsv に、毎日の CSV が溜まっているとします。
たまに壊れた CSV や、文字コードがおかしくて読めない CSV が混ざる。

やりたいことは二つです。
正常な CSV だけを結合した「本体クエリ」。
読めなかった CSV のファイル名・更新日時・エラー内容を一覧にした「取込失敗ログクエリ」。

共通部分：try 付きで CSV に変換するところまで

まずは「共通の土台」となるクエリを作ります。

let
    Source = Folder.Files("C:\Data\DailyCsv"),

    OnlyCsv =
        Table.SelectRows(
            Source,
            each Text.Lower([Extension]) = ".csv"
        ),

    // ここが“共通の肝”になる部分
    WithTry =
        Table.AddColumn(
            OnlyCsv,
            "CsvTry",
            each
                try
                    Csv.Document(
                        [Content],
                        [
                            Delimiter = ",",
                            Encoding = 65001,
                            QuoteStyle = QuoteStyle.Csv
                        ]
                    ),
            type any
        )
in
    WithTry
Power Query

このクエリを、たとえば「Csv_取込ベース」と名前を付けておきます。
ここまでで、「各ファイルに対して Csv.Document を試し、その結果（成功 or エラー）を CsvTry 列に持っているテーブル」ができています。

ここから先を「成功用」と「失敗ログ用」に分けます。

本体クエリ：成功したファイルだけ結合する

HasError = false の行だけを使う

「Csv_取込ベース」を参照して、本体クエリを作ります。

let
    Base = Csv_取込ベース,

    // 成功した行だけ残す
    SuccessOnly =
        Table.SelectRows(
            Base,
            each [CsvTry][HasError] = false
        ),

    // 成功した Value（テーブル）を取り出す
    WithTable =
        Table.AddColumn(
            SuccessOnly,
            "CsvTable",
            each [CsvTry][Value],
            type table
        ),

    // 代表テーブルから列名を取得
    SampleTable = WithTable[CsvTable]{0},
    ColNames = Table.ColumnNames(SampleTable),

    // 展開して結合
    Combined =
        Table.ExpandTableColumn(
            WithTable,
            "CsvTable",
            ColNames,
            ColNames
        )
in
    Combined
Power Query

これが「いつものフォルダ一括取込（壊れたファイルは自動スキップ）」の完成形です。
ここまでは前にやった内容と同じですが、今回の主役は「失敗した側」です。

取込失敗ログクエリ：HasError = true の行だけ一覧にする

失敗したファイルの「名前・更新日時・エラー内容」を残す

同じ「Csv_取込ベース」を参照して、今度は失敗ログ用クエリを作ります。

let
    Base = Csv_取込ベース,

    // 失敗した行だけ残す
    ErrorOnly =
        Table.SelectRows(
            Base,
            each [CsvTry][HasError] = true
        ),

    // エラー内容を取り出す列を追加（任意）
    WithErrorMessage =
        Table.AddColumn(
            ErrorOnly,
            "エラーメッセージ",
            each
                let
                    err = [CsvTry][Error]
                in
                    err[Message],
            type text
        ),

    // ログとして欲しい列だけに絞る
    LogTable =
        Table.SelectColumns(
            WithErrorMessage,
            {
                "Name",
                "Folder Path",
                "Date modified",
                "エラーメッセージ"
            }
        )
in
    LogTable
Power Query

ここでの重要ポイントは二つです。

[CsvTry][HasError] = true で「失敗した行だけ」を選んでいること。
[CsvTry][Error][Message] から「人間が読めるエラーメッセージ」を取り出していること。

このクエリをシートにロードしておけば、
「どのファイルが読めなかったか」「どんなエラーだったか」が一覧で見える“取込失敗ログ”になります。

例題2：Excel 一括取込でも同じ型でログを作る

想定する状況

C:\Data\MonthlyBooks に、毎月の Excel ブックが溜まっているとします。
たまに破損ファイルやパスワード付きファイルが混ざり、Excel.Workbook がエラーになる。

CSV のときと同じように、
成功したブックだけ結合する本体クエリと、
失敗したブックを一覧にするログクエリを作ります。

共通ベースクエリ（Excel 版）

let
    Source = Folder.Files("C:\Data\MonthlyBooks"),

    OnlyXlsx =
        Table.SelectRows(
            Source,
            each Text.Lower([Extension]) = ".xlsx"
        ),

    WithTry =
        Table.AddColumn(
            OnlyXlsx,
            "SheetTry",
            each
                try
                    let
                        wb =
                            Excel.Workbook(
                                [Content],
                                true,
                                true
                            ),
                        firstSheetRow =
                            Table.SelectRows(wb, each [Kind] = "Sheet"){0},
                        firstSheetTable = firstSheetRow[Data],
                        promoted =
                            Table.PromoteHeaders(
                                firstSheetTable,
                                [PromoteAllScalars = true]
                            )
                    in
                        promoted,
            type any
        )
in
    WithTry
Power Query

これを「Excel_取込ベース」として保存しておきます。

失敗ログクエリ（Excel 版）

let
    Base = Excel_取込ベース,

    ErrorOnly =
        Table.SelectRows(
            Base,
            each [SheetTry][HasError] = true
        ),

    WithErrorMessage =
        Table.AddColumn(
            ErrorOnly,
            "エラーメッセージ",
            each
                let
                    err = [SheetTry][Error]
                in
                    err[Message],
            type text
        ),

    LogTable =
        Table.SelectColumns(
            WithErrorMessage,
            {
                "Name",
                "Folder Path",
                "Date modified",
                "エラーメッセージ"
            }
        )
in
    LogTable
Power Query

これで、「壊れた Excel」「ロックされて読めなかった Excel」「パスワード付きで読めなかった Excel」などが、
全部「取込失敗ログ」として一覧化されます。

重要ポイントの深掘り

「try の結果を列に持つ」ことがログ作成の出発点

取込失敗ログを作るうえで一番大事なのは、
「エラーをその場で潰さず、try の結果を列として残しておく」ことです。

try 式 otherwise null と書いてしまうと、
エラーの詳細が消えてしまい、「なぜ失敗したか」が分からなくなります。

ログを作りたいときは、

try 式 の結果をそのまま列に入れる（HasError / Value / Error を持つレコード）。
HasError で成功／失敗を判定する。
Error[Message] で人間向けのメッセージを取り出す。

この3ステップを意識しておくと、「ログ用クエリ」が自然に書けるようになります。

ログに何を残すかを決めておく

最低限、次のあたりを残しておくと実務で役に立ちます。

Name（ファイル名）
Folder Path（フォルダパス）
Date modified（更新日時）
エラーメッセージ

必要なら、Size（サイズ）や Extension（拡張子）も足しておくと、
「サイズ 0 のファイルばかり失敗している」「特定の拡張子だけ失敗している」などの傾向も見えます。

実務テンプレとしてのまとめ

「取込失敗ログ作成」の型は、次の一言に集約できます。

「フォルダ一括取込の途中で try の結果を列に持たせ、HasError = true の行だけを別クエリとして一覧化する」

これを CSV 版・Excel 版で一度手を動かして体に入れてしまえば、
「壊れたファイルが混ざっても、本体クエリは止めずに」「何が壊れているかはログで確認する」という、
“現場で強い”構成が自然に組めるようになります。

スポンサーリンク
タイトルとURLをコピーしました