パワークエリで空のクエリから自動更新される日付テーブルを作る方法を投稿します。
日付が含まれたデータを使って時系列グラフ描画する際に、「日付抜け」があると時系列表現を行う折れ線グラフなどで描画がしっくりこないことがあります。
いわゆるトランザクションが記録される明細データなどは、明細が発生する日のみが記録され、明細が全くない日であれば、その日付は抜けてしまいます。
BIツールなどを使う場面では、この「日付抜け」を補完するために日付のみのテーブルで補完することがあります。
PowerQueryでは、M言語を使うことによって指定した日付から現在日付までを動的に出力することが可能です。若干、違和感ある操作で忘れそうなのでメモとして投稿します。
パワークエリで空のクエリから自動更新される日付テーブルを作る
手順1 空のクエリを作る
日付テーブルはエクセルシート上ではなく、パワーピボットのデータモデルとして使いたいので「空のクエリ」から作成します。
▲ 「データの取得」⇒「その他のデータソース（O）」⇒「空のクエリ（Q)」を選択します。
▲ Power Queryエディターが開きます。日付テーブルの作成は数式バーから行います。
手順2 開始日を指定する
M言語の日付関数である「#date」関数で日付値を作ります。
構文
date(year as number, month as number, day as number) as date説明
年 year、月 month、日 day から日付値が作成されます。 次の条件を満たさない場合、エラーが生じます。
1 ≤ 年 ≤ 9999
1 ≤ 月 ≤ 12
1 ≤ 日 ≤ 31
▲ 今回は「2019年1月1日」を基準日としてテーブルを作成します。数式バーに「#date(2019,1,1)」と入れます。Enterを押すと、日付のみが返されるシンプルな処理です。
PowerQueryエディター上のステップは、「ソース」という名前で記録されました。この「ソース」を次のM関数で使います。
手順3 何やらコードを打ち込む
ここからはM言語がわからないとさっぱりな感じ。私もまだ不慣れ。
▲ 数式バーの「fx」を押します。
これは「ステップを追加する」ボタンらしく、押すたびにステップが追加されます。押すのは一回でOKです。追加されたステップはその前のステップを参照しています。
そして次のコードを打ち込みます。
= List.Dates(ソース, Number.From(DateTime.LocalNow()) - Number.From(ソース), #duration(1,0,0,0))▲「ソース」は、前のステップで打ち込んだ値（2019年1月1日）を参照しています。
内容的には、「ソース」で指定した日付（2019年1月1日）から始まり、1日ずつ増分されるdata値のリストが現在日まで返されます。
▲ 2019年1月1日から現在日までのリストが作られました。エクセルのデータ更新ボタンを押すたびに範囲が変更します。
M関数の意味を知りたいからは以下、押すと開きます。
構文
List.Dates(start as date, count as number, step as duration) as list説明
「start」で指定した日付から始まり、「step」間隔で増分されるdata値のリストが「count」回数分返されます。
利用例
元旦 (#date(2021, 1, 1)) から始まり、1日ずつ増分される5個のdate値のリストを作成します。
List.Dates(#date(2021, 1, 1), 5, #duration(1, 0, 0, 0))構文
Number.From(value as any, optional culture as nullable text) as nullable number説明
- 指定した value から number 値が返されます。
- オプションの culture を指定することもできます (例: “en-US”)。
- 指定した value が null の場合、null は Number.From が返されます。
- 指定した value が number の場合、value が返されます。
利用例
#datetime(2020, 3, 20, 6, 0, 0) の number 値を取得します。
Number.From(#datetime(2020, 3, 20, 6, 0, 0))構文
DateTime.LocalNow() as datetime説明
システムの現在の日付と時刻に設定された datetime 値を返します。
構文
#duration(days as number, hours as number, minutes as number, seconds as number) as duration説明
days(日)、hours(時)、minutes(分)、seconds(秒)の数値から期間値が作成されます。
手順4 テーブルへの変換、データ型を日付へ
コードを打ち込んだだけではまだ使えず、もうひと手間あります。
▲ コードを打ち込むとリボンメニュー「（リストツール）変換」タブが増えています。この右端の「テーブルへの変換」を押します。
▲ 設定画面が出ますが、特に設定せずにOKを押します。
▲ 後は、列名称を付けてデータ型を「日付」に変更すれば、使えるようになります。
手順5 フィルター・スライサー用に列を追加しておく
これはオプションですが、日付テーブルにフィルターやスライサーで使いそうな列を追加しておくと便利になります。
▲ 年、月などはGUIから追加できます。
▲ 会計年度はカスタム列、会計四半期は条件列を使えば追加できます（データモデル入れた後に、DAXでも追加できます）。
ここまで出来たらシート内に読み込むか、データモデルに追加すればピボットテーブルで使えるようになります。
まとめ
M言語はとっつきづらいですが便利ですね。
VBAとかでもできるでしょうが、GUI＋ローコードで複雑なことができるのは魅力です。
紹介する書籍の中で翔泳社出版のものは、紙媒体やKindleのほかに公式サイト「SEshop」でPDF書籍が購入できます。オフィスで画面に移しながら手を動かせるので使い勝手抜群！！
全国送料無料！IT書、ビジネス書、資格書が豊富なSEshop
「SEShop」だとクーポン利用可、PDF版での入手となり扱いやすいです。現在、10%オフとなるクーポンが配布されていました。アマゾンとかで買うよりもお得です。
