元ファイルの列追加・列欠落・列名変更で更新が止まる原因を切り分け、必要列の契約とMissingFieldオプションで変更に強いクエリへ直します。 本記事では、完成形を先に確認したうえで、入力データの整え方、処理の組み立て、検算、更新運用までを順番に説明します。単に機能のボタンを追うのではなく、翌月のデータへ更新しても同じ結果を再現できることをゴールにします。
この記事でできるようになること
固定列参照の弱点を理解し、MissingFieldや列選択で変更に耐えるクエリを作る。あわせて、結果が合わないときにどこから確認すべきか、元データの様式変更へどう備えるかも分かります。
対象は、元ファイルの列変更で更新エラーが起きる担当者です。サンプルの列名は読み替えられますが、データの粒度、キー、例外処理という考え方は、業種やファイル名が変わっても共通して使えます。
Power Queryで解決する業務課題と完成形
この業務で難しいのは、計算式そのものよりも、入力の意味がそろっていない状態で集計を始めてしまうことです。必須列、任意列、無視する列を分け、必須列の欠落だけを更新停止条件にする、という設計を最初に固定すると、処理途中の判断がぶれにくくなります。
完成形は、担当者が毎回セルをコピーして作る表ではありません。元ファイルを所定の場所へ置き、更新を実行すると、同じ変換と判定が再実行される仕組みです。結果表には数値だけでなく、元ファイル、判定状態、エラー理由などの追跡列も残します。
図1では、入力から出力までの全体像を示しています。左側の元データを直接集計せず、中央で型・粒度・キーを整えた後、右側の確認用テーブルへ渡す流れを見てください。
入力データとキーを先に設計する
作業を始める前に、入力表を「何の1行か」という粒度で説明できる状態にします。同じ列名でも、明細1行、日次合計1行、月末残高1行では、結合や合計の意味が異なります。粒度が違う表をそのまま結合すると、行が増えたり金額が重複したりします。
使用するデータ
- 旧様式: 商品コード・数量・金額
- 新様式: 商品コード・数量・単価・金額
- 欠損様式: 商品コード・金額のみ
キー設計の考え方
必須列、任意列、無視する列を分け、必須列の欠落だけを更新停止条件にする。キー候補は、見た目が同じだけでは十分ではありません。空白、全角半角、先頭ゼロ、日付と日時、重複の有無を確認し、結合前に一意性を検査します。
図2では、入力表ごとに役割と粒度を分けています。どの列を結合に使い、どの列を結果確認に残すかを、変換操作より前に整理するのがポイントです。
Power Queryで処理を組み立てる手順
ここから実装します。操作名を暗記するより、各ステップの入力と出力を確認してください。適用したステップには「型の変更1」のような自動名を残さず、「商品コードを文字列化」「請求単位へ集約」のように目的が分かる名前を付けます。
手順1: エラーが発生する適用したステップを特定する
エラーが発生する適用したステップを特定する段階では、直前ステップの件数、列名、データ型を確認します。処理後にプレビューの先頭数行だけを見るのではなく、null件数、エラー件数、重複件数も確認してください。ここで異常を分離しておけば、最後の集計値だけを見て原因を探す必要がなくなります。
元データへ直接多くの処理を積まず、参照用またはステージング用のクエリを分けると、取得と業務ロジックの境界が明確になります。データソース変更時にも、影響範囲を取得部分へ限定できます。
手順2: 参照中の固定列名と現在の列名を比較する
参照中の固定列名と現在の列名を比較する段階では、直前ステップの件数、列名、データ型を確認します。処理後にプレビューの先頭数行だけを見るのではなく、null件数、エラー件数、重複件数も確認してください。ここで異常を分離しておけば、最後の集計値だけを見て原因を探す必要がなくなります。
この処理は前後のステップとセットで検証します。変換前の代表値を1件選び、変換後にどの行・どの値になったかを追跡してください。自動更新の品質は、この小さな追跡ができるかどうかで大きく変わります。
手順3: 必須列・任意列の一覧を定義する
必須列・任意列の一覧を定義する段階では、直前ステップの件数、列名、データ型を確認します。処理後にプレビューの先頭数行だけを見るのではなく、null件数、エラー件数、重複件数も確認してください。ここで異常を分離しておけば、最後の集計値だけを見て原因を探す必要がなくなります。
この処理は前後のステップとセットで検証します。変換前の代表値を1件選び、変換後にどの行・どの値になったかを追跡してください。自動更新の品質は、この小さな追跡ができるかどうかで大きく変わります。
手順4: 任意列はMissingField.UseNullで補う
任意列はMissingField.UseNullで補う段階では、直前ステップの件数、列名、データ型を確認します。処理後にプレビューの先頭数行だけを見るのではなく、null件数、エラー件数、重複件数も確認してください。ここで異常を分離しておけば、最後の集計値だけを見て原因を探す必要がなくなります。
この処理は前後のステップとセットで検証します。変換前の代表値を1件選び、変換後にどの行・どの値になったかを追跡してください。自動更新の品質は、この小さな追跡ができるかどうかで大きく変わります。
手順5: 必須列欠落はファイル名付きで検知する
必須列欠落はファイル名付きで検知する段階では、直前ステップの件数、列名、データ型を確認します。処理後にプレビューの先頭数行だけを見るのではなく、null件数、エラー件数、重複件数も確認してください。ここで異常を分離しておけば、最後の集計値だけを見て原因を探す必要がなくなります。
この処理は前後のステップとセットで検証します。変換前の代表値を1件選び、変換後にどの行・どの値になったかを追跡してください。自動更新の品質は、この小さな追跡ができるかどうかで大きく変わります。
手順6: 想定外の追加列を監視表へ出す
想定外の追加列を監視表へ出す段階では、直前ステップの件数、列名、データ型を確認します。処理後にプレビューの先頭数行だけを見るのではなく、null件数、エラー件数、重複件数も確認してください。ここで異常を分離しておけば、最後の集計値だけを見て原因を探す必要がなくなります。
この処理は前後のステップとセットで検証します。変換前の代表値を1件選び、変換後にどの行・どの値になったかを追跡してください。自動更新の品質は、この小さな追跡ができるかどうかで大きく変わります。
図3は、6つの操作を左から右へ並べたものです。途中でエラー行を捨てず、正常データと要確認データを分岐させている点に注目してください。
式の意味を分解して理解する
画面操作だけでも一部は作れますが、再利用性や例外処理を高めるには式の意味を確認する必要があります。次の式は、このユースケースの中心となる処理例です。実際のテーブル名と列名へ置き換えてください。
Normalized = Table.SelectColumns(
Source,
{"商品コード", "数量", "単価", "金額"},
MissingField.UseNull
)UseNullは欠落を隠すためではなく、任意列の不在を明示的なnullとして扱う方法です。商品コードのような必須列には別の検査を置きます。
式を一度に完成させず、変数または適用したステップごとに中間結果を表示すると理解しやすくなります。特にnull、0、空文字、存在しないキーは別物です。画面上で同じ空欄に見えても、比較式や集計結果は変わります。
図4では、式へ渡る入力、評価条件、出力の3段階を示しています。式の文字列だけでなく、どのフィルターや行が評価対象になるかを確認してください。
検算で更新結果の正しさを確認する
更新がエラーなく完了しても、業務上正しいとは限りません。検算は元データと結果を同じ軸で比較し、件数・金額・例外の3方向から行います。まず小さなサンプルで正解を作り、その後に全件へ広げます。
検算1: 必須列の欠落ファイルが0件
必須列の欠落ファイルが0件ことを確認します。差がある場合は、差額だけを見るのではなく、どのキーの行が増減したかを反結合や重複一覧で抽出します。件数が一致していても、別の行が入れ替わっている可能性があるため、代表キーと合計値の両方を確認してください。
検算2: 任意列null件数を様式別に確認する
任意列null件数を様式別に確認することを確認します。差がある場合は、差額だけを見るのではなく、どのキーの行が増減したかを反結合や重複一覧で抽出します。件数が一致していても、別の行が入れ替わっている可能性があるため、代表キーと合計値の両方を確認してください。
検算3: 想定外の追加列を一覧化する
想定外の追加列を一覧化することを確認します。差がある場合は、差額だけを見るのではなく、どのキーの行が増減したかを反結合や重複一覧で抽出します。件数が一致していても、別の行が入れ替わっている可能性があるため、代表キーと合計値の両方を確認してください。
検算4: 旧様式と新様式で金額合計を比較する
旧様式と新様式で金額合計を比較することを確認します。差がある場合は、差額だけを見るのではなく、どのキーの行が増減したかを反結合や重複一覧で抽出します。件数が一致していても、別の行が入れ替わっている可能性があるため、代表キーと合計値の両方を確認してください。
検算結果を保存する
更新日、元ファイル名、元件数、結果件数、主要合計、エラー件数を1行で残すと、翌月の変化を比較できます。検算表も成果物の一部です。
図5は、元データ、変換結果、例外一覧を照合する検算ループです。差が0になることだけでなく、除外した行に説明可能な理由があることを確認します。
よくある失敗と修正方法
このテーマでは、処理が止まるエラーよりも、止まらずに誤った値が出る状態へ注意が必要です。次の失敗は、プレビューだけでは気づきにくいため、設計段階で確認列と例外一覧を用意します。
失敗1: すべての欠落列を無条件にnull補完する
すべての欠落列を無条件にnull補完すると、結果表は作れても意味が変わります。まず該当条件だけを抽出した確認クエリまたは確認メジャーを作り、発生件数と影響額を見ます。そのうえで、自動補正できるもの、人の判断が必要なもの、元システムで直すものへ分けます。
対処時は、異常行を単純に削除しないでください。除外理由、元キー、元ファイルを残せば、後から担当者へ確認できます。修正後は正常行だけでなく、この失敗条件が0件または想定件数になったことを再検算します。
失敗2: 列番号で参照する
列番号で参照すると、結果表は作れても意味が変わります。まず該当条件だけを抽出した確認クエリまたは確認メジャーを作り、発生件数と影響額を見ます。そのうえで、自動補正できるもの、人の判断が必要なもの、元システムで直すものへ分けます。
対処時は、異常行を単純に削除しないでください。除外理由、元キー、元ファイルを残せば、後から担当者へ確認できます。修正後は正常行だけでなく、この失敗条件が0件または想定件数になったことを再検算します。
失敗3: 列名変更を列追加と誤認する
列名変更を列追加と誤認すると、結果表は作れても意味が変わります。まず該当条件だけを抽出した確認クエリまたは確認メジャーを作り、発生件数と影響額を見ます。そのうえで、自動補正できるもの、人の判断が必要なもの、元システムで直すものへ分けます。
対処時は、異常行を単純に削除しないでください。除外理由、元キー、元ファイルを残せば、後から担当者へ確認できます。修正後は正常行だけでなく、この失敗条件が0件または想定件数になったことを再検算します。
失敗4: サンプルファイルだけ直して変換関数を確認しない
サンプルファイルだけ直して変換関数を確認しないと、結果表は作れても意味が変わります。まず該当条件だけを抽出した確認クエリまたは確認メジャーを作り、発生件数と影響額を見ます。そのうえで、自動補正できるもの、人の判断が必要なもの、元システムで直すものへ分けます。
対処時は、異常行を単純に削除しないでください。除外理由、元キー、元ファイルを残せば、後から担当者へ確認できます。修正後は正常行だけでなく、この失敗条件が0件または想定件数になったことを再検算します。
毎月更新できる運用へ仕上げる
一度だけ正しい集計を作ることと、毎月安全に更新できることは別です。担当者が変わっても、入力場所、更新順、検算、エラー時の対応が分かる状態へ整えます。
運用ポイント1: スキーマ契約をドキュメント化する
スキーマ契約をドキュメント化するようにします。ルールはクエリやメジャーの中だけへ閉じ込めず、更新手順と確認表にも同じ言葉で記載します。誰がいつ確認したかを残すと、データの訂正や再更新が必要になったときに判断しやすくなります。
運用ポイント2: 更新時に列差分レポートを出す
更新時に列差分レポートを出すようにします。ルールはクエリやメジャーの中だけへ閉じ込めず、更新手順と確認表にも同じ言葉で記載します。誰がいつ確認したかを残すと、データの訂正や再更新が必要になったときに判断しやすくなります。
運用ポイント3: 様式変更前にテストファイルで確認する
様式変更前にテストファイルで確認するようにします。ルールはクエリやメジャーの中だけへ閉じ込めず、更新手順と確認表にも同じ言葉で記載します。誰がいつ確認したかを残すと、データの訂正や再更新が必要になったときに判断しやすくなります。
運用ポイント4: 必須列欠落時は黙って0件にせず更新を止める
必須列欠落時は黙って0件にせず更新を止めるようにします。ルールはクエリやメジャーの中だけへ閉じ込めず、更新手順と確認表にも同じ言葉で記載します。誰がいつ確認したかを残すと、データの訂正や再更新が必要になったときに判断しやすくなります。
図6では、データ配置、更新、検算、例外確認、確定という運用サイクルを示しています。自動化の目的は確認をなくすことではなく、確認すべき箇所を限定することです。
導入前に確認したい適用範囲
Power Query 列が見つかりません エラーは、繰り返し発生し、入力と判断ルールを説明できる業務で特に効果を発揮します。一方、毎回まったく異なる様式を人が読み解く作業や、正解が担当者の経験だけに依存する判断は、先にルールの整理が必要です。ツールへ移す前に、誰が見ても同じ判定になる条件を言葉とサンプルで残してください。
導入判断では、旧様式: 商品コード・数量・金額を安定して取得できるか、エラーが発生する適用したステップを特定するための権限や保存場所があるかを確認します。更新頻度が低く対象が数件だけなら、仕組みの保守コストが手作業を上回る場合もあります。件数だけでなく、誤集計時の影響、監査証跡、担当者交代の頻度も判断材料です。
本番化する前に、正常例だけでなく、すべての欠落列を無条件にnull補完するケースを含むテストデータを用意します。期待結果を先に表へ書き、処理結果と一致するかを確認してください。正解表があれば、後日の式変更や元データ変更でも回帰テストを行えます。
小さく始める基準
最初の対象は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 列が見つかりません エラーを実務で使うためのまとめ
元ファイルの列追加・列欠落・列名変更で更新が止まる原因を切り分け、必要列の契約とMissingFieldオプションで変更に強いクエリへ直します。 成否を分けるのは、必須列、任意列、無視する列を分け、必須列の欠落だけを更新停止条件にするという設計を、操作前に決められるかどうかです。
実装後は、必須列の欠落ファイルが0件ことから検算を始めてください。さらに、スキーマ契約をドキュメント化することで、翌月以降の更新にも耐えやすくなります。
最初から全データへ適用せず、代表的な正常例、境界値、例外例を含む小さなサンプルで結果を確定してから全件へ広げると、修正範囲を小さくできます。
コメント