Power Query 基本の4ステップを解説します

みなさんは、Excelに搭載されているPower Queryという機能をご存じでしょうか。

• 名前は聞いたことあるけど使ったことがない。
• そもそも名前すら聞いたことがない。

そういう方は、ぜひとも最後まで読んでいってください。
※この記事では基本的な内容を解説していますので、既に使いこなしている方には物足りない内容になっているかと思います。

Power Queryは、データの読み込みや加工を簡単に行うことができる機能です。
複数ファイルに分かれているデータをひとつのファイルに集約したり、また、元データに対して計算した結果を追加したりといったことができます。

従来、複数のファイルをひとつに集計しようとすると、ひとつひとつのファイルを手動で開いてコピー＆ペーストを繰り返したり、あるいは、自動化するためにVBAのプログラムを書く必要がありました。

しかし、Power Queryを使用することで、煩雑な手順を踏まなくても、半自動的にデータの集計ができるようになりました。

この記事では、Power Queryを使用したデータの集計や加工の流れを、順を追って解説していきます。
Power Queryにはいろいろな機能があるため、すべてを理解するのは難しいですが、この記事で解説する基本的な部分を抑えるだけでも、データ集計や加工の作業をかなり効率化できると思います。

作業イメージ

この記事では、具体的な作業を進めながら、Power Query の使い方を解説します。
まずは、事前に準備するものと完成イメージを確認しましょう。

準備するもの①：集計対象のファイル

今回の手順では、以下の３つの売上表を集計します。

4月～6月の売上表が、各月ごと1ファイルずつに分かれて格納されています。
また、それぞれのファイルは、以下のようなレイアウトになっています。

「日付」「商品コード」「数量」の３つの列がある表形式のデータです。
表の部分は、テーブルとして書式設定されています。
※”テーブルとして書式設定”の方法については、「補足｜テーブルとして書式設定」を参照してください。

準備するもの②：商品マスタ表

売上表にある商品コードについて、商品名と単価を表形式にまとめたものを、商品マスタとして準備します。

こちらも、売上表と同様に、テーブルとして書式設定をしてあります。

完成イメージ

最終的な完成イメージは以下のようになります。

4月から6月までの売上表がひとつにまとめられています。
また、商品マスタにあった「商品名」と「単価」の列が追加されており、単価×数量で求めた「金額」列も追加されています。

補足：テーブルとして書式設定とは？

テーブルとして書式設定は、「ホーム」タブにあるスタイルを設定する方法のひとつです。

クリックすると、いろいろなスタイルが表示され、その中からひとつ選ぶことで、書式設定をすることができます。

ここまでだと、書式を設定できるだけの機能のように思えるかもしれませんが、「テーブルとして書式設定」にはもうひとつの役割があります。
それは、指定した範囲がひとつのテーブルである、とExcelに認識させることです。

Excelにテーブルと認識させるメリットは、テーブルを活用した機能を使えるようになることです。

テーブルをインプットにピボットテーブルを作成したり、後述のようにPower Queryでデータソースとして扱ったり、あるいは、関数の中でテーブル名で参照したりといったように、Excelにはテーブルを活用した機能があります。
テーブルとして書式設定をすることで、それらの機能を有効に活用できるようになるのです。

Power Query 基本の4ステップ

では、実際の手順の解説に入っていきます。

Power Queryの作業は以下の4ステップで進めていきます。

ステップ1｜データソースの指定
ステップ2｜ファイル結合
ステップ3｜データ変換
ステップ4｜読み込み

それぞれのステップについて詳しく見ていきましょう。

ステップ1｜データソースの指定

最初に行うのは、データソースの指定です。
これは、集計するデータが入っているファイルやフォルダーを指定することです。

では、具体的な方法を見ていきましょう。

①「データ」タブ→「データの取得」→「ファイルから(F)」→「フォルダーから(F)」を選択します。

②表示されたウィンドウで、集計対象のファイルが格納されたフォルダーを選択し、「開く(O)」ボタンを押下します。

これで、売上表フォルダーの中にある3つのファイル（4月売上表.xlsx、5月売上表.xlsx、6月売上表.xlsx）をデータソースとして指定することができました。

①のウィンドウを見ていただくとわかる通り、データソースとして指定できるのは、フォルダーだけではありません。
個別のExcelブックやテキストファイル、CSVファイルなども指定することができます。
また、AccessやSQL Serverなど、一部のデータベースシステムなどを指定することも可能です。

本記事ではフォルダーを指定する方法を取り上げていますが、Power Queryには他にもいろいろな使い方がありますので、応用範囲の広い機能であるということが言えると思います。

ステップ2｜ファイル結合

続いて、ファイル結合を行います。

これは、先ほど指定したフォルダーに格納された3つのファイルを、ひとつのデータにまとめる作業です。
複数のファイルを取り込む場合に必要な作業になりますので、個別のファイル（ExcelブックやCSVファイルなど）を取り込む場合にはファイル結合は必要ありません。

具体的な手順を見ていきましょう。

①「結合 ▼」ボタンを押下し、「データの結合と変換」を選択します。

②「File の結合」ウィンドウが表示されたら、左の表示オプションの中から「売上表」を選択し、「OK」ボタンを押下します。

これで、ファイルの結合は完了です。

上の手順で選択した「売上表」は、事前準備のときにテーブルとして書式設定をしたことで選択できるようになったものです。
テーブルとして書式設定をすることによって、Excelが、表というひとつのオブジェクトとして認識しています。
それによって、「File の結合」ウィンドウで選択肢のひとつとして表示されているのです。

ステップ3｜データ変換

次に、データ変換を行います。

「File の結合」ウィンドウで「OK」ボタンを押下すると、以下のような画面が表示されます。
これは「Power Query エディター」と呼ばれるもので、Power Queryに関する操作は基本的にこのウィンドウで行われます。

それでは、実際に「Power Query エディター」を使用して作業していきましょう。

画面中央に、取り込んだデータのプレビューが表示されていますが、元のExcelファイルの内容とは異なる部分が2箇所あります。

• 商品コードの冒頭の「00」が消えている。
• 余計な「Source.Name」列が付与されている。

まずは、この2点を解消していきます。

型の変換

商品コードの冒頭「00」が消えているのは、商品コードが数値型で扱われているためです。
列名の「商品コード」の左側に「123」と表示されていますが、これが数値型で扱われていることを表しています。
冒頭の「00」を維持するには、数値ではなく文字列として扱う必要があるので、テキスト型に変換していきます。

①型を変換するには、商品コード列の列名部分を右クリックし、「型の変換」からどの型に変換するのかを選択します。

②テキスト型を選択すると、「列タイプの変更」ウィンドウが表示されます。ここで「現在のものを置換」をクリックします。

これで、型の変換は完了です。

列の削除

次に「Source.Name」列を削除しましょう。

①「Source.Name」列の見出しを右クリックし、「削除」を選択します。

これで完了です。

別の表とマージする。

次は、商品コードに紐づく商品名と単価の列を追加しましょう。

商品コードごとの商品名と単価は、商品マスタとして表にまとめてありました。（※事前準備参照）
この商品マスタをPower Queryを使用して取り込み、売上表と紐づけていきます。

①「ホーム」タブ → 「新しいソース」 → 「ファイル」 → 「Excelブック」を選択します。

②商品マスタのファイルを選択し、「インポート(M)」を押下します。

③「ナビゲーター」ウィンドウで、商品マスタ表を選択します。※事前準備で、「テーブルとして書式設定」で作成した表オブジェクトです。

これで、表の取り込みができました。

商品コードが数値型になっているので、テキスト型に変換しました。

次に、売上表と商品マスタをマージします。商品コードという共通の項目を利用して、売上表に対し、対応する商品マスタの情報を付与していきます。

①「ホーム」タブ → 「クエリのマージ」 → 「新規としてクエリをマージ」を選択します。

表示された「マージ」ウィンドウでは、上の段で売上表が選択され、下の段が空白になっています。

②下の段のプルダウンを表示し、商品マスタを選択します。

③下の段に商品マスタが表示されたら、上下の段それぞれの商品コードをクリックします。

これで、商品コードを使用して情報を関連付けることを指定できました。

④「OK」ボタンを押下します。

数量列の右側に、商品マスタの列が追加されました。

この時点では、商品マスタの情報はTableとして一列にまとめられています。
なので、これを商品名・単価など個別の情報に分けていきましょう。

⑤商品マスタ列の右側にあるボタンを押下します。展開する列を指定する欄が表示されますので、商品名と単価のみにチェックがついた状態にします。
「元の列名をプレフィックスとして使用します」のチェックを外し、「OK」ボタンを押下します。

これで、商品名と単価の列を追加することができました。

列の追加

最後に、単価×数量で計算した金額の列を追加しましょう。

①「列の追加」タブ→「カスタム列」を選択します。

②「新しい列名」欄と「カスタム式の列」欄を入力し、「OK」ボタンを押下します。
※「カスタム式の列」欄の「単価」と「数量」は、右側の「使用できる列」欄の該当する箇所を選択してから「<<挿入」ボタンを押すと入力できます。

金額列を追加することができました。列名を右クリックして、「整数」型に変更しましょう。

これで、データ変換が完了しました。

ステップ4｜読み込み

最後に、読み込みを行います。

読み込みとは、Power Queryエディターで作成したクエリを実行し、結果をワークシートに反映することです。

①「ホーム」タブ→「閉じて読み込む」→「閉じて次に読み込む...」を選択します。

②「データのインポート」ウィンドウが表示されるので、表示形式とインポート先を選択し、「OK」ボタンを押下します。
※今回は、表示形式を「テーブル(T)」、インポート先を「新規ワークシート(N)」とします。

これで、一連の作業が完了です。

データを更新する方法

Power Query で作成された表は、元のデータに対する変更を簡単に反映できます。

例として、「商品コード001：鉛筆」の単価を60円→70円に変更してみましょう。

元データの変更を反映するには、表の中のセルを右クリックし、「更新」を選択します。

これで、変更が反映されました。

おわりに

Power Query の基本的な使い方を解説しました。

これまで、複雑な関数やマクロ・VBAを使わないとできなかった作業が、Power Queryを使用することで簡単にできるようになりました。

定型的なデータの集計や加工作業については、Power Queryを使いこなせるかどうかで作業効率が大きく変わってきます。

ぜひ、活用してみてください。