【PowerQuery】関数だと簡単にできるのにパワークエリではどうやるんだ?を集めた②日付編

１．はじめに
２．日付関連のエクセル関数をM関数でやると
３．最後に
４．参考文献、記事

１．はじめに

パワークエリ1年生の苦悩シリーズ。

エクセル関数だとチャッとできるのに、パワークエリだとどうやるんだ？の、（自分が）よく使うものを集めましたシリーズ2回目。悲しいくらいに忘れるので、毎回どうやるんだっけ？→調べる、と時間がかかるので、ここに書いておく。

なお、クリック操作でやる方法ではなく、関数を書くほうが好みなので関数でやる方法を主に書いています。

ちなみになぜ関数で書くほうを好むかというと
・関数を知っていると、クリック操作でやるよりステップ数を短くできる場合がある
・関数を知っていると、複雑な要求もパワークエリ内で処理できる場合がある
・クリック操作でやるよりなんかデキル人っぽい感じがする
ような気がする、からです。

今回は日付関連を中心にやっています。
Date関数
 DateTime 関数

２．日付関連のエクセル関数をM関数でやると

（１）今現在（年月日時分秒）

エクセル関数だと(あんまり使わないけど）
=NOW()
→ 2024/4/7 13:36:14

パワークエリだと
=DateTime.LocalNow()
→ 2024/4/7 13:36:14

DateTime.LocalNow

（２）今日（日付）

エクセル関数だと
=TODAY()
→ 2024/4/7

パワークエリだと、「今現在」をだして、そこから日付を抽出する
=Date.From(DateTime.LocalNow())
→ 2024/4/7

または

= DateTime.Date(DateTime.LocalNow())
→ 2024/4/7

Date.From
DateTime.Date

（３）昨日（一日前の日付）

エクセル関数だと、日付は普通に引き算できます。
=Today() – 1
→2024/4/6

パワークエリだと、日付型と数値型の計算がエラーになるので以下のようにやるといいようです。
= Date.From(DateTime.LocalNow()) – #duration(1,0,0,0)

（４）翌日（一日後）

パワークエリだと、
= Date.From(DateTime.LocalNow()) + #duration(1,0,0,0)

（５）期間型ってなんだ？

一日前などを算出するのに使用した duration 。

#duration は「期間」
エクセル使いにはなじみのない概念。。。日、時間、分、秒で日付（時間含む）間隔をあらわす単位。。。？
で、1日分(の間隔)をあらわすには #duration(1,0,0,0)
1時間30分（の間隔）をあらわすには #duration(0,1,30,0)

#duration

構文
#duration(days as number, hours as number, minutes as number, seconds as number) as duration

日、時間、分、(小数) 秒を表す数値から期間値を作成します。
引用元：https://learn.microsoft.com/ja-jp/powerquery-m/sharpduration

期間とは？

期間型
時間の長さを表します。モデルに読み込まれる際に 10 進数型に変換されます。 10 進数型であれば、日付/時刻フィールドと加算または減算を行ったとき、正しい結果が得られます。 10 進数型であるため、視覚化のときに大きさを示すために使用しやすくなります。
引用元：https://learn.microsoft.com/ja-jp/power-query/data-types

「日付」列 2024/4/7 0:00:00
一日前：[日付] – #duration(1,0,0,0)
一日後：[日付] + #duration(1,0,0,0)
1時間30分後：[日付] + #duration(0,1,30,0)
90分後：[日付] + #duration(0,0,90,0)
分に90を突っ込むと、自動で繰り上げして1時間30分後を出してくれた。

鷹尾先生の記事で勉強させていただきました。
PowerQuery 日付型データ② （計算）

（６）月末を出す

エクセル関数だと（今日が2024/4/7の場合）
当月末：EOMONTH(TODAY(),0) → 2024/4/30
先月末：EOMONTH(TODAY(),-1) → 2024/3/31
翌月末：EOMONTH(TODAY(),1) → 2024/5/31
とか

パワークエリだと（今日が2024/4/7の場合）
当月末
Date.EndOfMonth(Date.From(DateTime.LocalNow()))
→2024/4/30
先月末
Date.EndOfMonth(Date.AddMonths(Date.From(DateTime.LocalNow()),-1))
→2024/3/31
翌月末
Date.EndOfMonth(Date.AddMonths(Date.From(DateTime.LocalNow()),1))
→2024/5/31

ちなみに Date.AddMonths の場所を間違えるとおかしくなる場合がありますのでご注意。
Date.AddMonths(Date.EndOfMonth(Date.From(DateTime.LocalNow())),1)
→2024/05/30

Date.EndOfMonth
Date.AddMonths

（７）月初の日付

エクセル関数だと（今日の日付が2024/4/7の場合)
当月1日：DATE(YEAR(TODAY()),MONTH(TODYA()),1) → 2024/4/1
先月1日：DATE(YEAR(TODAY()),MONTH(TODYA())-1,1) → 2024/4/1
翌月1日：DATE(YEAR(TODAY()),MONTH(TODYA())+1,1) → 2024/4/1
とか。EOMONTHを使ったり、日付で計算したり、他にも方法がいろいろありますが。

パワークエリだと
当月1日
Date.StartOfMonth(Date.From(DateTime.LocalNow()))
→2024/4/1
先月1日
Date.StartOfMonth(Date.AddMonths(Date.From(DateTime.LocalNow()),-1))
→2024/3/1
翌月1日
Date.StartOfMonth(Date.AddMonths(Date.From(DateTime.LocalNow()),1))
→2024/5/1

やはりほかにもいろいろ方法はあるようですが、これが簡単そう。

ちなみに、日付時刻型のままEndOfMonthをやったらおかしなことになったので（繰り上がった？）、元の値が日付型でやるか、Date.Fromなどで日付型に変換してからやったほうが無難そう。
ちなみに下図の結果も「月末」列を日付型に変換すると、4/30になりました。よくわからん。

Date.StartOfMonth

（８）年月日を取り出し

エクセル関数だと（2024/4/7の場合）
年:YEAR(TODAY()) → 2024
月:MONTH(TODAY()) → 4
日:DAY(TODAY()) → 7

パワークエリだと（2024/4/7の場合）
年:Date.Year(DateTime.LocalNow()) → 2024
月:Date.Month(DateTime.LocalNow()) → 4
日:Date.Day(DateTime.LocalNow()) → 7

Date.Year
Date.Month
Date.Day
数値が返されます。

（９）日付をyyyymmddのような文字列または数値に

2024/4/7 を 20240407 のような数値にする場合

エクセル関数だと
文字列にする場合：TEXT(Today(),”yyyymmdd”)
数値にする場合：VALUE(TEXT(Today(),”yyyymmdd”))
他にも方法はありますが、これが簡単かなと思いましたので。

パワークエリだと
文字列にする場合
= Date.ToText(Date.From(DateTime.LocalNow()),”yyyyMMdd”)
数値にする場合
= Number.From(Date.ToText(Date.From(DateTime.LocalNow()),”yyyyMMdd”))
ほかにも方法はあるだろうけど。これが単純かも。
yyyyMMdd の月の MM が大文字になるので注意。

Date.ToText
Number.From
Number.FromText