Aさん 特定の日付列から月末の日付を計算したいです。
Power Queryエディター上で日付の月の最終日を計算したい場合、どうしたら良いですか？
森田 その場合、M関数の「Date.EndOfMonth」を使えば良いですよ！
では、詳細を解説していきますね。
はじめに
この記事は「カスタム列」コマンドの詳細を把握していることが前提です。
「カスタム列」コマンドの詳細は、以下の記事をご参照ください。
はじめに この記事はパワークエリの概要を把握していることが前提です。 参考記事 パワークエリの概要については、以下の記事をご参照ください。 「カスタム列」の使いどころ 「カスタム列」コマンドは、クエリの新規作成ステップの …
「Date.EndOfMonth」の使いどころ
契約満了日を計算する等、特定の日付列を基準に月の最終日を計算したいケースがあります。
Power Queryエディター上で日付列から指定の月の最終日の日付列を作成したい場合、「カスタム列」ダイアログ上で「Date.EndOfMonth」を活用しましょう。
Date.EndOfMonthを活用することで、選択した日付列から月の最終日の日付列を作成できます。
構文
Date.EndOfMonthの構文は以下の通りです。
Date.EndOfMonth ( dateTime )
月を最後の日付を返します。
| 引数名 | 必須 | データ型 | 説明 |
|---|---|---|---|
| dateTime | ○ | 日付、日付/時刻、日付/時刻/タイムゾーン | 開始日を示す日付列・日付/時刻列や定数を指定します。 |
引数「dateTime」がnullの場合、Date.EndOfMonthの戻り値もnullを表示。
引数「numberOfMonths」に数値以外を指定した場合、Expression.Error表示。
必要に応じ、Microsoft Learnの「Date.EndOfMonth」の記事もご参照ください。
詳細については、以下をご覧ください。Date.EndOfMonth
数式例・使用イメージ
「入社3か月後日付」列から「契約満了日」列を作成したイメージが以下です。
なお、この列のデータ型は、後続のステップの内容に応じ、「日付」・「日付/時刻」・「日付/時刻/タイムゾーン」のいずれかへ変更してください。
また、カスタム列で参照している日付列は不要であれば別途削除してください。
ステップ登録手順
「カスタム列」コマンドでDate.EndOfMonthを登録したい場合は、以下の手順となります。
- リボン「列の追加」タブをクリック
- 「カスタム列」をクリック
- 任意の列名を入力
※今回は「契約満了日」 - 「d」を入力
- サジェストから「Date.EndOfMonth」を選択し、「Tab」キーで確定
- 「(」を入力
- 開始日を示す日付列を選択
※今回は「入社3か月後日付」列 - 「)」を入力
- 「OK」をクリック
M関数以外に「月の最終日」コマンドでも代替可能（日付列を選択→リボン「列の追加」タブ→「日付」→「月」→「月の最終日」の順にクリック）。
【応用】月数と最終日の計算を1ステップで行うには
入社日から契約満了日を計算する等、特定の日付列を基準にNヶ月後の月の最終日を計算したいケースが実務では起こり得ます。
しかし、Power Queryエディターには、月数の計算と最終日の計算を同時に行うコマンドはありません。
ワークシート上の関数でいう「EOMONTH」と完全に同じ機能を持ったコマンドがないということですね。
この場合、「カスタム列」ダイアログ上の数式で「Date.EndOfMonth」と「Date.AddMonths」を組み合わせれば、EOMONTHと同じことをPower Queryエディター上の1ステップで実現できます。
月数の計算はDate.AddMonths、最終日の計算はDate.EndOfMonthで役割分担を行うイメージですね。
M関数の「Date.AddMonths」とワークシート上の関数の「EOMONTH」の詳細は、以下の記事をご参照ください。
はじめに この記事は「カスタム列」コマンドの詳細を把握していることが前提です。 参考記事 「カスタム列」コマンドの詳細は、以下の記事をご参照ください。 「Date.AddMonths」の使いどころ 入社日から本採用日を計 …
はじめに この記事は関数の概要とDATE・YEAR・MONTHの使い方を把握していることが前提です。 参考記事 関数の概要とDATE・YEAR・MONTHの使い方については以下の記事をご参照ください。 開始日を起点に数か …
登録したステップの変更手順
Date.EndOfMonthのステップを後から変更する場合、以下の手順となります。
- 「追加されたカスタム列」ステップの歯車マークをクリック
- 任意の箇所を修正
- 「OK」をクリック
手順①のステップ名は「カスタム列」コマンドのデフォルトの名前（自身でリネームしている場合は別表記）。
手順①はステップ名をダブルクリックでもOK。
手順①で起動した「カスタム列」ダイアログは、ステップに設定した内容がセットされた状態。
サンプルファイルで練習しよう！
可能であれば、以下のサンプルファイルをダウンロードして、実際に操作練習をしてみてください。
サンプルファイル_PowerQueryデータ整形_Date.EndOfMonth.xlsx
サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります（上記リンクから登録フォームへ遷移します）。
ブックを開いたら、「社員マスタ」クエリを編集（Power Queryエディター起動）し、次の手順を実施してください（今までの解説のまとめです）。
ブック起動時、「セキュリティの警告」メッセージが表示された場合は「コンテンツの有効化」をクリック。
- リボン「列の追加」タブをクリック
- 「カスタム列」をクリック
- 任意の列名を入力
※今回は「契約満了日」 - 「d」を入力
- サジェストから「Date.EndOfMonth」を選択し、「Tab」キーで確定
- 「(」を入力
- 開始日を示す日付列を選択
※今回は「入社3か月後日付」列 - 「)」を入力
- 「OK」をクリック
本記事の解説と同じ結果になればOKです！
さいごに
いかがでしたでしょうか？
Date.EndOfMonthを活用することで、選択した日付列から月の最終日の日付列を作成できます。
一連の整形作業の中で、日付列の月の最終日を計算した日付列を作成したい場合に活用してみましょう！
なお、パワークエリの各種テクニックは、私の拙著でも体系的に解説していますので、こちらも参考にしてみてください。
ご参考になれば幸いですm(_ _)m
森田 Date.EndOfMonthに慣れたら、他の日付列の作成や計算に役立つM関数もチャレンジしてみましょう。
具体的には、年/月/日を示す3列から日付列を作成できる「#date」や、日付に指定の月数を加減算できる「Date.AddMonths」、日付に指定の日数を加減算できる「Date.AddDays」等です。
他にどんなM関数があるかは、Microsoft Learnの「Power Query M関数参照」も参照してみてくださいね！
![ピボットテーブルをドリルスルーし、詳細データを確認する方法（詳細の表示） [ピボットテーブル基礎]](https://i0.wp.com/excel-master.net/wp-content/uploads/2021/08/%E3%83%94%E3%83%9C%E3%83%83%E3%83%88%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB_%E8%A9%B3%E7%B4%B0%E3%81%AE%E8%A1%A8%E7%A4%BA1.png?fit=750%2C422&ssl=1)