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