【M関数を利用することで、数式の修正しなくても列数が可変の表を組み合わせて計算できるようにします】
値を別表で作成した上で計算を行うケースはよくあると思います
上の画像のケースでは、ボーナスを計算するのに基礎となる評点を作成した上で各ポイントをクリアしていれば「ポイント別」に加算点を計算しています
例えば、上の画像で言えば田中さんはポイント1と2をクリアしているので、80*9%と80*10%が加算点の合計点になります
上記では関数で計算していますが、問題はポイント数が変わった場合です
ポイント数が変わるたびに関数を変更しなくてはなりません
今回の記事では、いくつかのM関数を組み合わせたカスタム列を作成することで、計算の対象列数が可変でも計算が正しくできるようにします
目次
使用するデータ
２つのテーブルを使用します
上の画像は「tableMatrix」です
こちらのテーブルを読み込んだクエリでカスタム列を作成します
上の画像は「tablePoint」です
こちらもカスタム列内で使用します
最終的に作成するカスタム列
今回の記事で最終的に作成するカスタム列は次の通りとなります
一見複雑ですが、List.Accumulate関数の箇所を紐解けば一気に理解のハードルが下がります
List.Accumulate関数
次のリスト：myAccumulateを基にしてList.Accumulate関数でリストの合計値を計算してみます
変数：state,currentが出てくるので一見ややこしいです
= List.Accumulate(myAccumulate,0,(state,current)=>(state+current))
文法は次の通りとなります
=List.Accumulate(リスト名,計算の最初の値,計算式）
話を分かりやすくするために、実際にList.Accumulate関数の計算履歴を追ってみたいと思います
計算式1 ⇒state:0(計算の最初の値)+current:1 ⇒1
計算式2 ⇒state:1(計算式1)+curernt:2 ⇒3
計算式2 ⇒state:3(計算式2)+current:3 ⇒6
つまり、リストの中身を変数を使用した計算式にて順次計算して累計処理をしているわけです
では、最終式のList.Accumulate関数の中身を実際に分解していきたいと思います
Table.ColumnNames関数
List.Accumulate関数の第一引数はステップ名：myPointです
myPointはTable.ColumNames関数で算出された値です
Table.ColumnNames関数はテーブルの列名をリスト化します
今回はポイントを設定したtablePointクエリの列名をリスト化します
関数の引数はリスト名を指定するだけでOKです
このリストがList.Accumulate関数の第一引数になるということは、計算式（state+current）内の変数：currentには「ポイント1・・・」などのテキストが繰り返し代入されるとこになります
どうやって計算が行われるのか？と不思議になる読者の方もいらっしゃると思います
この点は次のRecord.Field関数の箇所で解説します
Record.Field関数
Record.Field関数は特定のレコード中から特定のフィールドの値を抽出します
ちなみに、上の画像のtablePoint{0}*はtablePointテーブル/クエリの1行目のレコードになります
今度は第二引数を”ポイント2″を指定してみます
ポイント2のフィールドの値が抽出されます
List.Accumulate関数内では「ポイント1,2・」が繰り返し変数：currentに入ります
この変数：currentはRecord.Field関数の第二引数に設定され、数値が抽出されます
計算式の追跡
では、ここでList.Accumulate関数の計算式を追跡してみましょう
(state,current)=>①state+②Record.Field(tablePoint{0},current)*③Record.Field(_,current)*④[評点]
追跡するのは次の行/レコードです
計算式１:state/0(計算の最初の値)、current/”ポイント1″
①：0(計算の最初の値・第二引数)
②：Record.Field(tablePoint{0},”ポイント1″)=0.09
③：Record.Field(_,”ポイント”)=1
④：[評点]=80
①+②*③*④の計算式により7.2が算出されます
ここで注意点です
③の_はカスタム列を作成しているクエリのレコードをそのまま受け継ぎます
今回の場合は次の画像のレコードを受け継ぎます
計算式２:state/7.2(計算式1の値)、current/”ポイント2″
①：7.2
②：Record.Field(tablePoint{0},”ポイント2″)=0.1
③：Record.Field(_,”ポイント”)=1
④：[評点]=80
①+②*③*④の計算式により7.2+8=15.2が算出されます
計算式３:state/15.2(計算式2の値)、current/”ポイント3″
①：15.2
②：Record.Field(tablePoint{0},”ポイント3″)=0.12
③：Record.Field(_,”ポイント”)=0
④：[評点]=80
①+②*③*④の計算式により15.2+0=15.2が算出されます
一見、数式は複雑そうですが、List.Accumulate関数の変数の中身を紐解くとそれほど難しくないことが分かります
＜まとめ＞
今回は列数が可変の複数の表を組み合わせて計算する方法を解説しました
M関数を組み合わせることで、列数を可変にした計算が可能になります
最後に注意点です
let式を使用していますが、「,」を付ける位置を注意する必要があります
inの前、letの最後は「,」を付ける必要がありません
逆にそれ以外のステップは「,」が必要となります
参考までに使用したファイルを添付します
コメントを残す