パワー クエリで業務改善！データ集計を自動化する5つのステップ

業務で毎回手動で集計やレポートを作るのに時間を取られた経験はありませんか？
「パワークエリ（Power Query）」を使えば、ExcelやPower BIといった身近なツールだけで、データ取得・整形・集計・可視化までを一つのプロセスにまとめることができます。
今回は、パワークエリを使って業務改善・データ集計を自動化するための 5つのステップ を詳しく掘り下げます。

ポイント
・「データソースは何が多いか？」と「集計すべき指標は何か」を最初に明確にすること。
・パワークエリの「変換」タブにある関数を覚えると、作業時間が大幅に短くなる。
・テスト・バージョン管理を徹底して、エラーが発生してもすぐに戻れる環境を作る。

1. データソースの洗い出し：接続とインポートをスムーズに

1‑1. 何がデータを持っているかを整理

  • 社内システム（Salesforce、SAP、Oracleなど）
  • クラウドサービス（Google Analytics、Azure、AWS S3）
  • ファイル（CSV、Excel、JSON、XML）
  • Web API（RESTful、GraphQL）

1‑2. パワークエリで接続する

  1. 取得 タブ → 「取得と変換」→「データ取得」
  2. 目的のソースを選択し、認証情報を入力
  3. 「プレビュー」画面で必要なテーブルやシートを選択

ヒント
① **「接続のみ作成」**を選ぶと、メモリにデータを読み込まずに次のクエリに渡せます。
② **「パラメータ」**を使って環境ごとに接続先を切り替えると、開発・本番で同じコードが再利用できます。

1‑3. 取得したデータの初期検証

  • レイアウト：列名・データ型の確認
  • 欠損：NULL 値の有無
  • 統一：文字コード、日付形式の統一

初期検証を済ませると、後のステップで「型変換」「欠損補完」などの処理がスムーズになります。

2. データの整形：一貫性を保ちつつクレンジング

2‑1. 列の名前・型を統一

  • 「列名の変更」→「列の型を変換」
  • 標準化：大文字小文字の統一、日付フォーマット統一（yyyy/mm/dd）

2‑2. 必要な列だけを残す

  • 「列の削除」→「列の選択」
  • データ量削減：不要な列を減らすことで、再処理速度を向上

2‑3. 欠損値の処理

  • 「値の置換」
    • 例：NULL → 0 で数値集計を安定化
  • 「行の削除」
    • 例：全列がNULLの行は除外

2‑4. 文字列操作でエラーを削減

  • 「文字列操作」
    • 例：余分なスペース除去 TRIM、特定文字列を置換 REPLACE
  • 正規表現
    • 例：メールアドレス形式チェック Regex.Replace

2‑5. 日付・時刻の整形

  • 「日付時刻」タブ
    • Date.FromText、 TimeZone.ToLocal でタイムゾーン調整
  • 時系列分析
    • Year, Month, Day の派生列作成

実践例

// 文字列を日付に変換して欠損時に一律日付へ
#"変換日付" = Table.TransformColumns(#"先頭行",{{"受注日", each
    try Date.FromText(_) otherwise #date(1900,1,1)}})

3. データ統合：複数テーブルを一元化

3‑1. 結合（Join） で関係性を構築

  • 「結合の実行」→「左外部結合」「内部結合」など
  • キー列は必ず型統一（文字列 vs 数字）のチェック
  • 大量データ時は 「結合のメモリ消費」 を意識し、事前にサブセットでテスト

3‑2. アンピボット・ピボット で形状を変える

  • 「ピボット列」→「アンピボット列」
  • KPI（売上、数量）を列方向に統一
  • 集計関数（SUM, COUNT, MAX など）を指定

3‑3. 結合の際の衝突対策

  • 同名列が複数ある場合 Table.Clean で余計な空白を削除
  • 列名の修正：Table.RenameColumns で一意に

3‑4. データのキャッシュ

  • 大規模な結合時は 「フェッチ数の削減」
  • Table.Buffer でキャッシュし、後続処理の速さを確保

実践例

// 発注テーブルと出荷テーブルを内部結合し、商品別の売上を算出
let
  注文 = Excel.CurrentWorkbook(){[Name="注文書"]}[Content],
  出荷 = Excel.CurrentWorkbook(){[Name="出荷レポート"]}[Content],
  結合 = Table.Join(注文, "OrderID", 出荷, "OrderID", JoinKind.Inner),
  ピボット = Table.Pivot(Table.TransformColumns(結合,{{"Quantity", each _, type number}}), List.Distinct(結合[Product]), "Quantity", List.Sum)
in
  ピボット

4. 集計と指標化：分析に直結するテーブルを作成

4‑1. グループ化（Group By）

  • 「グループ化」タブ → 「列でグループ化」
  • 集計関数：Sum, Count, Average, Min, Max
  • 複数列で集計：Group Byで同時に複数列をキーにし、関数をカスタム

4‑2. カスタム列で新たな指標を算出

  • Calculated Column
  • 例：利益 = 売上 – コスト
  • 例：売上高に対する返品率 → ([返品数]/[売上数])*100

4‑3. フィルタリング

  • 「高級フィルタ」
  • 時系列限定（1年前のデータのみ抽出）
  • 条件式：Date.IsInPreviousNMonths([受注日], 12)

4‑4. 階層的分析

  • レベル を作成し、Pivot で階層表示
  • 例：地域 → 市区町村 → 店舗別売上

4‑5. データの可視化準備

  • 列幅調節：数値列を固定小数点、日付列は統一フォーマット
  • 列名の説明：Table.AddColumn で説明カラムを付与

実践例

// 日付ごとの平均売上と最大売上日を求める
let
  グループ = Table.Group(
      #"先頭行",
      {"受注日"},
      {{"平均売上", each List.Average([売上]), type number},
       {"最大売上日", each List.Max([売上]), type number}}
  )
in
  グループ

5. 自動化・運用：定期更新と保守性を確保

5‑1. クエリの名前付けと説明付与

  • クエリエディタ上で「名前を変更」し、意味が伝わるように
  • 「説明」フィールドに作業内容や使用した変数を記入

5‑2. ステップごとのコメント

  • M言語は // でコメント可能
  • 何をしているかを必ずコードに書き込むと、次回メンテが楽です。

5‑3. パラメータ管理

  • 「ホーム」タブ → 「パラメータ」
  • 環境変数（DB接続文字列、フォルダパス、対象年月）を一元管理
  • ソースファイルの再利用性が向上

5‑4. スケジュール実行

  • Power Query は Power BI Service で「スケジュールリフレッシュ」設定
  • Excel Online でも Office スクリプトと連携し、定期実行が可能
  • 実行結果の 通知設定（失敗時にメール送信）

5‑5. 監査ログとバージョン管理

  • Workbook Connections には エラー履歴 が残る
  • Git で *.xlsx のバージョン管理（大きいファイルは git-lfs）
  • クエリの「エクスポート」→「HTML」レポートで実装変更を可視化

実践例

// ユーザーパラメータを使用した接続文字列
let
  パラメータ = Excel.CurrentWorkbook(){[Name="Params"]}[Content],
  サーバー = パラメータ{0}[Server],
  DB = パラメータ{0}[Database],
  ソース = Sql.Database(サーバー, DB)
in
  ソース

まとめ

  1. データソースを網羅的に把握し、パワークエリで接続
  2. 整形（型統一・欠損補完・文字列処理）を徹底
  3. 結合・ピボットで1つの分析テーブルへ統合
  4. 集計でビジネスに直結する指標を算出
  5. 自動化（パラメータ・スケジュール）で定期実行と保守を確立

これらのステップを順守すれば、従来数時間から数日かかっていたデータ集計作業を、数分で完了させることが可能です。
パワークエリは「好きなデータを好きな形に加工する」力を持つので、まずは小さな自動化から始め、徐々にプロセスを拡張してみてください。

成功の鍵は「設計思考」と「コードの可読性」。
データを扱う人全員がコードを理解できれば、変更や追加に対しても柔軟に対応できます。

さあ、今日からパワークエリで業務を自動化し、時間を本当に価値ある仕事に回しましょう！

コメント

タイトルとURLをコピーしました