「自作カレンダー」について
通常データを読み込むために使うPower Queryですが、今回は逆にPower Queryでデータを用意します。サンプルとして自作のカレンダーテーブルを作りながら、その中でPower Queryの本体である「M」という言語と、日付についての処理を紹介していきます。
【技術的なポイント】
● 空のクエリから作る
● 日付型データは、 #date(年, 月, 日)
● 期間型データは、 #duration(日, 時間, 分, 秒)
● 日付リスト作成は、List.Dates(開始日, 増加回数, 期間の増分)
実践
さっそく始めます。
空のクエリを開く
今回はデータソースは自分で作るので、空のクエリから作ります。
データメニュー→データの取得→その他のデータソースから→空のクエリを選択します。
これで空っぽのPower Query エディターが開きます。
最初に、画面左上の詳細エディターを開きます。
詳細エディターが開きます。
このテキストこそがPower Queryの取り込み・加工ロジック本体のプログラミング言語、「M」です。
ちなみに「let」以下に適用したステップが順番に並び、最後の適用したステップが「in」に来ます。したがって通常は「let」の最後の行は「in」と一致します。今はまだ何もしていないので、「let」 の「ソース」がそのまま「in」に来ています。（ソースの中身は””なので、結果も無です。）
ここまで確認したら画面右下のキャンセルを押して、元の画面に戻ります。
日付リストを作る
今度は、数式バーに以下の式を記入します。
= List.Dates(#date(2019, 1, 1), 365*2, #duration(1, 0, 0, 0))
先頭のList .Dateは日付リストを作るPower Queryの関数で、その引数として①#date(2019, 1, 1)、②365*2、③#duration(1, 0, 0, 0)を設定しています。
List.Dates関数の文法は以下のようになります。
List.Dates(①開始日 [date型], ②増加回数 [number型], ③期間の増分 [duration型])
ここで日付型データの文法は、① #date(年, 月, 日)
期間型データの文法は、 ③ #duration(日, 時間, 分, 秒)
となりますので、今回の式は以下のリストを作ることになります。
① 2019年1月1日から
② 365*2 回増える
③ 1日ずつ
①と③でそれぞれ#date、#durationを使っているのはデータ型を関数の書式に合わせるためで、②のみ数値型なので数字をそのまま入れています（数式も可）。
これだけで２年分の日付リストができてしまいます。
ちなみにさりげなくリストと言っていますが、これはPower Queryではテーブルとは異なり、一列のみからなるデータのことを言います。それに対して、テーブルは複数の列と行を持てます。
カレンダーテーブルを作る
ヘッダーを見ると列名が「リスト」とあり、前述したようにこれは日付「リスト」であって、「テーブル」ではないのでテーブルに変換します。変換メニューの左上、テーブルへ変換をクリックします。
テーブルへの変換ウィンドウが現れますが、そのままOKを押します。
これでテーブルに変換されました。列名がColumn1になり、その左側にテーブルアイコンが出たことに注目してください。
まずはColumn1の列名をダブルクリックし、列名を「日付」に変更します。
次に変換メニューのデータ型の検出をクリックして、日付型に変換します。
これで日付の下準備が済んだので、その他の項目を追加していきます。
まずは列の追加メニューに移動します。
次に、日付→年→年を選択します。
これで年が追加されました。
カーソルが年列に移動しているので、日付列を選択し直したあと以下の手順でそれぞれ月と曜日を追加します。
日付→月→月を選択。
日付→日→曜日名を選択。
最後に仕上げとして、別に用意していた祝日カレンダーと日付列を照合列としてマージします。
マージの手順については、以下記事を参考にしてください。
modernexcel7.hatenablog.com
最後におさらいとして再び詳細エディターを開いてください。
今度は、Power Queryエディターで追加された適用されたステップが「let」以下に並び、その最後が「in」に渡されています。
ちなみに、「let」以下では左側にステップ名が並び、「＝」を挟んで右側の関数に直前のステップ名が伝言ゲームのように参照されているのが分かります。
また、一行目の関数は「List」で始まる関数で、それ以降は「Table」で始まる関数が並んでいます。これは2行目でリストがテーブルに変換されたためです。
ここまで確認出来たら、キャンセルを押して元の画面に戻り、閉じて読み込むを実行してください。これで自作カレンダーテーブルは完成です。
