Power Query の「グループ化」の基本

Power Query でご質問いただいた件があり、それをやるためにはグループ化がわからないといけなくて。ということで、いずれもう少し高度なことをやるための手順は書きますが、一旦、Power Query のグループ化について書きます。

Power Query の［グループ化］は、指定した列の値が同じ行をまとめて、ほかの列の値を集計する機能です。ここまでは Access の集計クエリ (Group by) といっしょ。Power Query のグループ化は、グループ化した後、集計はせずに元の行をそのまま保持したミニテーブルを作ることもできます。

たとえば、下図の [研修実施履歴] テーブルには、いつ、どんな研修が実施され、だれが担当して、何人の参加者がいたのかがまとめられています。

ピボットテーブルを使って、各研修に何人参加したのかを集計するとこんな感じになります。これは、[研修] 列に同じ値が入っている行をまとめて、そのあと [人数] 列を集計 (合計) しています。

そして、各研修が何回実施されたのかを求めるとこんな感じになる。

これをワークシート上でピボットテーブル (または SUMIF や COUNTIF を使った数式) で集計するのではなく、Power Query での加工の過程で行って、集計された結果をワークシートに読み込んだり、Power BI のデータモデルとしてインポートしたりするときに、グループ化の機能を使います。

今回は Excel でテーブルを選択し、リボンの [データ] タブの [データの取得と変換] グループの [テーブルまたは範囲から] をクリックして Power Query エディターを起動し、何パターンか、基本的なグループ化をご紹介します。

同じ研修が何回実施されたか / 何人参加したかを集計する

1. Power Query エディターでクエリを選択し、[研修] 列 (キーになる列) を選択して、リボンの [ホーム] タブの [変換] グループの [グループ化] をクリックします。

2. [グループ化] ウィンドウが表示され、[基本] が選択されていて、[研修] 列が指定されます。

[新しい列名] に集計結果 (実施回数) を表示する列の名前を入力し、[行数のカウント] を選択して [OK] をクリックします。

3. グループ化が行われ、行数のカウント結果が表示されます。 (Excel 1 は 3 回実施されているので合っていることがわかる)

4. たとえば、[操作] で「合計」を選択して、[列] で人数を選択して [OK] をクリックすると、各研修の参加人数の合計を算出できます。(Excel 1 は、20＋10+10 で 40 なので合っている)

5. 研修ごとの担当ごとの実施回数を求めたいのなら、[研修] 列と [担当] 列を両方選択してから [グループ化] をクリックします。

6. [グループ化] ウィンドウが表示され、[詳細設定] が選択された状態になり、2 つの列がグループ化の対象として指定されます。

[新しい列名] に列名を入力して、[操作] で「行数のカウント」を選択して [OK] すると、担当ごとの研修の実施回数が表示されます。(織田さんは Excel 1 を 2 回実施しているので合っている)

まずはここまでの内容を理解して、イメージできれば基本は OK。

グループ化した行が含まれるミニテーブルを作る

冒頭でも書いたとおり、Power Query のグループ化では、指定した列の値が同じ行をまとめたあと、集計はせずに元の行を保持したミニテーブルを作ることができます。これができると、グループの中から条件に合致する行を抜き出すという次のステップに進めます。(このグループ化ありきでやりたいことがあったので、その前段として書いています。)

[研修] 列を選択してグループ化を実行し、[新しい列名] を入力して [操作] で「すべての行」を選択して [OK] をクリックするとグループ化が実行され、同じ研修の行を保持したミニテーブルが作成されます。