Excelのテーブルを元にPower Queryで日付テーブルを作る

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

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

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

#日付でカレンダーを作る

##Power Queryに取り込む準備
ワークシートに**[元テーブル]**という名前のテーブルを作り、列名は**[年月最小値][年月最大値]**とする。
レコードには日付形式でカレンダーの始点と終点を設定する。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/ec935200-d30b-defd-6213-7a367f752c03.png)

テーブルの何処かを選択した状態で**[データ]タブ-[取得と変換]-[テーブルから]**を選択する。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/e6016f39-bff3-7716-4591-bf108bdb6ada.png)

Power Queryに取り込まれたので、ここからカレンダーに変換していく。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/90a3ec11-2483-4a64-bf84-9fd07f5b45b3.png)

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

カレンダーを生成するには日付形式の方が都合が良いので、まずは**データ型**を設定する。
列名の左側をクリックし、出てきたメニューから**[日付]**を選択。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/baaec94e-cd24-de4e-74c9-6b479ad78d44.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/749ba7a9-f737-38ca-506b-73b9b3eed614.png)

両方の列にデータ型を設定したら次に進む。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/8350a303-b002-6f8e-e738-d00313c91101.png)

##カレンダーの生成

**[適用したステップ]**の一番下のステップを右クリックし、**[後にステップの挿入]**を選択。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/5c9c7253-f2c9-0c04-677c-150d15b928fd.png)
**[カスタム1]**というステップが追加される。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/d5316a45-cafc-4eb8-9afd-eaafd39799d5.png)
**数式バー**に以下の数式を入力。

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

**数式バー**はここ。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/7eced299-0964-8b6d-babd-17dc54fbc1ed.png)

今回使う**List.Dates関数**によるカレンダー生成は、Power BIの神が以下の記事で詳しく解説しています。
[Power Query の カスタム列の追加 を使わずに 日付テーブル を生成](https://qiita.com/PowerBIxyz/items/1203ec0e6cf31281a598)
公式リファレンスは此方。
[List.Dates - PowerQuery M | Microsoft Docs](https://docs.microsoft.com/en-us/powerquery-m/list-dates)

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

##テーブルに変換して完成
**List.Dates関数**はListを返すので、最後は**[テーブルへの変換]**を選んでテーブルにしたらフィニッシュ。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/c95fc97f-cd85-a355-3b00-c9df4b7340d4.png)

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

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

```PQ:日付でカレンダー
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
```

#年度でカレンダーを作る
日付入れるのが面倒な方向け。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/c1b26753-b4a9-5a4a-9ddd-67b2a437cbc7.png)


データが年度だと**日付型へのデータ加工**が必要になる。
それ以外は**[日付でカレンダーを作る]**と全く同じ。
##カレンダーで使うデータの準備
日付型へのデータ加工のため、**データ型をテキスト型に変更する。**
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/e2661c4e-39e9-f3bb-659a-024a20d388f3.png)

リボンの**[列の追加]**から**[カスタム列]**を選択。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/cfb92a67-85fd-0729-fc0e-fa1cc29024c9.png)

**[開始年度]**に**Date.FromText関数**を使い、日付形式に変換できるよう**月日の部分を付け加える。**
[Date.FromText - PowerQuery M | Microsoft Docs](https://docs.microsoft.com/en-us/powerquery-m/date-fromtext)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/797ba09e-31de-ebd9-177e-7816f32a6aa4.png)

出来たカスタム列を**日付形式に変換**する。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/2b004310-06a8-535c-dd95-ebd2419ed1ac.png)

同じ手順を**[終了年度]**にも行えば準備完了。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/b5847486-f945-16a2-8ea5-f34fb352238c.png)

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

##年度でカレンダーのコード
例によってテーブルさえ用意すれば、詳細エディターに張り付けて終わりです。

```PQ:年度でカレンダー
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時を回らないとやる気がでない現象をどうにかしたい。