【パワークエリ】データに小計・合計行を追加し、更に列のピボット、合計列を追加する。
以前、「データに小計・合計行を追加する。」記事を書きましたが、今回はそれの続編で更に列にピボット展開し、列の合計までをパワークエリで作る方法について書いてみます。ただ、やることはさほど難しくはないのですが、ワークシート上でピボットテーブルで集計するほうが数十倍速いです。あくまでこうゆうこともできるよという紹介となりますのでご承知おきください。
１．やりたいこと
下図の通りBEFOREのようなデータから、AFTERのように部門小計・合計行を追加、更に日付を列に展開し、部門・商品毎の合計列を追加した集計表をパワークエリで作成します。
２．実践（1）基データ取込み～クエリの複製（下準備）
まず、基となるデータをエディタに取込みます。
日付列の左端をクリックし、型を日付に変更しておきます。
この状態のクエリ（名前を集計表としています）をベースに以下でパーツとなる小計・合計行／列を作成していきます。クエリの設定ペインの適用したステップは変更された型までの状態です。
左側の集計表クエリ名上で右クリックし、複製をクリックします。
集計表（2）が作成されます。同じ作業をあと3回繰り返し、下図左のような状態にします。その後、集計表（2）〜集計表（5）の名前を下図右の名前に変更します。名前は後でわかるようにしておけば何でも構いません。今作成したクエリの中身をこの後作っていきます。
次のステップの前に、この後に中身を作るクエリ（テーブル）と最終形の集計表との関係を下図にまとめておきます。すべて表現できていませんがおおよそ下図の通りです。
３．実践（2）各小計・合計行／列クエリの編集（パーツ作成）
各クエリの中身を作っていきます。まず、合計列クエリを選択します。この時点では先ほどの集計表クエリと同じ状態です。
列の追加タブ→カスタム列 をクリックします。
下図の通り式を入力し、OKをクリックします。
日付列の上で右クリックし、削除をクリックします。
カスタム列の名前を「日付」に変更します。
このクエリはここまでです。このクエリは合計列自体ではなく、最後に集計表クエリでピボットする前の合計列のもととなるデータを作成したものとなります。
続いて部門合計列クエリを選択します。ホームタブ→グループ化 をクリックします。
下図の通り、選択／入力し、OKをクリックします。
部門毎に売上がグループ化されました。
列の追加タブ→カスタム列 をクリックします。
下図の通り、列名と式を入力し、OKをクリックします。
日付列が挿入されました。部門合計列クエリはここまでです。
続いて、部門小計行&部門合計クエリを選択します。ホームタブ→グループ化 をクリックします。
詳細設定のラジオボタンを選択し、日付、部門列を選択します。新しい列名等は先程同様に入力／選択し、OKをクリックします。
数式バーの左のfxボタン（ステップの追加）をクリックします。
適用したステップにカスタム1が追加され、数式バーは前ステップのグループ化された行を参照した状態になります。
この数式バーを直接次の数式に書き換えます。
変更前：
= グループ化された行
変更後：
= Table.Combine( {グループ化された行,部門合計列})
この関数は複数テーブルをひとつに結合するものです。つまり、前ステップのグループ化された行 と 先程作成した部門合計列 が下図の通りひとつになります。
続いて日付列を選択した状態から、変換タブ→列のピボット をクリックします。
値列は売上を選択し、OKをクリックします。
行が部門小計で日付が列に並び最後が合計列のピボットされた表になりました。
列の追加タブ→カスタム列 をクリックします。
カスタム列の式を下図の通り入力し、OKをクリックします。もとの部門列に (半角スペース+)小計を追加した列にします。
部門列を削除します。
先ほど作成したカスタム列の名前を 部門 に変更します。これでこのクエリは終わりです。
次に最後の日付合計行クエリを選択します。ホームタブ→グループ化 をクリックします。
グループ化のキーを日付とし、その他下図の通り選択／入力し、OKをクリックします。
グループ化されたらfxボタンをクリックし、適用されたステップ カスタム1が挿入された状態にします。
数式バーを直接以下の数式に書き換えます。
変更前：
=グループ化された行
変更後：
= Table.InsertRows( グループ化された行,Table.RowCount(グループ化された行),{[日付="合計", 売上 =List.Sum(グループ化された行[売上]) ]})
数式の詳細について次の画像で説明します。
合計行が追加されました。この合計値 90,230 が行列の合計値となります。
変換タブ → 入れ替え をクリックします。
前ステップの行と列が入れ替わった状態になります。この時点での列名はColumn1～5が自動的に割り当てられます。左上のワークシートマークのよいなボタンをクリックします。
1行目をヘッダーとして使用 をクリックします。
1行目がヘッダー（列名）となりました。列の追加タブ → カスタム列をクリックします。
新しい列名を 部門 とし、カスタム列の式を下図の通り入力してOKをクリックします。
これで日付合計行クエリの作成は完了です。
４．実践（3）集計表の作成（組み立て）
集計表クエリ を選択します。
fxボタン をクリックし、適用されたステップ：カスタム1 が挿入された状態にします。
数式バーを次の数式に直接書き換えます。
変更前：
= 変更された型
変更後：
= Table.Combine( {変更された型,合計列})
前述と同じ関数で、前ステップ：変更された型 と 合計列クエリ（テーブル）がひとつに結合されます。
続いて、日付列を選択した状態で ホームタブ → 昇順で並べ替えボタンをクリックします。これはこの後に列のピボットを実行する際に列を日付順に並べる為の操作です。（基データが日付順に入力されているとは限らないという前提）
日付列を選択した状態で、変換タブ → 列のピボット をクリックします。
値列を 売上 とし、OKをクリックします。
ピボットされました。これに小計・合計行を加えていきます。
fxボタン をクリックし、適用されたステップ：カスタム2 が挿入された状態にします。
数式バーを次の数式に直接書き換えます。
変更前：
=ピボットされた列
変更後：
= Table.Combine({ピボットされた列,#"部門小計行＆部門合計",日付合計行})
前述と同じ関数ですが、今回は3つのテーブル（前ステップ：ピボットされた列、部門小計行＆部門合計、日付合計行）を結合します。
続いて、部門列を選択した状態で ホームタブ → 昇順で並べ替えボタンをクリックします。
更に、商品列を選択した状態で同様に 昇順で並べ替えをします。
最後の仕上げに、数値がnull（空白）のものを 0 に置き換えたいのですが、ここは少し工夫が必要です。まず、置換したい日付列を選択した状態で、ホームタブ → 値の置換 をクリックします。
下図の通り、検索する値を null 、置換後を 0 とし、OKをクリックします。
各日付列のnull が 0 に置換されました。しかし、自動生成された数式をよく見ると各列名に対して値の置換処理が実行されています。このままだと、新たな日付がデータに追加された場合に置換がされなくなってしまいます。
これを回避するため、数式の一部を以下に書き換えます。
変更前：
= Table.ReplaceValue(並べ替えられた行,null,0,Replacer.ReplaceValue,{"2021/04/01", "2021/04/02", "2021/04/03", "2021/04/05"})
変更後：
= Table.ReplaceValue(並べ替えられた行,null,0,Replacer.ReplaceValue,Table.ColumnNames(並べ替えられた行) )
※ステップの追加から直接上記変更後の数式を入力する方法でも構いません。
下図は上記変更後の数式の補足となります。
テーブルのすべての null が 0 へ置換されましたが、今度は商品列の各小計・合計行のnullまで 0 が入ってしまいました。ここは個別に逆の操作をします。
商品列を選択した状態で、ホームタブ → 値の置換 をクリックします。
先ほどと逆に、検索された値 0、置換後 null としOKをクリックします。
これで完成となります。
５．動作確認検証
下図はワークシート上の基データです。ちなみに作成した集計表は別ワークシートに取込み、その他のクエリは接続のみとしています。
試しに基データに2行追加してみます。
取込み済の集計表シートを選択し、テーブル上で右クリックし、更新をクリックします。
追加したデータがきちんと集計表に反映されました。以上となります。
長くなってしまいましたが、最後まで読んでいただきありがとうございました。
