【パワークエリ】データに小計・合計行を追加する

今回は基のデータに簡単なピボットテーブルで集計したような小計・合計行をクエリ上で追加する方法について書いてみます。前回のBS作成時と似たような内容ですが、少し違った方法でやってみました。

【目次】

 １．やりたいこと

下図(基のデータ)のようなデータの一覧に、部門毎の小計行、末尾に合計行を追加して(最終形）のようにします。

（基のデータ）

（最終形）

２．作成のポイント

①基のデータテーブルとは別に小計行・合計行のテーブルを作成する。

②M関数を使って２つのテーブルを結合する。

３．実践（1）クエリ(テーブル）の複製 ～ 部門集計テーブルの作成

まず、基データをエディターに取り込みます。クエリ設定ペインは変更された型ステップまで自動で生成された状態です。このクエリ名「集計表」に小計・合計行を追加していきますが、この状態でクエリ名を右クリックし、複製をクリックします。

複製されたクエリの名前を「部門集計」とします。

「部門集計」のクエリをアクティブにして以下編集していきます。

ホームタブ→グループ化をクリックします。グループ化の内容は下図の通り、「部門」列を選択し、新しい列名＝売上、操作＝合計、列＝売上　としＯＫをクリックします。

部門でグループ化されたテーブルができました。

次に、列の追加タブ→カスタム列 をクリックします。カスタム列の式は下図の通り、

=[部門]&" 小計"   とします。基の部門列の名称に" 小計"（前に半角スペース有）を追加した列を作るということです。

カスタム列が追加されたら、部門列を削除します。

カスタム列の名称を ”部門"に変更し、列を先頭に移動します。

次に、数式バー左のfxボタン（ステップの追加）をクリックします。クエリの設定ペインにカスタム1が追加され、数式バーは下図の通り前のステップを参照したものが自動で入ります。

数式バーを以下の数式に直接書き換えます。

変更前：

　#"名前が変更された列 "

変更後：

= Table.InsertRows( #"名前が変更された列 ",Table.RowCount(#"名前が変更された列 "),{[部門="合計",売上=List.Sum(#"名前が変更された列 "[売上])]})

合計行が追加されました。

数式の内容について次の画像で詳しく説明しています。

ここまでで、部門集計テーブルの作成は終わりです。

４．実践（2） ２つのテーブルの結合～部門列の並び替え

ここで、「集計表」クエリをアクティブにします。先ほどと同様に数式バー左のfxボタン（ステップの追加）をクリックします。クエリの設定ペインにカスタム1が追加され、数式バーは下図の通り前のステップを参照したものが自動で入ります。

数式バーを以下の数式に直接書き換えます。

変更前：

　変更された型

変更後：

　= Table.Combine({変更された型,部門集計})

Table.Combine関数はテーブルのリストをマージした結果として得たテーブルを返す関数です。上記数式は、集計表の前ステップである「変更された型」と  小計・合計行のテーブル「部門集計」をマージするということです。各テーブルの列名は合わせておく必要があります。また、引数のテーブルはリスト型となりますので { } でくくることがポイントです。

下図の通り、変更された型の末尾に部門集計テーブルが追加された状態になります。（項目なしはnull）

最後に、ホームタブ→昇順で並び替え をクリックします。

これで完成です。

５．最後に

 今回の方法で単一の列の小計を出すことはできましたが、ピボットテーブルのように複数の小計列（今回でいう、部門と商品等）は難しそうです。また、この方法で日付列の小計を追加する場合は、Date.ToText関数等で一旦日付をテキスト化する必要があります。（Date型にテキストをくっつけるとエラーになります。）