【パワークエリ】一定期間の予算データから月割計上用データを作成する
１．やりたいこと
下図に記載の通りです。今回は一般的に年２回支給される賞与とそれに伴う社会保険料の個人別予算を支給前の期間で月割り計上する前提で例を作成しています。
２．実践（1）基dataの取込み ～ 「分割月数」「基準年月日」の設定
前画像BEFOREの3つのテーブルをエディターに取り込んだところから始めます。各クエリはデータ型を変更して、適用したステップが「変更された型」となっている状態です。この段階で、「分割月数」「基準年月日」は接続の作成のみ、「予算data」はテーブルとしてワークシートへ読み込んでいます。（説明省略）
下図は参考までに完成後の各クエリの状態を表したものです。
今回は同じエクセルのワークシートのテーブルから取り込んでいますが、ファイルからの取込みとしても構いません。
まず、「分割月数」クエリを選択します。
この「分割月数」はワークシートで入力した月数を単一の数値として以降で参照するものとしますので次の操作を行います。数値入った箇所で右クリックし、「ドリルダウン」をクリックします。
変換タブに数値ツールと表示され、適用したステップに「分割月数1」が追加されました。これでテーブルから数値に変換されました。
次に「基準年月日」を選択します。
まず、先ほどと同様にドリルダウンします。
変換タブに日付時刻ツールと表示され、適用したステップに「基準年月日1」が追加されました。これでテーブルから日付に変換されました。
次が今回のポイントです。
ホームタブ → 詳細エディター をクリックします。
下図が、前述のステップまでのコードです。ここに直接関数を書き込みます。
今回使うM関数は、List.Accumulate です。詳細は以下リンク公式の通り。
繰り返しの処理をする関数で、今回の処理を非常に簡潔に言うと、基準年月日2021/4/1 の月末日(2021/4/30) から6ヶ月分の日付(各月末日）をリスト化するもの となります。
もとの基準年月日1のコードの末尾に「 , 」を入力後、以下数式を入力します。各行の//以降の緑文字は説明書きです。inの後は 分割対象年月 に変更し完了します。
分割対象年月 =
List.Accumulate( {1..分割月数-1}, // リスト：1～ 分割月数-1 の連続した数値
{Date.EndOfMonth(基準年月日1)}, // 初項(seed) : 基準年月日1の日付の月の最終日
(x,y)=> x & {Date.EndOfMonth(Date.AddMonths(基準年月日1,y))} //最初の状態x とリストの各項目xを処理した結果のリスト出力
)
List.Accumulate( {1..分割月数-1}, // リスト：1～ 分割月数-1 の連続した数値
{Date.EndOfMonth(基準年月日1)}, // 初項(seed) : 基準年月日1の日付の月の最終日
(x,y)=> x & {Date.EndOfMonth(Date.AddMonths(基準年月日1,y))} //最初の状態x とリストの各項目xを処理した結果のリスト出力
)
詳細を下図で説明します。
６ヶ月分のリストが作られました。基準年月日の操作はここまでとなります。
３．実践（2）予算dataから月割計上dataを作成
「予算data」クエリを選択し、列の追加タブ → カスタム列 をクリックします。
下図の通り、入力してＯＫをクリックします。カスタム列の式の 基準年月日 は先ほどList.Accumulate関数で作成したリストです。
計上年月日 列が追加されました。各行は基準年月日リストが入れ子となった状態です。
計上年月日列見出し右側の矢印ボタン → 新しい行に展開する をクリックします。
もとのデータが計上年月日の数分に展開されました。
更に列の追加タブ → カスタム列 をクリックします。
賞与の月割計上列を追加するため、以下数式を入力します。
= if [計上年月日] = List.Last(基準年月日) then
Number.Round([賞与予算]/ 分割月数,0,RoundingMode.AwayFromZero)*-1 *(List.Count(基準年月日)-1)
else
Number.Round([賞与予算]/ 分割月数,0,RoundingMode.AwayFromZero))
Number.Round([賞与予算]/ 分割月数,0,RoundingMode.AwayFromZero)*-1 *(List.Count(基準年月日)-1)
else
Number.Round([賞与予算]/ 分割月数,0,RoundingMode.AwayFromZero))
if式で、内容を要約（ラウンドは省略）すると
計上年月日 が基準年月日リストの最期の項目(今回は2021/9/30）であった場合は、
（賞与予算を分割月数（6）で除した金額 ×-1 ）× （基準年月日リストの項目数 -1）
それ以外は、
（賞与予算を分割月数（6）で除した金額 ）
となります。
賞与_月割 列が追加されました。4-8月は予算の1/6 、最終月の9月は4-8月の合計額をマイナスした金額となっています。
更にカスタム列を追加し、社保予算についても同様の数式を入力します。
社保_月割 列が追加されました。予算列は不要となるので（残しておいても問題なし）削除します。
これで完成です。
今回はわかりやすいように賞与と社保の列を分けていますが、その後の集計用データとして使う場合はこの後にピボット解除したり、部門でグループ化したりすればよいと思います。
４．動作確認等
下図は完成後にエクセルワークシートに取り込んだものです。集計行（合計）を追加すると各列の合計は当然0となっています。
試しに基テーブルの分割月数を 6 → 12 に変更してみます。
月割dataシートに戻り、テーブル上で右クリックし、更新 をクリックします。
データが12ヶ月に変更されました。
以上となります。
