PowerQuery 集計した値を元テーブルで利用する方法｜グループ化

「グループ化」を用いて集計した値を集計前のテーブルで利用する方法を紹介します。

別テーブルで用意してテーブル結合すればできますが、それだとテーブルが多くなってしまいますので ここでは別の方法を解説します。

ドノ

ここでは 別テーブルのセル値を参照することにより集計値を利用する方法を紹介します

別テーブルの値の取得方法

集計前テーブルで集計値を利用するには 別テーブルの値を取得が必要ですので まずはその方法を紹介します。

パワークエリに取り込んだテーブルの値は下記の式で取得することができます。行番号は0始まりなので注意してください。

テーブル名 [列名] {行番号}

ドノ

テーブル名・列名・行番号は状況に応じて変更してください。

詳しい説明はこちらの記事でも紹介していますので、より知りたい方は参考にしてみてください。

あわせて読みたい

PowerQuery テーブル参照・セル参照の方法 パワークエリ内にある別テーブルの値 や エクセルのワークシート内での入力値 を利用する方法を紹介します。 この方法を利用することでこのようなことができるようにな…

Sponsored link

集計値を集計前テーブルで利用する方法

集計前テーブルでグループ化により集計した値を利用する手順を解説します。

設定方法の例として、下図のように会社ごとの売上合計を集計して その集計値を元テーブルに新規列として追加します。

STEP

グループ化により値の集計

まず 変換タブにある「グループ化」を利用して値を集計します。

会社ごとの売上合計を求めるのでグループ化する列に「会社」、集計列に「売上」を設定します。

集計結果としては下図のようになります。

STEP

新しいステップの挿入（元テーブル参照）

次に集計前のテーブルを処理ステップに戻します。

クエリの設定の適用したステップの最後にあるステップを右クリックして「後にステップの挿入」を選択します。

挿入したステップの数式バーに集計前テーブル名（ここでは「ソース」）を入力します。

そうするとグループ化して集計する前のテーブルを参照することができます。

STEP

集計値の利用

次に集計値をカスタム列として元テーブルに追加します。

まずは列の追加タブにある「カスタム列」を選択します。

次に別テーブル値の取得方法（テーブル名 [列名] {行番号}）を利用して、下記のカスタム列の式を入力します。ここでは if構文を用いて会社Aと会社Bのときで参照する列を変更しています。

if [会社]="A" then グループ化された行[合計]{0}
else if [会社]="B" then グループ化された行[合計]{1}
else null

テーブルの値の参照方法

値の参照に使うテーブル名・列名・行番号はそれぞれ下図のものとなります。
行番号で0始まりですので注意してください

STEP

集計値の利用完了

下図のように会社ごとの合計値が元テーブルに追加できています。

上記の方法で集計値の利用はできましたが、問題としてデータソースが更新され集計テーブルの値の位置が変わってしまうと誤った値の参照をしてしまう可能性があります。

続いて、その問題を解消してデータソース更新や集計テーブル変更に対応する方法を紹介します。

Sponsored link

集計テーブル変更（参照位置変更）に対応する方法

データソース更新などにより集計グループの増減があった場合、テーブル値の参照位置が変わってしまう可能性があります。

それを解決するためには、List.PositionOf関数を用いて行番号を自動で取得すればOKです。

List.PositionOf関数とは？

行番号の自動取得にはList.PositionOf関数が利用できます。テーブル名・列名・検索値を設定することで、あるテーブルにある列の検索値に一致する行番号（インデックス）を取得することができます。

List.PositionOf(テーブル名[列名],"検索値")

テーブル値の参照位置の自動取得（インデックス自動取得）

設定方法の例として、手順説明で使用したデータを用います。

下図では会社Aの場合には集計テーブルの0行目を取得して、会社Bの場合には集計テーブルの1行目を取得するように設定しています。この行番号を会社名が一致している行番号を利用するように変更します。

if [会社]="A" then グループ化された行[合計]{0}
else if [会社]="B" then グループ化された行[合計]{1}
else null

式は下記のように変更します。それぞれの変更箇所の意味はこちらです。

• List.PositionOf(グループ化された行[会社],”A”)：集計テーブルの「会社」列が “A” の行番号
• List.PositionOf(グループ化された行[会社],”B”)：集計テーブルの「会社」列が “B” の行番号

if [会社]="A" then グループ化された行[合計]{List.PositionOf(グループ化された行[会社],"A")}
else if [会社]="B" then グループ化された行[合計]{List.PositionOf(グループ化された行[会社],"B")}
else null

これによりデータソース更新がされても 集計テーブルの参照インデックスを自動で変更してくれます。

ドノ

グループ化する列の項目毎にif構文を準備しておく必要はあるので注意してください。

if構文についてはこちらの記事で紹介していますので参考にしてみてください。

DONOLOG

PowerQuery if文でカスタム列を作成する｜複数条件と入れ子 | DONOLOG パワークエリでIF文を使って新しい列データを作成する方法を紹介します。リボンにある「条件列」ボタンを用いる方法とカスタム列+IF文（Ｍ言語）を用いる方法を解説します…

Sponsored link