Power Query：プロのようにデータをインポート・変換する方法

毎週、CSVファイルをダウンロードして、空の行を削除し、日付の書式を整え、分析用のデータを準備するためだけに複雑な入れ子の数式を書くのに何時間も費やしているなら、必要以上の苦労をしています。Microsoft Excelに組み込まれた、最も強力なデータ自動化ツールであるPower Queryの世界へようこそ。

「データの取得と変換」とも呼ばれるPower Queryを使用すると、ほぼすべてのデータソースに接続し、情報をクレンジングして整形し、スプレッドシートに読み込むことができます。最大の魅力は何でしょうか？それは、作業手順がすべて記録されることです。次回新しいデータを受け取ったときは、手作業を繰り返す必要はなく、単に更新をクリックするだけです。

この包括的なガイドでは、Power Queryとは何か、そのインターフェースの操作方法を探り、煩雑なデータセットをクリーンで分析可能な情報に変換する実践的な例を手順を追って説明します。

Power Queryとは？

Power Queryは、データの接続と準備を行うエンジンです。データベース管理の世界では、このプロセスはETL（Extract：抽出、Transform：変換、Load：書き出し）として知られています。

• 抽出 (Extract)：さまざまなソース（Excelブック、CSVファイル、テキストファイル、Webページ、SQLデータベース、さらにはクラウドサービスなど）からデータを取得します。
• 変換 (Transform)：データをクレンジングして整形します。これには、行のフィルタリング、データ型の変更、テキストの分割、テーブルの結合、カスタム列の作成などが含まれます。
• 読み込み (Load)：整えたデータを、Excelのテーブル、ピボットテーブル、またはPower Pivotのデータモデルに直接出力します。

従来、Excelユーザーは、TRIM、PROPER、SUBSTITUTE、VLOOKUPなどの関数を組み合わせ、手作業によるコピー＆ペーストでこれらのタスクを処理していました。Power Queryは、その面倒なワークフローを、視覚的で使いやすいインターフェースに置き換えます。

Power Queryを学ぶべき理由

新しいExcelツールの学習をためらっている方へ、Power Queryをマスターすることがなぜ生産性を飛躍させるのか、その理由をご紹介します：

• コーディング不要：Power Queryは「M」と呼ばれる数式言語で実行されますが、データ変換の95%はリボンのボタンと右クリックメニューだけで実行できます。
• VBAなしでの自動化：反復作業を自動化するためにマクロの書き方を知る必要はありません。Power Queryは「適用したステップ」を記憶し、新しいデータにも自動的に適用します。
• ビッグデータの処理：Excelには物理的に1,048,576行という制限があります。Power Queryは数百万行のデータセットに接続し、バックグラウンドでデータを集計し、要約された結果のみをスプレッドシートに読み込むことができます。
• 非破壊的な編集：Power Queryが元のデータソースを変更することは決してありません。接続を作成し、データを読み取り、新しくクレンジングされたコピーを出力します。

はじめに：Power Queryエディター

Power Queryにアクセスするには、空のExcelブックを開き、リボンのデータタブに移動します。左端にあるデータの取得と変換グループを探してください。

ここから、データの取得をクリックすると、利用可能なデータソースのドロップダウンメニューが表示されます。ファイルを選択して「データの変換」をクリックすると、新しいウィンドウでPower Queryエディターが開きます。このインターフェースは主に4つの領域で構成されています：

1. リボン：上部にあり、ホーム、変換、列の追加、表示などのタブがあります。ここにはすべてのツールが揃っています。
2. クエリペイン：左側にあり、ブック内のすべてのアクティブなデータ接続がリストされます。
3. データプレビュー：中央のグリッドで、現在選択されているステップでデータがどのように見えるかをライブプレビューとして表示します。
4. クエリの設定ペイン（適用したステップ）：右側にあります。これはエディターの最も重要な部分です。変更を加えるたびに、Power Queryはそれを連続したステップとしてここに記録します。これらのステップはいつでも削除、並べ替え、編集が可能です。

ステップバイステップのチュートリアル：煩雑な売上データのクレンジング

実践的で現実的な例を見てみましょう。会社のCRMから週次の売上レポートをエクスポートしたと想像してください。エクスポートされた生のデータは乱雑で、不要なヘッダー、結合されたテキスト文字列、一貫性のない書式が含まれています。

以下は、その乱雑な生データのサンプルです：

System Export: Q3 Sales Report	Column2	Column3
Generated on: 10/01/2023
Rep_ID_Name	Order_Date	Revenue
101-John Doe	2023-08-15	1500.5
102-Jane Smith	09/01/2023	$2,340.00
103-Bob_Jones	23-Sep-2023	850.75

従来の数式を使用する場合、LEFT、RIGHT、FIND、VALUEなどを使用して担当者名を抽出し、数値を修正する必要があります。代わりにPower Queryを使用してみましょう。

ステップ1：データのインポート

乱雑なデータをCSVまたはExcelファイルとして保存します。新しいExcelブックを開き、データ > データの取得 > ファイルからに進み、ファイルを選択します。プレビューウィンドウが表示されたら、データの変換をクリックします。Power Queryエディターが開きます。

ステップ2：上位の行を削除し、ヘッダーを昇格する

データの最初の2行はシステムエクスポートのメタデータであり、実際のデータレコードではありません。これらを削除する必要があります。

• ホームタブで、行の削減 > 上位の行の削除をクリックします。「2」と入力してOKをクリックします。
• これで、実際の列ヘッダーが1行目になりました。ホームタブの1行目をヘッダーとして使用ボタンをクリックします。即座に、「Rep_ID_Name」、「Order_Date」、「Revenue」が正式な列タイトルになります。

ステップ3：担当者（Rep）列を分割する

「Rep_ID_Name」列には、ハイフンで区切られたID番号と従業員名の両方が含まれています。

• 「Rep_ID_Name」の列ヘッダーを右クリックします。
• 列の分割 > 区切り記号による分割を選択します。
• 「カスタム」を選択し、ハイフン（ - ）を入力します。OKをクリックします。
• Power Queryがデータを2つの列に分割します。新しい列ヘッダーをダブルクリックして、それぞれ「Rep_ID」と「Rep_Name」に名前を変更できます。

Bobの名前（Bob_Jones）に含まれるアンダースコアをクリーンアップするには、「Rep_Name」列を右クリックして値の置換を選択し、「検索する値」ボックスにアンダースコア（_）を入力し、「置換後の文字列」を空白のままにするか、スペースを追加します。OKをクリックします。

ステップ4：データ型を標準化する

日付と収益の形式がまったく異なっていることにお気づきでしょうか？Power Queryを使用すると、これを簡単に標準化できます。

• 「Order_Date」列ヘッダーの左側にある小さなアイコン（おそらく「ABC」または「任意」と表示されています）をクリックします。これを日付に変更します。Power Queryは自動的に異なる日付形式を統一された標準の日付に変換します。
• 「Revenue」の横にあるアイコンをクリックし、通貨（または10進数）に変更します。ドル記号が削除され、すべてがきれいにフォーマットされます。

ステップ5：条件付き列を追加する

たとえば、$1,000を超える売上を「High Value（高価値）」として分類したいとします。Excelで=IF(C2>=1000, "High Value", "Standard")のような複雑なIF関数を記述する代わりに、Power QueryのUIを使用できます。

列の追加タブに移動し、条件付き列をクリックします。ルールを設定します：[Revenue]が1000以上の場合、「High Value」を出力し、そうでない場合は「Standard」を出力する。バックグラウンドでは、Power Queryがこのステップのために次のMコードを生成します：

= Table.AddColumn(#"Changed Type", "Sales Category", each if [Revenue] >= 1000 then "High Value" else "Standard")

クエリのマージ：VLOOKUPに代わる最新の機能

データ分析で最も一般的なタスクの1つは、テーブルの結合です。各営業担当者の担当地域が含まれる別のテーブルがある場合、通常はそのデータを取り込むためにVLOOKUPの完全ガイドを参照するかもしれません。

しかし、何千ものVLOOKUPやINDEX、MATCHの数式を実行すると、ブックの動作が極端に遅くなる可能性があります。Power Queryでは、クエリのマージ機能を使用します。

単に両方のテーブルをPower Queryにインポートし、メインの売上テーブルを選択して、ホームタブのクエリのマージをクリックします。2番目のテーブル（地域のテーブル）を選択し、両方のテーブルの一致する列（例：「Rep_ID」）をクリックして、OKをクリックします。Power Queryは、行数が10行であれ1,000万行であれ、超高速なVLOOKUPに相当する処理を数秒で実行します。

データのピボット解除：Power Queryの秘密兵器

多くの場合、受け取るデータはすでにピボットのような構造（たとえば、列に1月、2月、3月、4月と並んでいるなど）にグループ化されています。これは人間にとっては読みやすいですが、グラフやピボットテーブルを作成するには不適切です。

識別子の列（「Rep_Name」など）を選択し、ヘッダーを右クリックして、その他の列のピボット解除を選択します。Power Queryは即座に、幅の広いクロス集計データを、新しい「属性」（月）列と「値」（売上）列を持つフラットなテーブルレイアウトに変換します。これを標準のExcelの数式で行うことはほぼ不可能なため、ピボット解除はPower Queryで最も高く評価されている機能の1つです。

変換したデータの読み込み

データが完全にクリーンになったら、Excelに戻すタイミングです。

ホームタブで、閉じて読み込むをクリックします。デフォルトでは、これにより変換されたデータが新しいワークシートの新しい緑色のExcelテーブルに読み込まれます。データを直接分析フェーズに送信したい場合は、ドロップダウン矢印をクリックして閉じて次に読み込む...を選択し、代わりにピボットテーブルレポートを選択できます。これらの集計の作成方法について復習が必要な場合は、初心者向けのピボットテーブル作成チュートリアルをご覧ください。

「更新」ボタンの魔法

Power Queryの真の力は、来週新しい生の売上エクスポートを受け取ったときに明らかになります。上記のステップを繰り返さないでください！

新しいCSVファイルを古いファイルに上書き保存するだけです（ファイル名とフォルダーの場所を完全に同じにします）。次に、Excelブックを開き、クリーンなデータテーブルの任意の場所を右クリックして、更新をクリックします。

Power Queryはファイルにアクセスし、行の削除、ヘッダーの昇格、列の分割、テキストの置換、条件の確認、テーブルのマージといったすべての手順を再適用し、最終的な出力をほんの一瞬で更新します。これは、Excel自動化ワークフローの重要な構成要素です。

AIを使ってデータ処理をシンプルにする

Power Queryは構造的な変換を見事に処理しますが、時には高度なExcelの数式やカスタムのMコードを必要とする特定の条件付きロジックや複雑なテキスト解析が必要になることがあります。フォーラムで答えを探し回る代わりに、人工知能を活用できます。

完璧なカスタム列の計算を書くのに苦労している場合、ExcelGPTは完璧な相棒です。「混在したテキスト文字列から数字だけを抽出する数式が必要です」など、達成しようとしていることを分かりやすい言葉で説明するだけで、ExcelGPTは即座に正しい数式やMコードを生成します。Power Queryとデータクレンジング用AIを組み合わせることで、データ分析のための無敵のツールキットを手に入れることができます。

よくある質問

Power Queryは元のデータファイルを変更しますか？

いいえ。Power Queryはソースデータへの一方向の接続を作成します。データを読み取り、メモリ内で変換を適用し、Excelで新しい結果を出力します。元のCSV、データベース、またはワークブックは完全に手つかずのまま安全に保たれます。

MacでPower Queryを使用できますか？

はい。Microsoftは、Mac版ExcelにおけるPower Queryのサポートを大幅に改善しました。従来、Mac版にはWindows版で利用できる高度なコネクタやUI機能の一部が欠けていましたが、最新バージョンのMicrosoft 365では、ローカルファイルやデータベースに接続し、既存のクエリをスムーズに更新できるようになっています。

Power Queryにおける「マージ」と「追加」の違いは何ですか？

マージ (Merge) は、VLOOKUPやINDEX / MATCHに相当します。2つのテーブル間で共通のIDを照合し、新しいデータの列を追加するために使用します。追加 (Append) は、シートの一番下にデータをコピー＆ペーストするようなものです。テーブルを上下に重ね合わせて、新しい行を追加（例：1月の売上と2月の売上を結合）するために使用します。

Power Queryの更新が失敗したのはなぜですか？

クエリの更新が失敗する最も一般的な理由は、ソースファイルが移動、名前変更、または削除されたことです。もう1つの頻繁な問題は、生データの列ヘッダーが変更されたことです（システムによって「Revenue」が「Total Revenue」に変更されたなど）。これを修正するには、Power Queryエディターを開き、「適用したステップ」ペインに移動して、「ソース」ステップを更新するか、ステップのロジック内で列の名前を変更します。