Power Queryは、毎月届く売上表や複数店舗のCSVを、同じ手順で取り込み、整形し、Excelへ読み込むための機能です。コピー、貼り付け、列削除、置換を毎回手作業で繰り返すのではなく、一度記録した変換手順を更新時に再実行できます。Microsoftは基本の流れを「接続」「変換」「結合」「読み込み」の4段階で説明しています。ETLという言葉では、Extract（取り出す）、Transform（整える）、Load（読み込む）に相当します。難しいプログラムを書かなくても画面操作から始められるため、表の形がほぼ同じ定型作業と特に相性がよい機能です。
最初に確認すること
Power Queryは現在のExcel for Windows、Mac、Webで利用できますが、使えるデータソースや編集機能は版と環境によって異なります。まずExcelの「データ」タブに「データの取得」「テーブルまたは範囲から」などの項目があるか確認してください。会社PCで項目が見つからない場合は、機能がないと決めつけず、Excelの版、更新状況、管理者ポリシーを確認します。本稿はデスクトップ版Excelを中心に説明しますが、ボタン名が少し違っても、元データへ接続し、Power Queryエディターで手順を作り、結果を読み込む考え方は共通です。
練習用の表を準備する
例として、日付、地域、商品、数量、単価の5列を持つ売上表を使います。見出しは1行にまとめ、途中の小計行、結合セル、空だけの列を入れないのが理想です。とはいえ、実務の表が完全に整っていなくても、Power Query側で不要行の削除や見出しの設定ができます。最初は原本のコピーを作り、10～30行程度の小さな表で練習してください。結果が正しいと確認できてから本番データへ接続先を切り替えると、誤変換の影響を抑えられます。
- 元表の中を選択し、Ctrl＋TでExcelテーブルに変換します。「先頭行をテーブルの見出しとして使用する」が正しいか確認します。
- 「データ」タブから「テーブルまたは範囲から」を選びます。Power Queryエディターが開き、右側に「適用したステップ」が表示されます。
- 列名、行数、先頭数行を見て、意図した範囲が取り込まれたか確認します。範囲違いに気づいたら、この時点で元表や接続元を修正します。
変換は小さな手順に分ける
エディターで行った操作は、通常「適用したステップ」へ上から順に追加されます。たとえば「先頭行を見出しとして使用」「空白行を削除」「地域列の前後の空白を除去」「数量を整数型へ変更」という具合です。元ファイルそのものを書き換えるのではなく、取得したデータに対して手順を順番に適用します。後からステップをクリックすれば、その段階の状態を確認できます。いきなり複雑な式を1つ作るより、名前の分かる小さな操作へ分ける方が、エラー箇所を見つけやすくなります。
- 見出しを短く一意にする。同じ名前の列や空の見出しを残さない。
- 日付、整数、小数、テキストなど、列のデータ型を目的に合わせる。
- 地域名や商品名は前後の空白を除き、表記ゆれを集計前にそろえる。
- 不要な列と明らかな空白行を外し、必要なら数量×単価の金額列を追加する。
- エラーを無条件に削除せず、どの値がどの変換でエラーになったかを先に調べる。
データ型は見た目ではなく意味で決める
Power Queryにはテキスト、整数、小数、日付、日時、論理値などのデータ型があります。見た目が数字でも、郵便番号、商品コード、先頭ゼロを持つ番号はテキストの方が適切です。売上日は日付、数量は整数、単価は通貨計算に合う数値型というように、後で何をする列かで決めます。型が違うままでは、日付順に並ばない、数値を合計できない、コードの先頭ゼロが消えるといった問題が起きます。
日付や小数点の解釈はロケールにも影響されます。たとえば「01/02/2026」が1月2日なのか2月1日なのかは地域設定で変わり得ます。海外データでは「ロケールを使用して型を変更」を検討し、変換後の最小値、最大値、数件の原票を照合してください。また、nullは値が存在しない状態で、空文字や数字の0とは同じではありません。集計前に、それぞれを欠損、空欄、実績ゼロのどれとして扱うか決めておきます。
追加とマージを使い分ける
複数データの結合には主に「追加」と「マージ」があります。追加は、東京店と大阪店の売上表のように、同じ列構成の表を縦に積み重ねる操作です。マージは、売上表の商品コードに商品マスターの商品名や分類を付けるように、共通キーで列を横に結び付ける操作です。似た言葉ですが目的が違います。マージではキーの重複や空欄があると行数が増減することがあるため、結合前後の行数と未一致件数を必ず確認します。
同じフォルダーに毎月のCSVを置き、フォルダー接続でまとめる方法もあります。ただし、途中から列名やファイル形式が変わると更新に失敗します。まず2～3ファイルで列構成、文字コード、日付形式が一致するかを確認し、バックアップファイルや説明用ファイルを同じ取込フォルダーに混ぜない運用を決めてください。
読み込み先を選ぶ
編集が終わったら「閉じて読み込む」または「閉じて次に読み込む」を使います。通常の表として確認したい場合はワークシートへ読み込みます。大きなデータをピボットテーブルや複数表の関係で分析する場合は、接続のみの作成やデータモデルへの追加が候補です。最初の練習では新しいワークシートへ表として読み込み、元表と件数、数量合計、金額合計を比較するのが分かりやすい方法です。
読み込まれた結果表を直接修正しても、次の更新で置き換わる可能性があります。恒久的な修正は、元データを直すか、Power Queryの変換ステップとして追加します。出力表へ手入力列を足したい場合も、更新でずれない設計かを先に検証してください。入力、変換、出力を別シートや別テーブルとして区別すると、どこを直すべきか判断しやすくなります。
更新で同じ手順を再実行する
翌月のデータへ差し替えたら、クエリを更新すると、保存した接続と変換が再実行されます。これがPower Queryの中心的な利点です。ただし「更新が完了した」ことと「数字が正しい」ことは同義ではありません。ファイルが1件増えたのに行数が変わらない、列名変更で値がnullになった、型変換エラーが増えた、といった変化を検知する確認欄を用意します。更新日時、取込ファイル数、行数、金額合計、エラー件数を記録すると異常を見つけやすくなります。
初心者がつまずきやすい点
- 元ファイルの保存場所や名前を変え、接続先が見つからなくなる。移動する場合はデータソース設定も見直す。
- 列名を毎月変え、前月に作ったステップが対象列を見つけられない。入力テンプレートを固定する。
- 先頭数行だけを見て型を決め、後半の文字列や海外日付でエラーになる。列品質とエラー行を確認する。
- 結果表を手直しし、更新後に修正が消える。修正場所を元データか変換ステップへ移す。
- マージ後の行数増加を見落とす。結合キーが一意か、未一致と複数一致がないかを検査する。
- 更新を第三者へ引き継げない。クエリ名、ステップ名、入力場所、検算方法を記録する。
処理を安定させる基本
Microsoftのベストプラクティスでは、適切なコネクターを選び、必要なデータだけに早めに絞り、並べ替えなど負荷の高い操作を後に回す考え方が示されています。データベース接続では、接続先が一部の変換を処理できる「クエリフォールディング」が働く場合もありますが、すべての接続先と操作で保証されるものではありません。まず不要列と不要行を減らし、処理時間と結果を測定してから複雑なステップを追加します。
クエリには「売上CSV取込」「商品マスター整形」のように役割が分かる名前を付けます。既定の「クエリ1」や自動生成されたステップ名のまま増やすと、数か月後の修正が難しくなります。処理のまとまりごとに説明を残し、ステップを削除したとき後続処理へ影響しないか確認してください。参照用の中間クエリは接続のみとし、必要な最終結果だけをシートへ読み込むとブックを整理しやすくなります。
数式、ピボット、VBA、Power Pivotとの違い
Power Queryは主に取得と整形を担当します。セルごとの計算や利用者が値を入力する表は通常のExcel数式、集計の切り口を対話的に変えるならピボットテーブルが向いています。Power Pivotは、取り込んだ複数表の関係を作り、メジャーなどで分析モデルを構築する役割です。VBAは画面操作やブック全体の自動化にも使えますが、Power Queryで標準化できる取得・整形だけなら、まずPower Queryを検討すると手順を追跡しやすくなります。どれか1つに統一するのではなく、Power Queryで整えてからピボットやPower Pivotで分析する組み合わせが実用的です。
資格情報と共有時の注意
Web、データベース、SharePointなどへ接続すると資格情報やプライバシーレベルの設定が必要になる場合があります。共有ブックのセル、クエリ名、MコードへパスワードやAPIキーを直接書き込まないでください。読み取りだけで足りる接続には読み取り権限を使い、退職者個人のアカウントだけに依存しない運用を決めます。外部へブックを渡す前には、接続先名、ファイルパス、サンプルデータ、クエリ結果に個人情報や社内情報が残っていないか確認します。
完成と判断するチェックリスト
- 元データの件数とPower Query取込後の件数を比較し、増減理由を説明できる。
- 日付、数量、金額、コードの型が目的に合い、変換エラーが0件または理由を把握している。
- 数量合計や金額合計を元資料と照合し、差額がある場合は原因を記録している。
- 翌月相当の別ファイルへ差し替え、更新だけで同じ結果を再現できる。
- クエリ名、入力場所、更新方法、検算項目を別の担当者が読める形で残している。
- 元ファイルと完成ブックのバックアップがあり、機密情報と接続権限を確認している。
最初の目標は、大規模な自動化ではなく「手作業で行っていた5つの整形を、更新可能な5つのステップへ置き換える」ことです。小さな表で接続、型設定、不要行削除、読み込み、更新までを一巡し、件数と合計を検算してください。その一巡が安定したら、追加で複数月を縦に結合し、次にマスターとのマージへ進みます。工程を分けて確認すれば、Power Queryを便利な一発処理ではなく、再現可能なデータ準備手順として運用できます。
公式情報・参考資料
コメント