ExcelやPower BIでデータを取り込み・集計する際には前処理としてPower Queryでデータ型を定義したり、複数のデータテーブルを統合・結合したり、不要なデータを削除したりするわけですが、相対的な日付範囲のフィルタ（例えば当月末以前など）は詳細エディターにてExcel関数とは異なる言語「Power Query M 数式言語」を使って計算させる必要があります。
必要になったときに「こんな感じで出来た気がするなぁ」とググってもパッと出てこないので備忘として記事にしておきます。
この記事で出来るようになること
- Power Queryの数式バーまたは詳細エディタでフィルター式を書き換える
- 現在日時を基準に「今日より前」「今日以前」「今日より後」「今日以降」の日付フィルタを設定する
- 現在日時を基準に「当月末より前」「当月末以前」「当月末より後」「当月末以降」の日付フィルタを設定する
- 現在日時を基準に「翌月末より前」「翌月末以前」「翌月末より後」「翌月末以降」の日付フィルタを設定する
- 現在日時を基準に「前月末より前」「前月末以前」「前月末より後」「前月末以降」の日付フィルタを設定する
- カスタム列を作成して計算結果を確認する
チートシート
こまけぇこたぁいいんだよ!! って方向け。
[日付] の部分は対象とするフィールドの名前に応じて変更してください。
今日を基準に日付範囲を指定
今日より前
[日付] < DateTime.Date(DateTime.LocalNow())
今日以前
[日付] <= DateTime.Date(DateTime.LocalNow())
今日より後
[日付] > DateTime.Date(DateTime.LocalNow())
今日以降
[日付] >= DateTime.Date(DateTime.LocalNow())
当月を基準に日付範囲を指定
当月末より前
[日付] < DateTime.Date(Date.EndOfMonth(DateTime.LocalNow())))
当月末以前
[日付] < DateTime.Date(Date.EndOfMonth(DateTime.LocalNow())))
当月末より後
[日付] < DateTime.Date(Date.EndOfMonth(DateTime.LocalNow())))
当月末以降
[日付] < DateTime.Date(Date.EndOfMonth(DateTime.LocalNow())))
前月・翌月を基準に日付範囲を指定
前月末より前
[日付] < DateTime.Date(Date.EndOfMonth(Date.AddMonths(DateTime.LocalNow(), -1)))
前月末以前
[日付] <= DateTime.Date(Date.EndOfMonth(Date.AddMonths(DateTime.LocalNow(), -1)))
前月末より後
[日付] > DateTime.Date(Date.EndOfMonth(Date.AddMonths(DateTime.LocalNow(), -1)))
前月末以降
[日付] >= DateTime.Date(Date.EndOfMonth(Date.AddMonths(DateTime.LocalNow(), -1)))
翌月末より前
[日付] < DateTime.Date(Date.EndOfMonth(Date.AddMonths(DateTime.LocalNow(), 1)))
翌月末以前
[日付] <= DateTime.Date(Date.EndOfMonth(Date.AddMonths(DateTime.LocalNow(), 1)))
翌月末より後
[日付] > DateTime.Date(Date.EndOfMonth(Date.AddMonths(DateTime.LocalNow(), 1)))
翌月末以降
[日付] >= DateTime.Date(Date.EndOfMonth(Date.AddMonths(DateTime.LocalNow(), 1)))
こまけぇ説明
今回使った検証用テーブル
今回検証するにあたって以下のようなテーブルを作成しました。
| 日付 | 値 |
|---|---|
| 2020/08/31 | 先月末 |
| 2020/09/01 | 当月月初 |
| 2020/09/15 | 昨日 |
| 2020/09/16 | 今日 |
| 2020/09/17 | 明日 |
| 2020/09/30 | 当月末 |
| 2020/10/01 | 翌月月初 |
| 2020/10/31 | 翌月末 |
Excelでテーブルを作成してデータタブからテーブルまたは範囲からによってPower Queryに読み込ませています。試してみたい方は同様に作成してみてください。
数式を直接編集して「今日より前」という条件を設定してみる
日付フィルタのメニューから「今日」は設定できるが「今日より前」はできない
Power Queryの画面で日付フィールドの右にある▼をクリックして 日付/時刻フィルターを辿ると以下のような項目があり、日 を選択すれば「明日」「今日」「昨日」が選べる。
しかし、「今日より前」などの選択肢は無いのでここからは設定できない。
メニューになくても詳細エディターからより細かい条件指定ができる
Power Queryのステップ（処理内容）はExcelの数式バーと同様に直接数式を編集することが可能。
ここに日付を計算する各種関数を入れれば実現可能になる。
※詳細エディターからも編集可能ですが、1ステップを編集する場合は数式バー、複数ステップを編集する場合は詳細エディターからと使い分ければOKです。
まずは日付フィルタで「今日」を選択した場合どのような式になっているか確認してみましょう
= Table.SelectRows(変更された型, each Date.IsInCurrentDay([日付])) となっています。Date.IsInCurrentDay という関数で日付フィールドをチェックさせることで”今日”かどうかを判定させているようです。
次に指定の日より前の場合を見てみましょう
[日付] < #datetime(2020, 9, 16, 0, 0, 0) とあり、不等号で指定可能っぽい。
これなら #datetime(2020, 9, 16, 0, 0, 0) の部分で”今日”を計算させれば良さそうです。
今日 を計算する関数は DateTime.LocalNow関数というのがあるらしい
試しに #datetime(2020, 9, 16, 0, 0, 0) を置き換えてみると以下のとおり”今日”が表示されるようになった。あれ？ 「今日より前」ということは 2020/09/16 0:00:00 は含まれないはず。
というのも DateTime.LocalNow() は現在日時を算出するので計算結果は実行した瞬間の時刻情報も含んでいるため例えばこのクエリを15時に実行すると [日付] < 2020/09/16 15:00:00 として比較されているのです。
日付データのみに変換するにはDateTime.Date関数
DateTime.Date(DateTime.LocalNow()) のようにすることで日時情報を日付のみに変換することが可能です。
あれ、エラーが発生しました。このエラーは 日付 と 日時 という型の異なるデータを比較しようししたために発生したものです。今度は日付フィールドのデータからも時間情報を取り払ってやる必要がありそう。
フィールドの型変換はメニューから操作可能
日付フィールドを選択した状態で「変換」タブ内の「データ型：日付/時刻」をクリック。
「日付」を選択すれば日付型に変換してくれます。
このように0:00:00が消えていれば成功
再度DateTime.Date(DateTime.LocalNow())を適用してみると上手くいきました。
先程は表示されてしまった”今日”も消えており、きちんと「今日より前」になっているようです。
月末を計算する関数を組み合わせる
日時データを元に月末を算出する関数 Date.EndOfMonth()を使います。
前述の日付比較に組み合わせる場合は DateTime.Date(Date.EndOfMonth(DateTime.LocalNow()))) のように記述します。
※日付データでも日時データでも変換してくれますが、変換元のデータ型を維持する点には注意しましょう。例えば以下のようになります。
日付型のデータ 2020/09/16 を変換した場合は 2020/09/30
日時型のデータ 2020/09/16 0:00:00 を変換した場合は 2020-09-30T23:59:59.9999999
現在の月から前後の月を求めるにはAddMonths関数
基準となる日付または日時データに対して加算する月数を指定して使います。例えば現在の月から1ヶ月を求めたい場合はDate.AddMonths(DateTime.LocalNow(), 1)となります。
AddMonthsだと前月は計算できないと思うかもしれませんがマイナス値の指定が可能です
1 とすれば一ヶ月先になるのに対して、 -1 とすれば一ヶ月前を計算できるのでAddMonths関数を使えばどちらでも対応可能です。
【おまけ】計算式の結果を試しに見たい場合はカスタム列を使う
フィルター条件に設定する場合どんな計算結果になっているかが見えません。
正しく計算出来ているか確認するにはカスタム列を追加して確認しましょう。
「列の追加」タブ内の「カスタム列」をクリック→列名と計算式を入力してOK。
するとこのようにカスタム列が追加されて計算結果の値が確認できます
参考にしたページ
Power Query関連の記事ってあまりないんですよね。見つけるの下手なだけかもしれませんが。
