【Excel】Power Queryで経費データの列名が月ごとに変わる時の設計方法

経費データをPower Queryで取り込む際、月を跨ぐたびに列名が「4月」「5月」と変わってしまい、クエリがエラーになる悩みは多くの会社員が経験します。固定の列名を前提にしたクエリは、新しい月のデータが来るたびに修正が必要になり、業務効率を大きく損ねます。本記事では、列名が動的に変わる経費データでも自動で正常に読み込めるPower Queryの設計方法を解説します。原因の切り分け方、具体的な手順、よくある失敗パターンとその対策を網羅し、管理部門やIT担当者にも伝えるべきポイントをまとめました。

なぜ月ごとに列名が変わるとPower Queryがエラーになるのか

原因:クエリが固定列名に依存している

Power Query(別名:Power Query Editor)は、既定では取り込み時にテーブルの列名をそのまま保持します。しかし、その後のステップで「列の削除」「列の名前変更」「列の展開」などを行うと、指定した列名がクエリ内に固定値として記録されます。たとえば「列の削除」で「4月」という列を指定した場合、次月のデータに「5月」という列が含まれていても、その列は削除対象とならず、逆に「4月」列が存在しないためエラーが発生します。

経費データの典型的なパターンとして、年月ごとに列名が「4月」「5月」と変化するほか、「April」「May」といった英語表記や「2024/04」「2024/05」のように変化する場合もあります。いずれも固定列名を前提としたクエリは破綻します。

影響:手作業の修正が月次で発生する

クエリが壊れるたびに、担当者がクエリエディタを開いて「適用したステップ」を一つずつ確認し、新しい列名に合わせて修正する作業が発生します。経費データが複数の部門やプロジェクトから送られてくる場合、修正漏れや人為的ミスがリリース業務の遅延につながります。また、Power Queryのクエリを共有している場合、他のユーザーが誤った修正を加えるリスクもあります。

動的列名に対応する設計の基本戦略

戦略1:列名を取得してから処理する

Power QueryのM言語では「Table.ColumnNames」関数を使うと、テーブルからすべての列名をリストとして取得できます。このリストを基に、特定のパターンに一致する列だけを選択したり、列名を変更したりできます。たとえば、列名が「◯月」の形式なら、Text.Contains関数で「月」を含む列を抽出し、Unpivot(列のピボット解除)に利用します。

戦略2:パラメータクエリで月を外部から指定する

別のセルやテーブルに当月の月名を用意し、その値をパラメータとしてクエリに渡す方法です。パラメータクエリを使えば、列名を直接指定せずに、動的に該当する列を参照できます。ただし、経費データのように複数月の列が同時に存在するケースでは、全列を扱うためにパラメータを複数用意する必要があり、かえって複雑になりやすいため注意が必要です。

戦略3:リスト操作とUnpivotで縦持ちに変換する

最も汎用的な方法は、月別の列をUnpivot(ピボット解除)して「月」と「金額」の2列に変換することです。元のテーブルに「費目」「部門」などの固定列が存在する場合、それ以外の可変列を動的に選択してUnpivotします。これにより、列名が変わってもデータ構造が安定するため、後続の分析やレポート作成が容易になります。

手法	メリット	デメリット	適したケース
固定列名クエリ(修正あり)	単純、習得が容易	毎月手修正が必要、エラーリスク大	データ更新が月1回だけ、列名が変わらないことが確定している場合
パラメータクエリ	外部から月を指定できる	複数月の列には対応が複雑	単一月のデータのみを扱う場合
動的リスト+Unpivot	列名が変わっても自動対応、縦持ちで安定	M言語の知識が必要、処理がやや重い	複数月の列が混在する経費データ全般

具体的な設計手順:動的Unpivotの実装

ここからは、実際にPower Queryエディタで動的Unpivotを構築する手順を説明します。経費データのテーブルが「費目」「部門」「4月」「5月」「6月」のような構成になっていると仮定します。固定列は「費目」と「部門」で、それ以外の月別列が可変です。

1. データを読み込む: Power Queryエディタを開き、経費データが格納されたテーブルまたは範囲を選択して「テーブルまたは範囲から」で読み込みます。
2. 列名のパターンを確認する: 月別列が「4月」のように数字+「月」の形式かを確認します。不規則な場合は別途ルールを定義する必要があります。
3. 固定列のリストを作成する: 「カスタム列」から「= {“費目”,”部門”}」など、固定列名をリストとして保持します。これは後で可変列を特定するために使います。
4. 全列名から固定列を除外する: 「カスタム列」を使い、M関数「Table.ColumnNames」で全列名を取得し、「List.RemoveItems」で固定列リストを除去します。これにより動的な可変列名リストが得られます。
5. Unpivotを実行する: 可変列名リストを元に、動的にUnpivotOtherColumns関数を呼び出します。具体的なMコードは次のとおりです(例として、クエリの名前を「経費データ」とします)。

Mコード例(カスタムステップとして追加):

let
    Source = Excel.CurrentWorkbook(){[Name="経費データ"]}[Content],
    FixedColumns = {"費目", "部門"},
    AllColumns = Table.ColumnNames(Source),
    DynamicColumns = List.RemoveItems(AllColumns, FixedColumns),
    Unpivoted = Table.UnpivotOtherColumns(Source, FixedColumns, "月", "金額")
in
    Unpivoted

このコードでは、固定列を除くすべての列を「月」と「金額」にUnpivotしています。列名が「4月」「5月」から「April」「May」に変わっても、固定列以外は自動的にUnpivot対象となるため、クエリが壊れません。

注意:UnpivotOtherColumnsとUnpivotの違い

標準のUnpivotはピボット解除する列を明示的に指定する必要がありますが、UnpivotOtherColumnsは「ピボット解除しない列」を指定することで、残りを自動的に解除します。動的列名に対応するには、こちらの方が便利です。

よくある失敗パターンと対策

失敗1:”列が見つかりません”エラー

クエリのステップで固定列名を指定(例:「列の削除」で「4月」を指定)していると、次月にその列が存在しないためにエラーが発生します。対策は、前述の動的Unpivotのように、列名を直接指定せずにリスト操作で処理することです。

失敗2:Unpivot後の列名が意図した月の値にならない

UnpivotOtherColumnsを使うと、「月」列には元の列名がそのまま入ります。たとえば「4月」という列名がそのまま値として格納されるため、後続の処理(日付変換など)で支障が出る場合があります。対策として、Unpivot後に「月」列の値を置換して日付型に変換するステップを追加します。たとえば「4月」→「2024/04/01」のように変換ルールを定義します。

失敗3:可変列の中に固定列として扱いたい列が混在する

経費データに「予算コード」のような固定列が追加された場合、FixedColumnsリストにその列名を追加し忘れると、その列もUnpivotされてしまいます。対策は、FixedColumnsをテーブルから動的に取得するか、パラメータとして外部管理することです。たとえば、元データの先頭行に「固定列」とマークを付けておき、その情報を読み取る方法も考えられます。

管理者に確認すべきこと・伝えるべきこと

経費データの列名が月ごとに変わる問題は、Power Query側で吸収できるとはいえ、根本的にはデータソースの設計改善も検討すべきです。以下の点を管理者(ファイルサーバー管理者や経理システム管理者)に確認・提案してください。

• 列名を統一するルールを導入できないか: たとえば「月1」「月2」のような固定の列名を使い、別途月の対応表を用意する方法です。これによりPower Query側の処理が単純化されます。
• データを縦持ち形式で出力してもらう: 元データがすでに「月」「金額」の行形式であれば、列名問題は発生しません。システムの出力設定を変更できるか打診します。
• Power Queryのクエリを共有する際の注意点: 動的Unpivotを含むクエリは、他のユーザーが編集すると壊れる可能性があります。クエリを保護するか、読み取り専用で配置するなどの運用ルールを決めてください。

よくある質問(FAQ)

Q1. 列名が「4月」「April」と混在する場合はどうすればいいですか?

A. その場合、Text.Containsを使ったパターンマッチングでは対応が難しいため、すべての列名を一度リストアップし、固定列リストと突き合わせて可変列を抽出するロジックに変更します。固定列リストを「費目」「部門」「備考」のように明示的に持つことで、文字列パターンに依存しない処理が可能です。

Q2. 過去の月のデータが削除されると、クエリがエラーになりませんか?

A. 動的Unpivotの場合は、削除された列が単に存在しなくなるだけなので、エラーにはなりません。ただし、固定列リストに削除された列が含まれていると問題になる場合があるため、固定列リストは元データの構造変更に合わせて更新する必要があります。

Q3. 処理が遅いと感じます。高速化する方法はありますか?

A. 動的Unpivotは全列をスキャンするため、列数が多いと処理負荷が上がります。高速化のコツは、データを読み込む前に不要な列をドロップすることです。ただし、可変列を事前に絞ると動的処理の意味が薄れるため、バランスを考慮してください。必要に応じて、列名リストをキャッシュするなど、Mコードの最適化も検討します。

Q4. この方法はPower BIにも応用できますか?

A. はい、Power BIのPower Queryエディタでもまったく同じMコードが使えます。Power BIの場合は、さらに「クエリのパラメーター化」機能を使うと、動的列名の制御がより柔軟になります。

まとめ

月ごとに列名が変わる経費データに対しては、固定列名を追跡するのではなく、Unpivotを動的に適用する設計が効果的です。この方法により、毎月のクエリ修正作業から解放され、データ更新の自動化が実現します。実装にはM言語の基本的な理解が必要ですが、一度作ってしまえば長期的なメンテナンスコストが大幅に削減されます。まずはサンプルデータで動作検証を行い、関係者と共有してから本番運用に移行することをお勧めします。管理者と相談の上、可能であれば元データの構造自体を見直すことも検討してください。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】