前回これで最後と書いた癖に、もう一つ追加。
列のピボットで作成したマトリクス表に「集計行」を追加したい時どうするか。
「テーブルの最下行に1行追加ってどうやるの?」と悩んだ方は、Power Queryの便利な機能を一つ忘れている。「行列の入替」である。
縦横逆に集計して、カスタム列の追加で集計行(列だけど)を追加して、最後に行列を入れ替えてやればいいのだ。
「日付」列を選択した状態で[編集]→[列のピボット]
値列:件数 詳細設定オプションの値の集計関数:合計 →[OK]
これで縦横逆のマトリクス表が完成。後はカスタム列の追加を実行するだけ。こういう時に便利なのが「Record.FieldValues(_)」。何列あろうが同行の値をリストとして読み込んでくれる。列数が可変であっても関係なし。
[列の追加]→[カスタム列]
新しい列名:合計 カスタム列の式:= List.Sum(List.Skip(Record.FieldValues(_)))
「List.Skip」を挟んでいるのは、先頭の「日付」列を集計範囲から外すため。続いてカスタム列の追加で「個数」列を追加する。
新しい列名:個数 カスタム列の式:= List.NonNullCount(List.Skip(Record.FieldValues(_)))-1
最後に「-1」しているのは、一つ前に追加した合計列の分を引くため。
これでほぼ形は出来上がったので、最後の仕上げ。行列入替の前にヘッダーを先頭行に降格しておく。
[変換]→[1行目をヘッダーとして使用]→[ヘッダーを1行目として使用]
[変換]→[入れ替え]
これでおしまい。あとは「閉じて読み込む」で完成。
ただし一点だけ注意。今回は横軸が「日付」なので、あえて先頭行をヘッダに昇格していない(見出しにすると必ず「文字列」になってしまうから)。このままだと見苦しいので、
[テーブルデザイン] →テーブル スタイルのオプションの[見出し]のチェックを外す
これで見た目もすっきりする。行ごとの色付けや罫線はお好みで。