自動化厨のプログラミングメモブログ │ CODE:LIFE

Python/ExcelVBA/JavaScript/Raspberry Piなどで色んなことを自動化

Power Queryで相対的な日付範囲フィルタを設定する計算式チートシートと解説

ExcelやPower BIでデータを取り込み・集計する際には前処理としてPower Queryでデータ型を定義したり、複数のデータテーブルを統合・結合したり、不要なデータを削除したりするわけですが、相対的な日付範囲のフィルタ(例えば当月末以前など)は詳細エディターにてExcel関数とは異なる言語「Power Query M 数式言語」を使って計算させる必要があります。

必要になったときに「こんな感じで出来た気がするなぁ」とググってもパッと出てこないので備忘として記事にしておきます。

この記事で出来るようになること

  • Power Queryの数式バーまたは詳細エディタでフィルター式を書き換える
  • 現在日時を基準に「今日より前」「今日以前」「今日より後」「今日以降」の日付フィルタを設定する
  • 現在日時を基準に「当月末より前」「当月末以前」「当月末より後」「当月末以降」の日付フィルタを設定する
  • 現在日時を基準に「翌月末より前」「翌月末以前」「翌月末より後」「翌月末以降」の日付フィルタを設定する
  • 現在日時を基準に「前月末より前」「前月末以前」「前月末より後」「前月末以降」の日付フィルタを設定する
  • カスタム列を作成して計算結果を確認する


チートシート

こまけぇこたぁいいんだよ!! って方向け。

[日付] の部分は対象とするフィールドの名前に応じて変更してください。

今日を基準に日付範囲を指定

今日より前

[日付] < DateTime.Date(DateTime.LocalNow())

f:id:maru0014:20200916230516p:plain

今日以前

[日付] <= DateTime.Date(DateTime.LocalNow())

f:id:maru0014:20200916230532p:plain

今日より後

[日付] > DateTime.Date(DateTime.LocalNow())

f:id:maru0014:20200916230541p:plain

今日以降

[日付] >= DateTime.Date(DateTime.LocalNow())

f:id:maru0014:20200916230549p:plain

当月を基準に日付範囲を指定

当月末より前

[日付] < DateTime.Date(Date.EndOfMonth(DateTime.LocalNow())))

f:id:maru0014:20200916230600p:plain

当月末以前

[日付] < DateTime.Date(Date.EndOfMonth(DateTime.LocalNow())))

f:id:maru0014:20200916230608p:plain

当月末より後

[日付] < DateTime.Date(Date.EndOfMonth(DateTime.LocalNow())))

f:id:maru0014:20200916230628p:plain

当月末以降

[日付] < DateTime.Date(Date.EndOfMonth(DateTime.LocalNow())))

f:id:maru0014:20200916230635p:plain

前月・翌月を基準に日付範囲を指定

前月末より前

[日付] < DateTime.Date(Date.EndOfMonth(Date.AddMonths(DateTime.LocalNow(), -1)))

f:id:maru0014:20200916230647p:plain

前月末以前

[日付] <= DateTime.Date(Date.EndOfMonth(Date.AddMonths(DateTime.LocalNow(), -1)))

f:id:maru0014:20200916230657p:plain

前月末より後

[日付] > DateTime.Date(Date.EndOfMonth(Date.AddMonths(DateTime.LocalNow(), -1)))

f:id:maru0014:20200916230706p:plain

前月末以降

[日付] >= DateTime.Date(Date.EndOfMonth(Date.AddMonths(DateTime.LocalNow(), -1)))

f:id:maru0014:20200916230715p:plain

翌月末より前

[日付] < DateTime.Date(Date.EndOfMonth(Date.AddMonths(DateTime.LocalNow(), 1)))

f:id:maru0014:20200916230725p:plain

翌月末以前

[日付] <= DateTime.Date(Date.EndOfMonth(Date.AddMonths(DateTime.LocalNow(), 1)))

f:id:maru0014:20200916230733p:plain

翌月末より後

[日付] > DateTime.Date(Date.EndOfMonth(Date.AddMonths(DateTime.LocalNow(), 1)))

f:id:maru0014:20200916230741p:plain

翌月末以降

[日付] >= DateTime.Date(Date.EndOfMonth(Date.AddMonths(DateTime.LocalNow(), 1)))

f:id:maru0014:20200916230749p:plain

こまけぇ説明

今回使った検証用テーブル

今回検証するにあたって以下のようなテーブルを作成しました。

日付
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に読み込ませています。試してみたい方は同様に作成してみてください。

f:id:maru0014:20200916230800p:plain

数式を直接編集して「今日より前」という条件を設定してみる

日付フィルタのメニューから「今日」は設定できるが「今日より前」はできない

Power Queryの画面で日付フィールドの右にある▼をクリックして 日付/時刻フィルターを辿ると以下のような項目があり、日 を選択すれば「明日」「今日」「昨日」が選べる。

しかし、「今日より前」などの選択肢は無いのでここからは設定できない。

f:id:maru0014:20200916230818p:plain

メニューになくても詳細エディターからより細かい条件指定ができる

Power Queryのステップ(処理内容)はExcelの数式バーと同様に直接数式を編集することが可能。

ここに日付を計算する各種関数を入れれば実現可能になる。

※詳細エディターからも編集可能ですが、1ステップを編集する場合は数式バー、複数ステップを編集する場合は詳細エディターからと使い分ければOKです。

まずは日付フィルタで「今日」を選択した場合どのような式になっているか確認してみましょう

= Table.SelectRows(変更された型, each Date.IsInCurrentDay([日付])) となっています。Date.IsInCurrentDay という関数で日付フィールドをチェックさせることで”今日”かどうかを判定させているようです。

f:id:maru0014:20200916230829p:plain


次に指定の日より前の場合を見てみましょう

[日付] < #datetime(2020, 9, 16, 0, 0, 0) とあり、不等号で指定可能っぽい。

これなら #datetime(2020, 9, 16, 0, 0, 0) の部分で”今日”を計算させれば良さそうです。

f:id:maru0014:20200916230846p:plain


今日 を計算する関数は DateTime.LocalNow関数というのがあるらしい

試しに #datetime(2020, 9, 16, 0, 0, 0) を置き換えてみると以下のとおり”今日”が表示されるようになった。あれ? 「今日より前」ということは 2020/09/16 0:00:00 は含まれないはず。

というのも DateTime.LocalNow() は現在日時を算出するので計算結果は実行した瞬間の時刻情報も含んでいるため例えばこのクエリを15時に実行すると [日付] < 2020/09/16 15:00:00 として比較されているのです。

f:id:maru0014:20200916230916p:plain


日付データのみに変換するにはDateTime.Date関数

DateTime.Date(DateTime.LocalNow()) のようにすることで日時情報を日付のみに変換することが可能です。

あれ、エラーが発生しました。このエラーは 日付 と 日時 という型の異なるデータを比較しようししたために発生したものです。今度は日付フィールドのデータからも時間情報を取り払ってやる必要がありそう。

f:id:maru0014:20200916230926p:plain


フィールドの型変換はメニューから操作可能

日付フィールドを選択した状態で「変換」タブ内の「データ型:日付/時刻」をクリック。

「日付」を選択すれば日付型に変換してくれます。

f:id:maru0014:20200916230935p:plain


このように0:00:00が消えていれば成功

f:id:maru0014:20200916230943p:plain


再度DateTime.Date(DateTime.LocalNow())を適用してみると上手くいきました。

先程は表示されてしまった”今日”も消えており、きちんと「今日より前」になっているようです。

f:id:maru0014:20200916230951p:plain


月末を計算する関数を組み合わせる

日時データを元に月末を算出する関数 Date.EndOfMonth()を使います。

前述の日付比較に組み合わせる場合は DateTime.Date(Date.EndOfMonth(DateTime.LocalNow()))) のように記述します。

f:id:maru0014:20200916230959p:plain


※日付データでも日時データでも変換してくれますが、変換元のデータ型を維持する点には注意しましょう。例えば以下のようになります。

日付型のデータ 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)となります。

f:id:maru0014:20200916231007p:plain


AddMonthsだと前月は計算できないと思うかもしれませんがマイナス値の指定が可能です

1 とすれば一ヶ月先になるのに対して、 -1 とすれば一ヶ月を計算できるのでAddMonths関数を使えばどちらでも対応可能です。

f:id:maru0014:20200916231017p:plain


【おまけ】計算式の結果を試しに見たい場合はカスタム列を使う

フィルター条件に設定する場合どんな計算結果になっているかが見えません。

正しく計算出来ているか確認するにはカスタム列を追加して確認しましょう。

「列の追加」タブ内の「カスタム列」をクリック→列名と計算式を入力してOK。

f:id:maru0014:20200916231025p:plain

するとこのようにカスタム列が追加されて計算結果の値が確認できます

f:id:maru0014:20200916231034p:plain

参考にしたページ

Power Query関連の記事ってあまりないんですよね。見つけるの下手なだけかもしれませんが。

日付関数 | Microsoft Docs - PowerQuery M

DateTime 関数 - PowerQuery M

Power Query を使って絞り込んだデータを取得する - 日付編