【通常は一律で処理するPower Queryですが、M関数を使えば小計と合計を列に追加・挿入することができます】
M関数をうまく活用することで、通常では考えられないような表を作成することができます
今回は上のGIF画像の左の表から、グループ別の小計と全体の合計を自動的に挿入します
上記の内容ではＭ関数を使用しますが、その前に大事なのはレコードやテーブルとは何か？どのように作成できるか？ということです
まずは、空のクエリからレコードとテーブルを作成してみます
目次
レコードとテーブルの作成
レコード
レコードは角括弧：[]を使用することで空のクエリから作成することができます
テーブル
こちらはM関数：#tableを使用します
関数内では波括弧：{}を組み合わせて、列名と行の値を指定します
行の値は２重の波括弧:{{}}で指定する点にご注目ください
グループ別の小計の挿入
まずは、小計を行う単位：部門でグループ化を行います
操作は「すべての行」を指定します
すると数式バーの中身は次のようになっています
= 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}と指定することで、各テーブルの部門列の１行目を指定しつつ、”小計”を組み合わせることで「部門名＋小計」の文字列を追加します
②は空欄を指定します
③は各テーブルの金額列の小計を指定します
ちなみに上記の数式の右から２番目の）が#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関数とは何か？について学べる内容になっています
ぜひ手を動かして試してみてください
コメントを残す