Power Queryは大量のデータを効率よく加工できる便利なツールですが、基本的には「列単位で一律に処理する」仕組みになっています。
そのため、
- グループごとに小計行を挿入したい
- データの最後に総合計を追加したい
- Excelの集計表のような見た目にしたい
といった処理は難しいと思われがちです。
しかし、M関数を活用すると、Power Queryでも小計行や合計行を自動的に追加できます。

そのためには、
- レコードとは何か
- テーブルとは何か
- Table.Group関数
- #table関数
- Table.InsertRows関数
といったPower Queryの内部構造を理解することが重要です。
この記事では、グループ別の小計と全体の合計を自動挿入する方法を題材にしながら、M言語による高度なテーブル操作を解説します。
普段のGUI操作だけでは学べない、Power Queryの一段深い世界に挑戦してみましょう。
目次
この記事でできること
✓ レコードの仕組みを理解できる
✓ テーブルの仕組みを理解できる
✓ #table関数を利用できる
✓ Table.Group関数を利用できる
✓ Table.InsertRows関数を利用できる
✓ Table.ColumnNames関数を利用できる
✓ List.Sum関数を利用できる
✓ M関数によるテーブル生成ができる
✓ M関数によるレコード生成ができる
✓ グループ化されたテーブルを扱える
✓ 「すべての行」の活用方法を理解できる
✓ グループ別の小計を作成できる
✓ 小計行を自動挿入できる
✓ 総合計行を自動挿入できる
✓ テーブルへ行を追加できる
✓ レコードをテーブルへ追加できる
✓ GUIでは難しいデータ加工を実現できる
✓ Power Queryの内部構造を理解できる
✓ M言語の実践的な使い方を学べる
✓ Power Query中級者から上級者へのステップアップができる
概要
M関数をうまく活用することで、通常では考えられないような表を作成することができます
今回は上のGIF画像の左の表から、グループ別の小計と全体の合計を自動的に挿入します
上記の内容ではM関数を使用しますが、その前に大事なのはレコードやテーブルとは何か?どのように作成できるか?ということです
まずは、空のクエリからレコードとテーブルを作成してみます
レコードとテーブルの作成
レコード
レコードは角括弧:[]を使用することで空のクエリから作成することができます

テーブル
こちらはM関数:#tableを使用します

関数内では波括弧:{}を組み合わせて、列名と行の値を指定します
行の値は2重の波括弧:{{}}で指定する点にご注目ください
グループ別の小計の挿入
まずは、小計を行う単位:部門でグループ化を行います

操作は「すべての行」を指定します

すると数式バーの中身は次のようになっています
= Table.Group(ソース, {“部門”}, {{“ALL”, each _, type table [部門=text, 商品=text, 金額=number]}})

こちらの数式バーを修正する形で小計を追加します
まず,type tableから]の部分は列の形式を指定しているだけなので、今回は消去しておきます
消去すると次の形となります
= Table.Group(ソース, {“部門”}, {{“ALL”, each _}})
上記の数式の中で「each _」は各テーブルの中身・各行を借り受けしています
こちらに各小計を追加します
小計はテーブルを追加する形で行います
追加なので&からはじめます
= Table.Group(ソース, {“部門”}, {{“ALL”, each _ & }})
&に続くのは、前述のM関数#tableになります
= Table.Group(ソース, {“部門”}, {{“ALL”, each _ & #table(Table.ColumnNames(_),}})
今回は列名を指定するのに波括弧:{}を使用ぜず、Table.ColumnNames関数を使用します
Table.ColumnNames関数を使用することで、列名のリスト:{“部門”,”商品”,”金額”}を代用して指定します
次にテーブルの行の値①②③を指定し、#table関数の右括弧)を付け加えます
= Table.Group(ソース, {“部門”}, {{“ALL”, each _ & #table(Table.ColumnNames(_),{{①[部門]{0}&”小計”,②””,③List.Sum([金額])}})}})
①は部門列に対応します
{0}と指定することで、各テーブルの部門列の1行目を指定しつつ、”小計”を組み合わせることで「部門名+小計」の文字列を追加します
②は空欄を指定します
③は各テーブルの金額列の小計を指定します
ちなみに上記の数式の右から2番目の)が#table関数の)です
これで小計が追加できました

後は展開処理を行い、ステップ名を「mySubTotal」とします


合計の挿入
まず、ステップを追加します

次に追加したステップの数式にてレコードを追加します

尚、追加したレコードは後で呼び出して本来のテーブルの最後に追加します
レコードは前述のような形で次のように指定します

ステップ名はmyTotalとしておきます
ここからステップを再び追加します
次に最終処理として数式にTable.InsertRows関数を指定し、mySubTotalにmyTotalレコードを追加する形にします
Table.InsertRows関数の文法は次の通りとなります
=Table.InsertRows(①テーブル,②挿入行の位置,③追加するリスト)
今回の①はmySubTotalを指定します
②の挿入行の位置の指定は、TableRowCount関数を使用します
③はmyTotalを使用しますが、リスト化するために波括弧:{}でくるみます
= Table.InsertRows(mySubTotal,Table.RowCount(mySubTotal),{myTotal})
これで完成です

<まとめ>
今回はM関数を組み合わせて小計と合計を追加する方法を解説しました
Power Queryは列単位では一律に処理するので意外と感じられる方も多かったのではないでしょうか?
今回の内容はレコードとは何か?テーブルとは何か?そしてM関数とは何か?について学べる内容になっています
ぜひ手を動かして試してみてください


aragraph –>










コメントを残す