Power BIだとDAXで手軽に日付テーブルを作れる。
しかしExcelだとPower Pivot標準の日付テーブルを利用するか、Power Queryで作る必要がある。

日付テーブルに必要なパラメーターやPower Pivotの操作を説明するのも結構面倒。
なのでExcelでテーブルを作り、そこで指定した日付範囲を基にPower Queryで日付テーブルを作るやり方を書きます。

最近仕事でExcelを使わざるを得ない事も多いので備忘も兼ねる。

日付でカレンダーを作る

Power Queryに取り込む準備

ワークシートに**[元テーブル]という名前のテーブルを作り、列名は[年月最小値][年月最大値]**とする。
レコードには日付形式でカレンダーの始点と終点を設定する。

テーブルの何処かを選択した状態で**[データ]タブ-[取得と変換]-[テーブルから]**を選択する。

Power Queryに取り込まれたので、ここからカレンダーに変換していく。

カレンダーで使うデータの準備

カレンダーを生成するには日付形式の方が都合が良いので、まずはデータ型を設定する。
列名の左側をクリックし、出てきたメニューから**[日付]**を選択。

両方の列にデータ型を設定したら次に進む。

カレンダーの生成

**[適用したステップ]の一番下のステップを右クリックし、[後にステップの挿入]**を選択。

**[カスタム1]**というステップが追加される。

数式バーに以下の数式を入力。

= List.Dates(
        変更された型[年月最小値]{0}
      , Number.From(変更された型[年月最大値]{0} - 変更された型[年月最小値]{0}) + 1
      , #duration(1, 0, 0, 0)
    )

数式バーはここ。

今回使うList.Dates関数によるカレンダー生成は、Power BIの神が以下の記事で詳しく解説しています。
Power Query のカスタム列の追加を使わずに日付テーブルを生成
公式リファレンスは此方。
List.Dates - PowerQuery M | Microsoft Docs

List.Dates関数で使った変更された型[年月最小値]{0}はステップ名[列名]{列のN番目の要素}という意味。
ここではステップ[変更された型]の[年月最小値]列の0番目を値を取ってくるという動作になる。
好きなステップからデータを取り出せるので、覚えておいて損はない。

テーブルに変換して完成

List.Dates関数はListを返すので、最後は**[テーブルへの変換]**を選んでテーブルにしたらフィニッシュ。

後はPower Query上で加工するなり、Excelデータモデルに読み込んでDAXで計算列を作るなり、好きに使いましょう。

日付でカレンダーのコード

~~自分用~~面倒な方向け。
テーブル名と列名さえ同じならこれを詳細エディターに張り付ければ終わりです。

日付でカレンダー

let
    ソース = Excel.CurrentWorkbook(){[Name="元テーブル"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"年月最小値", type date}, {"年月最大値", type date}}),
    Dates = List.Dates(
        変更された型[年月最小値]{0}
      , Number.From(変更された型[年月最大値]{0} - 変更された型[年月最小値]{0}) + 1
      , #duration(1, 0, 0, 0)
    ),
    ConvertedToTable = Table.FromList(
        Dates
      , Splitter.SplitByNothing()
      , type table [日付 = date]
    )
in
    ConvertedToTable

年度でカレンダーを作る

日付入れるのが面倒な方向け。

データが年度だと日付型へのデータ加工が必要になる。
それ以外は**[日付でカレンダーを作る]**と全く同じ。

カレンダーで使うデータの準備

日付型へのデータ加工のため、データ型をテキスト型に変更する。

リボンの**[列の追加]から[カスタム列]**を選択。

[開始年度]にDate.FromText関数を使い、日付形式に変換できるよう月日の部分を付け加える。
Date.FromText - PowerQuery M | Microsoft Docs

出来たカスタム列を日付形式に変換する。

同じ手順を**[終了年度]**にも行えば準備完了。

カレンダーの生成

ここから先は**[日付でカレンダーを作る]**と同じなので割愛。

年度でカレンダーのコード

例によってテーブルさえ用意すれば、詳細エディターに張り付けて終わりです。

年度でカレンダー

let
    ソース = Excel.CurrentWorkbook(){[Name="元テーブル"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"開始年度", type text}, {"終了年度", type text}}),
    追加されたカスタム = Table.AddColumn(変更された型, "年月最小値", each Date.FromText([開始年度]&"0401")),
    変更された型1 = Table.TransformColumnTypes(追加されたカスタム,{{"年月最小値", type date}}),
    追加されたカスタム1 = Table.AddColumn(変更された型1, "年月最大値", each Date.FromText([終了年度]&"0331")),
    変更された型2 = Table.TransformColumnTypes(追加されたカスタム1,{{"年月最大値", type date}}),
    Dates = List.Dates(
        変更された型2[年月最小値]{0}
      , Number.From(変更された型2[年月最大値]{0} - 変更された型2[年月最小値]{0}) + 1
      , #duration(1, 0, 0, 0)
    ),
    ConvertedToTable = Table.FromList(
        Dates
      , Splitter.SplitByNothing()
      , type table [日付 = date]
    )
in
    ConvertedToTable

まとめ

Excelワークシート上からでもクエリを操作できる。
- 一々データの更新が必要ですが、What-ifも出来ます。ExcelにはPower BIほど柔軟性は無いです…。
Power Queryでデータを加工する際にはデータ型に注意。
- 関数は引数にデータ型が指定されている事があるので、リファレンスでよく確認すると予期せぬエラーを減らせます。
[後にステップの挿入]や[カスタム列]を使うと幅が広がる。
- 慣れてくるとGUIでポチポチだけでは物足りなくなってくるので、積極的に使ってみてください。

あー仕事でExcelじゃなくて,Power Platformを触りたい(´・ω・｀)
後、午前0時を回らないとやる気がでない現象をどうにかしたい。