【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独自のカレンダーテーブルにより算出する方法を解説しました

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

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

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

にほんブログ村

【通常は一律で処理する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関数とは何か？について学べる内容になっています

ぜひ手を動かして試してみてください

にほんブログ村