【M言語独自のリスト作成を通じて、経過営業日を自由自在に算出する】

エクセルのワークシートには経過営業日を計算してくれる便利な関数があります。Power Queryではその手の関数はないですが、独自のカレンダーテーブル活用により自由自在に計算方法をカスタマイズできます

上の画像のような連続した値を作成する方法は過去の記事でも紹介しています

今回の記事ではこういったPower Query・M言語独自のテクニックが詰まった内容になっています

ポイント

今回のポイントは関数などで一律に計算するのではなく、日付を並べたうえで計算を行います

上の画像のように該当する日付を並べて、曜日に変換し「0,1」を並べます

そして、最後に「0,1」の列を合計します

この合計が経過営業日です

ですので、後から条件式の追加などが容易です

上の画像では祝日が考慮されていませんが、祝日の考慮はもちろん、その他の条件の考慮も容易に行えます

カレンダーテーブルの作成

カレンダーテーブルの作成はリスト作成⇒テーブルに変換の流れで作成します

「..」を使用すると連続した値のリストが作成できます

この仕組みを利用して、他クエリからまずは連続した日付のリストを作成します

他のクエリとは下の画像のような２つの日付を記したクエリになります

こちらのクエリの各列の１行目を参照して、空のクエリでリストを作成します

＊｛０｝で１行目を参照します

ここではNumber.From関数で一旦数値に変換しています

= {Number.From(期間単数[期間A]{0})..Number.From(期間単数[期間B]{0})}

連続した数値を作成した後は、テーブルに変換します

後は日付形式に変換を行います

曜日への変換

曜日への変換は「Date.DayOfWeekName関数」を使用します

曜日だけではなく、祝日も考慮が必要です

祝日については別途テーブルを作成しておく必要があります

こちらは作成した空のクエリのマージします

そして、曜日との集約を「条件列」で行います

これで「0,.1」を並べる列を作成する準備ができました

0,1列の作成及び合計

0,1を並べる列はカスタム列作成画面でif関数を使用して作成します

これで0,1が並ぶ列/Digitを作成することができました

尚、上記の式ですと「起算日の曜日」が休日でない場合には自動的に計算に含まれています

それでは本題に戻りますが、最後にこちらのDigit列を対象にして、変換タブから合計処理を行います

これで経過営業日を算出できました

ちなみに祝日クエリをマージした時に、日付の並びが変わってしまいますので、並び替えを行っておくと計算過程が分かりやすいです

計算方法の調整

起算日には曜日・祝日に関わらず計算に入れる（0を立てる）、もしくは計算に入れないなどの調整にはインデックス列を使用します

上記の画像のように、起算日の行になるのは「インデックス列」の１になります

ですので、if式をインデックスが１の時には値が０（もしくは１）になるように修正します

逆に計算の終点の日を曜日・祝日に関わらずに計算に入れる場合にはLis.Max関数を使用します

複数行計算する場合

複数行にて計算する場合には、カスタム関数を使用します

前述のクエリの詳細エディタを開き、コードに記号を埋め込んで関数化します

={Number.From(myDate[起算日A]{0})..Number.From(myDate[起算日B]{0})}

⇒{Number.From(A)..Number.From(B)},

上の画像のAとBの記号が、下の画像の各列と対応します

そして、記号を埋め込んで完了ボタンを押すと、関数は完成です

こちらの関数を複数行あるクエリで呼び出します

この際、前述の記号に各該当列を割り当てます

これで、各行に関数が割り当てられます

後は関数で作成した列を展開して終了です

＜まとめ＞

今回は経過営業日をPower Query独自のカレンダーテーブルにより算出する方法を解説しました

もしかしたら、上記の内容で「この画像の起算日の計算式は？」という内容もあるかもしれませんが、ご容赦頂ければと思います

今回の方法ですと、ワークシート関数で一律に計算するより、分かりやすく、カスタマイズしやすいです

ぜひ、手を動かしながら試してみて頂きたいと存じます！

にほんブログ村