前回はパワークエリを使って累計情報を計算する方法を書きました。
そこで今回はその応用編として、グループごとに累計情報を出せないかというテーマでお届けします。
今回は前回と違い、いろいろと操作しないといけませんが、ポイントはこちらです。
- グループごとにインデックスを付番する方法
- グループごとのリストを指定する方法
この2点さえクリアできれば今回のテーマは簡単です。
Contents
この記事でやりたいこと
まず今回使うデータがこちら。
日々の売上データを想定していますが、部門と日付の並びをあえてランダムにしてみました。
これを、部門コード・日付の順で並び替えます。ちなみにこのステップは複数条件の並び替えを紹介しますが、日付さえ並び替え出来ていればOKです。
そして部門コード別に累計額を算出した列を追加します。
では、さっそく見ていきましょう。
事前準備のための処理
データの取得
まず、データタブ「データの取得と変換」よりデータの形式に応じてデータを取得します。
データを複数条件で並び替える
データの取得が完了したら、これを部門コード別・日付別に並び替えてみましょう。
ちなみに、あとで部門コード別にグループ化しますので、実際並び替えが必要なのは日付だけですが、ここでは複数条件で並び替える方法を紹介します。
と言っても、優先する順番で並び替え（ここでは「昇順で並び替え」）を選ぶだけなんですけどね。
これで複数条件で並び替えた状態になります。
このように、並び替えた順番が列名に表示されました。
部門コード別のインデックス列を追加する
次に、まず部門コード別にグループ化します。
部門コード列を選択し、右クリックでグループ化を選択します。
次に、対象の列に「部門コード」が選択されていることを確認し、操作に「すべての行」を選択します。
ちなみに新しい列名は何でも構いません。ここでは分かりやすく「部門別テーブル」としています。
そして列の追加タブより「カスタム列の追加」を選択し、下記のように入力します。
ポイントはカッコ内で、対象列以降の「列名」「初期値（１）」「増加値（１）」を忘れないことですね。
インデックス列などの展開
ここまででクエリはこのように部門ごとのテーブルに集約されてますので、各項目を展開します。
カスタム列タイトル右端の展開ボタンより日付・金額・INDEXを展開します。
これで準備処理は完了です。
ちなみに、ここでは展開した列名が「カスタム.・・・」となっていますが、
数式バーの列名をこのように変更すれば、「カスタム.」を取ることもできます。
要は編集できるってことですね。
ではいよいよグループごとに累計していく方法です。
グループごとに累計値を算出する方法
カスタム列追加で入力する数式
まず結論を見ておきましょう。
列の追加タブ「カスタム列」より、このように入力します。
では詳しい説明に移ります。
累計値を出す関数
ここは前回と同じ関数、List.Sum関数を使います。
列の追加タブより「カスタム列」を選択し、「カスタム列の式」にList.Sum関数を入力するところまでは同じですが、カッコ内の”合計対象リスト”をどうするかが問題です。
この”合計対象リスト”に部門コードごとのリスト、さらには各データに応じてデータ数が変動するようにリストを指定する必要があります。
グループごとのリストの取り出し方
ここが今回一番重要なポイントです。
部門コードごとのリストをどうやって指定するかです。
では、任意の部門の金額列がどうやってパワークエリ上でリストに変換されるのかについて見てみましょう。
インデックスを追加した状態から見ていきます。
この状態で、上の赤枠をダブルクリックします。
すると、部門コード1の部門別テーブルが展開されました。
次に金額列を選択し変換タブ「リストに変換」を実行します。
するとこのようにリストに変換されました。
この状態で、詳細エディターを見てみましょう。
必要なのは赤枠の2行です。
これは1行にまとめるとこうなります。
つまり、グループごとのテーブルをリストとして取り出す方法はこのようになります。
グループ化したとしても、結局リストにするにはテーブル・列の順で指定することに変わりはないということです。
金額に応じたリストの指定
ここは前回と同じくList.FirstN関数を使います。
＝List.FirstN（ 部門コードごとのリスト , 部門コードごとのインデックス ) とすれば、「部門コードごとの、上から○番目の金額リスト」が出来上がるわけです。
ここでの「部門コードごとのリスト」とは前段で説明したリストがそれにあたります。
いったんここで、参照する部門コード列と上から○番目を示す列を確認しておきます。
ここまでで作成したクエリはこのようになっています。
そして、部門コードは1列目「部門コード」そして上から部門コードごとのインデックスが5列目「INDEX」列です。
List.FirstN関数にはこの二つの列を参照します。
部門ごとにリスト：追加されたカスタム{[部門コード=[部門コード]]}[部門別テーブル][金額]
部門コードごとのインデックス：[INDEX]
この2つのパーツをList.FirstN関数に使います。
List.FirstN(追加されたカスタム{[部門コード=[部門コード]]}[部門別テーブル][金額],[INDEX])
これで、部門コード別の合計対象リストの取得ができました。
合計関数との組み合わせ方
ここまでくれば、あとはList.Sum関数に投入するだけです。
List.Sum(List.FirstN(追加されたカスタム{[部門コード=[部門コード]]}[部門別テーブル][金額],[INDEX]))
これでList.FirstN関数で取得したリストをList.Sum関数で合計する関数ができました。
これを、カスタム列の数式として入力したわけです。
これでOKすると・・・
このように累計列が追加されました。
注意点
ここで気を付けることがあります。
List.FirstN関数でリストを取得する際に参照しているステップのことです。
参照しているステップが、直前の「展開されたステップ」ではなく、グループごとのインデックスを追加したステップ「追加されたカスタム」となっていることです。
ポイントは、「展開前のステップを参照していること」です。
展開後のステップでは部門コードに対して複数のテーブルが存在するため、エラーとなるのです。
そこで展開する前、つまり部門コードに対して一意のテーブルが指定されている状態のステップを参照しています。
あとがき
いかがだったでしょうか。
長々と書きましたが、やはりポイントはグループごとのリストをどうやって指定するか、でしょう。
それさえできれば前回の記事と同じ要領で出来るはずです。
ところで、これもっと簡単になりそうな気がするんですけどね。。。
もうちょっと考えてみましょうか。
いずれにせよ、最後まで読んでいただきありがとうございました！
