入力値を受け取って同じ変換結果を返すカスタム関数を作り、複数ファイル・複数クエリに重複している整形ロジックを1か所へ集約します。 本記事では、完成形を先に確認したうえで、入力データの整え方、処理の組み立て、検算、更新運用までを順番に説明します。単に機能のボタンを追うのではなく、翌月のデータへ更新しても同じ結果を再現できることをゴールにします。
この記事でできるようになること
引数・戻り値・呼び出し方を理解し、共通変換を関数化する。あわせて、結果が合わないときにどこから確認すべきか、元データの様式変更へどう備えるかも分かります。
対象は、同じ変換を複数クエリへコピーして保守に困っている中級者です。サンプルの列名は読み替えられますが、データの粒度、キー、例外処理という考え方は、業種やファイル名が変わっても共通して使えます。
Power Queryで解決する業務課題と完成形
この業務で難しいのは、計算式そのものよりも、入力の意味がそろっていない状態で集計を始めてしまうことです。関数の責務を1つに絞り、入力と戻り値、失敗時の扱いを明示する、という設計を最初に固定すると、処理途中の判断がぶれにくくなります。
完成形は、担当者が毎回セルをコピーして作る表ではありません。元ファイルを所定の場所へ置き、更新を実行すると、同じ変換と判定が再実行される仕組みです。結果表には数値だけでなく、元ファイル、判定状態、エラー理由などの追跡列も残します。
図1では、入力から出力までの全体像を示しています。左側の元データを直接集計せず、中央で型・粒度・キーを整えた後、右側の確認用テーブルへ渡す流れを見てください。
入力データとキーを先に設計する
作業を始める前に、入力表を「何の1行か」という粒度で説明できる状態にします。同じ列名でも、明細1行、日次合計1行、月末残高1行では、結合や合計の意味が異なります。粒度が違う表をそのまま結合すると、行が増えたり金額が重複したりします。
使用するデータ
- 入力: ファイルのBinary、対象シート名、基準日など
- 処理: ヘッダー昇格、型変換、列選択
- 出力: 共通スキーマのTable
キー設計の考え方
関数の責務を1つに絞り、入力と戻り値、失敗時の扱いを明示する。キー候補は、見た目が同じだけでは十分ではありません。空白、全角半角、先頭ゼロ、日付と日時、重複の有無を確認し、結合前に一意性を検査します。
図2では、入力表ごとに役割と粒度を分けています。どの列を結合に使い、どの列を結果確認に残すかを、変換操作より前に整理するのがポイントです。
Power Queryで処理を組み立てる手順
ここから実装します。操作名を暗記するより、各ステップの入力と出力を確認してください。適用したステップには「型の変更1」のような自動名を残さず、「商品コードを文字列化」「請求単位へ集約」のように目的が分かる名前を付けます。
手順1: 重複している変換ステップを抽出する
重複している変換ステップを抽出する段階では、直前ステップの件数、列名、データ型を確認します。処理後にプレビューの先頭数行だけを見るのではなく、null件数、エラー件数、重複件数も確認してください。ここで異常を分離しておけば、最後の集計値だけを見て原因を探す必要がなくなります。
元データへ直接多くの処理を積まず、参照用またはステージング用のクエリを分けると、取得と業務ロジックの境界が明確になります。データソース変更時にも、影響範囲を取得部分へ限定できます。
手順2: 変化する値を引数へ置き換える
変化する値を引数へ置き換える段階では、直前ステップの件数、列名、データ型を確認します。処理後にプレビューの先頭数行だけを見るのではなく、null件数、エラー件数、重複件数も確認してください。ここで異常を分離しておけば、最後の集計値だけを見て原因を探す必要がなくなります。
この処理は前後のステップとセットで検証します。変換前の代表値を1件選び、変換後にどの行・どの値になったかを追跡してください。自動更新の品質は、この小さな追跡ができるかどうかで大きく変わります。
手順3: クエリを関数形式へ変換する
クエリを関数形式へ変換する段階では、直前ステップの件数、列名、データ型を確認します。処理後にプレビューの先頭数行だけを見るのではなく、null件数、エラー件数、重複件数も確認してください。ここで異常を分離しておけば、最後の集計値だけを見て原因を探す必要がなくなります。
この処理は前後のステップとセットで検証します。変換前の代表値を1件選び、変換後にどの行・どの値になったかを追跡してください。自動更新の品質は、この小さな追跡ができるかどうかで大きく変わります。
手順4: 代表ファイルで単体実行する
代表ファイルで単体実行する段階では、直前ステップの件数、列名、データ型を確認します。処理後にプレビューの先頭数行だけを見るのではなく、null件数、エラー件数、重複件数も確認してください。ここで異常を分離しておけば、最後の集計値だけを見て原因を探す必要がなくなります。
この処理は前後のステップとセットで検証します。変換前の代表値を1件選び、変換後にどの行・どの値になったかを追跡してください。自動更新の品質は、この小さな追跡ができるかどうかで大きく変わります。
手順5: ファイル一覧から関数を呼び出す
ファイル一覧から関数を呼び出す段階では、直前ステップの件数、列名、データ型を確認します。処理後にプレビューの先頭数行だけを見るのではなく、null件数、エラー件数、重複件数も確認してください。ここで異常を分離しておけば、最後の集計値だけを見て原因を探す必要がなくなります。
この処理は前後のステップとセットで検証します。変換前の代表値を1件選び、変換後にどの行・どの値になったかを追跡してください。自動更新の品質は、この小さな追跡ができるかどうかで大きく変わります。
手順6: 展開前にエラーと戻り値の列を確認する
展開前にエラーと戻り値の列を確認する段階では、直前ステップの件数、列名、データ型を確認します。処理後にプレビューの先頭数行だけを見るのではなく、null件数、エラー件数、重複件数も確認してください。ここで異常を分離しておけば、最後の集計値だけを見て原因を探す必要がなくなります。
この処理は前後のステップとセットで検証します。変換前の代表値を1件選び、変換後にどの行・どの値になったかを追跡してください。自動更新の品質は、この小さな追跡ができるかどうかで大きく変わります。
図3は、6つの操作を左から右へ並べたものです。途中でエラー行を捨てず、正常データと要確認データを分岐させている点に注目してください。
式の意味を分解して理解する
画面操作だけでも一部は作れますが、再利用性や例外処理を高めるには式の意味を確認する必要があります。次の式は、このユースケースの中心となる処理例です。実際のテーブル名と列名へ置き換えてください。
(content as binary, sheetName as text) as table =>
+let
+ Book = Excel.Workbook(content, null, true),
+ Data = Book{[Item = sheetName, Kind = "Sheet"]}[Data],
+ Result = Table.PromoteHeaders(Data)
+in
+ Result型注釈を付けると、関数が何を受け取り何を返すかが明確になります。ファイルごとの差を関数内の無数の条件分岐で吸収しないことも重要です。
式を一度に完成させず、変数または適用したステップごとに中間結果を表示すると理解しやすくなります。特にnull、0、空文字、存在しないキーは別物です。画面上で同じ空欄に見えても、比較式や集計結果は変わります。
図4では、式へ渡る入力、評価条件、出力の3段階を示しています。式の文字列だけでなく、どのフィルターや行が評価対象になるかを確認してください。
検算で更新結果の正しさを確認する
更新がエラーなく完了しても、業務上正しいとは限りません。検算は元データと結果を同じ軸で比較し、件数・金額・例外の3方向から行います。まず小さなサンプルで正解を作り、その後に全件へ広げます。
検算1: 代表的な正常ファイルで期待列が返る
代表的な正常ファイルで期待列が返ることを確認します。差がある場合は、差額だけを見るのではなく、どのキーの行が増減したかを反結合や重複一覧で抽出します。件数が一致していても、別の行が入れ替わっている可能性があるため、代表キーと合計値の両方を確認してください。
検算2: 空ファイル・シート欠落時の挙動を確認する
空ファイル・シート欠落時の挙動を確認することを確認します。差がある場合は、差額だけを見るのではなく、どのキーの行が増減したかを反結合や重複一覧で抽出します。件数が一致していても、別の行が入れ替わっている可能性があるため、代表キーと合計値の両方を確認してください。
検算3: 関数適用前後の件数を比べる
関数適用前後の件数を比べることを確認します。差がある場合は、差額だけを見るのではなく、どのキーの行が増減したかを反結合や重複一覧で抽出します。件数が一致していても、別の行が入れ替わっている可能性があるため、代表キーと合計値の両方を確認してください。
検算4: 1か所の修正が全呼出先へ反映される
1か所の修正が全呼出先へ反映されることを確認します。差がある場合は、差額だけを見るのではなく、どのキーの行が増減したかを反結合や重複一覧で抽出します。件数が一致していても、別の行が入れ替わっている可能性があるため、代表キーと合計値の両方を確認してください。
検算結果を保存する
更新日、元ファイル名、元件数、結果件数、主要合計、エラー件数を1行で残すと、翌月の変化を比較できます。検算表も成果物の一部です。
図5は、元データ、変換結果、例外一覧を照合する検算ループです。差が0になることだけでなく、除外した行に説明可能な理由があることを確認します。
よくある失敗と修正方法
このテーマでは、処理が止まるエラーよりも、止まらずに誤った値が出る状態へ注意が必要です。次の失敗は、プレビューだけでは気づきにくいため、設計段階で確認列と例外一覧を用意します。
失敗1: 巨大な万能関数を作る
巨大な万能関数を作ると、結果表は作れても意味が変わります。まず該当条件だけを抽出した確認クエリまたは確認メジャーを作り、発生件数と影響額を見ます。そのうえで、自動補正できるもの、人の判断が必要なもの、元システムで直すものへ分けます。
対処時は、異常行を単純に削除しないでください。除外理由、元キー、元ファイルを残せば、後から担当者へ確認できます。修正後は正常行だけでなく、この失敗条件が0件または想定件数になったことを再検算します。
失敗2: エラーをtryだけで握りつぶす
エラーをtryだけで握りつぶすと、結果表は作れても意味が変わります。まず該当条件だけを抽出した確認クエリまたは確認メジャーを作り、発生件数と影響額を見ます。そのうえで、自動補正できるもの、人の判断が必要なもの、元システムで直すものへ分けます。
対処時は、異常行を単純に削除しないでください。除外理由、元キー、元ファイルを残せば、後から担当者へ確認できます。修正後は正常行だけでなく、この失敗条件が0件または想定件数になったことを再検算します。
失敗3: 引数名とデータ型が不明確
引数名とデータ型が不明確と、結果表は作れても意味が変わります。まず該当条件だけを抽出した確認クエリまたは確認メジャーを作り、発生件数と影響額を見ます。そのうえで、自動補正できるもの、人の判断が必要なもの、元システムで直すものへ分けます。
対処時は、異常行を単純に削除しないでください。除外理由、元キー、元ファイルを残せば、後から担当者へ確認できます。修正後は正常行だけでなく、この失敗条件が0件または想定件数になったことを再検算します。
失敗4: 関数内で外部クエリへ過度に依存する
関数内で外部クエリへ過度に依存すると、結果表は作れても意味が変わります。まず該当条件だけを抽出した確認クエリまたは確認メジャーを作り、発生件数と影響額を見ます。そのうえで、自動補正できるもの、人の判断が必要なもの、元システムで直すものへ分けます。
対処時は、異常行を単純に削除しないでください。除外理由、元キー、元ファイルを残せば、後から担当者へ確認できます。修正後は正常行だけでなく、この失敗条件が0件または想定件数になったことを再検算します。
毎月更新できる運用へ仕上げる
一度だけ正しい集計を作ることと、毎月安全に更新できることは別です。担当者が変わっても、入力場所、更新順、検算、エラー時の対応が分かる状態へ整えます。
運用ポイント1: 関数名を動詞で付ける
関数名を動詞で付けるようにします。ルールはクエリやメジャーの中だけへ閉じ込めず、更新手順と確認表にも同じ言葉で記載します。誰がいつ確認したかを残すと、データの訂正や再更新が必要になったときに判断しやすくなります。
運用ポイント2: 入力・出力・前提をコメントに残す
入力・出力・前提をコメントに残すようにします。ルールはクエリやメジャーの中だけへ閉じ込めず、更新手順と確認表にも同じ言葉で記載します。誰がいつ確認したかを残すと、データの訂正や再更新が必要になったときに判断しやすくなります。
運用ポイント3: 正常・異常サンプルでテストする
正常・異常サンプルでテストするようにします。ルールはクエリやメジャーの中だけへ閉じ込めず、更新手順と確認表にも同じ言葉で記載します。誰がいつ確認したかを残すと、データの訂正や再更新が必要になったときに判断しやすくなります。
運用ポイント4: 変更時は呼出先一覧と影響範囲を確認する
変更時は呼出先一覧と影響範囲を確認するようにします。ルールはクエリやメジャーの中だけへ閉じ込めず、更新手順と確認表にも同じ言葉で記載します。誰がいつ確認したかを残すと、データの訂正や再更新が必要になったときに判断しやすくなります。
図6では、データ配置、更新、検算、例外確認、確定という運用サイクルを示しています。自動化の目的は確認をなくすことではなく、確認すべき箇所を限定することです。
導入前に確認したい適用範囲
Power Query カスタム関数は、繰り返し発生し、入力と判断ルールを説明できる業務で特に効果を発揮します。一方、毎回まったく異なる様式を人が読み解く作業や、正解が担当者の経験だけに依存する判断は、先にルールの整理が必要です。ツールへ移す前に、誰が見ても同じ判定になる条件を言葉とサンプルで残してください。
導入判断では、入力: ファイルのBinary、対象シート名、基準日などを安定して取得できるか、重複している変換ステップを抽出するための権限や保存場所があるかを確認します。更新頻度が低く対象が数件だけなら、仕組みの保守コストが手作業を上回る場合もあります。件数だけでなく、誤集計時の影響、監査証跡、担当者交代の頻度も判断材料です。
本番化する前に、正常例だけでなく、巨大な万能関数を作るケースを含むテストデータを用意します。期待結果を先に表へ書き、処理結果と一致するかを確認してください。正解表があれば、後日の式変更や元データ変更でも回帰テストを行えます。
小さく始める基準
最初の対象は1期間・1部門・代表的な入力に絞ります。検算方法まで合意できたら対象範囲を広げ、例外の発生率と確認工数を記録します。
本記事にはアフィリエイトリンクが含まれます。リンク経由で申込みがあった場合、運営者に報酬が支払われることがあります。
動画で体系的に学びたい方へ
ここまでの内容を自分のデータへ応用するには、画面操作と式の評価結果を往復しながら練習することが近道です。次の3講座は、この記事と学習範囲が重なるものを選んでいます。講座内容、価格、利用環境は変更されることがあるため、リンク先の最新情報を確認してから判断してください。
1. Excel＆Power BI両方で使えるPower Query 完全ガイド 初級者から上級者まで対応
Power Queryを実務レベルまで深めたい人。ExcelとPower BIの両方で使いたい人。
2. 【続】Microsoft – Excel Power Pivot 入門講座 ～Power Query Editor編～
Power Query Editorの操作を体系的に学びたい人。変換手順を動画で確認したい人。
3. Microsoft Excel – Power Pivot, Power Query, DAX 入門講座
Power QueryとDAXをExcelでまとめて学びたい人。データモデルの基礎から確認したい人。
Power Query カスタム関数を実務で使うためのまとめ
入力値を受け取って同じ変換結果を返すカスタム関数を作り、複数ファイル・複数クエリに重複している整形ロジックを1か所へ集約します。 成否を分けるのは、関数の責務を1つに絞り、入力と戻り値、失敗時の扱いを明示するという設計を、操作前に決められるかどうかです。
実装後は、代表的な正常ファイルで期待列が返ることから検算を始めてください。さらに、関数名を動詞で付けることで、翌月以降の更新にも耐えやすくなります。
最初から全データへ適用せず、代表的な正常例、境界値、例外例を含む小さなサンプルで結果を確定してから全件へ広げると、修正範囲を小さくできます。
コメント