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時を回らないとやる気がでない現象をどうにかしたい。