Power Query の カスタム列の追加 を使わずに 日付テーブル を生成

Power BI や PowerPivot for Excel などで 日付テーブル を作ることはよくあるのだけど、Power Query で生成することが多い。簡単だしそこそこ複雑なことができるから。で、思っていたのが [カスタム列の追加]がちょっと面倒くさい。シンプルな操作ではあるけれどもステップが増えていく一方なので考え方を変えてみた。

# 日付テーブルとは
日付テーブルを例にするけれども、似たような構成が必要になったときでもよそうそうな気がする。

**日付テーブル**
日付列を持ち、その日付は日単位で連続していること。この日付テーブルとリレーションシップにより タイム インテリジェンス関数 (DAX) が機能する。なので必須でもあるディメンジョンテーブル。

|日付|年|月|日|
|:--:|:--:|:--:|:--:|
|...|...|...|...|
|2018-05-01|2018|5|1|
|2018-05-02|2018|5|2|
|...|...|...|...|
|2018-12-31|2018|12|31|
|2019-01-01|2019|1|1|
|...|...|...|...|

# Power Query
## 連続データの生成
連続データの生成にはいくつか方法があって、必要な連続データに合わせてどれかを使えばよい。
### .. 演算子
list(リスト)は、"{" "}"(波かっこ)を用いて定義できるが、連続する値リストになる定義として .. 演算子が利用できる。

```text:連続する値をもつlist
{-3 .. 3} = {3, 2, 1, 0, 1, 2, 3} // true

{"あ" .. "お"} = {"あ", "ぃ", "い", "ぅ", "う", "ぇ", "え", "ぉ", "お"} // true
```
number(数値)は Int32.Type(32bit整数型)、text(文字列)は 長さ1のtextをサポートする。
なので、連続した日付を持つ list をいきなり出力することができない。

```text:整数に変換してから、..演算子
{ Number.From( #date( 2018, 5, 1 ) ) .. Number.From( #date( 2018, 5, 4 ) ) }
```
で、あとから date(日付)に変換するなど。

### List.Dates 関数
連続した日付を持つ list(リスト)を出力する [List.Dates 関数 (Power Query)][List.Dates]

```text:構文：List.Dates
List.Dates(
    start as date,
    count as number,
    step as duration
) as list
```
連続した日付のstart(開始日付) に step(duration)を加算しながら、count(リストアイテム数)の list(リスト)を出力する。

```text
List.Dates( #date( 2018, 5, 1 ), 3, #duration( 1, 0, 0, 0 ) )
    = { #date( 2018, 5, 1 ), #date( 2018, 5, 2 ), #date( 2018, 5, 3 ) } // true

List.Dates( #date( 2018, 5, 1 ), 3, #duration( 2, 0, 0, 0 ) )
    = { #date( 2018, 5, 1 ), #date( 2018, 5, 3 ), #date( 2018, 5, 5 ) } // true

List.Dates( #date( 2018, 5, 1 ), 3, #duration( 0, 12, 0, 0 ) )
    = { #date( 2018, 5, 1 ), #date( 2018, 5, 1 ), #date( 2018, 5, 2 ) } // true 
```

step(duration)に "日"単位以外を指定してもdate(日付)のlist(リスト)に。
必要な日付値のアイテム数を指定するのには便利なのかも。開始と終了から日付リストを得る場合、アイテム数を計算して求める感じになる。

```text:開始と終了の日付から
let
    StartDate = #date( 2018, 5, 1 ), EndDate = #date( 2018, 5, 3 ),
    Dates = List.Dates(
        StartDate,
        Duration.Days( EndDate - StartDate ) + 1,
        #duration( 1, 0, 0, 0 )
    )
in
    Dates
```
### List.Generate 関数
[List.Generate 関数 (Power Query)][List.Generate]

```text:構文：List.Generate
List.Generate(
    initial as function,
    condition as function,
    next as function,
    optional selector as nullable function
) as list
```
initial を初期値として扱い、condition が true になるまで next を繰り返す。selector で出力されるリストアイテムを変換する。最初はとっつきにくいけど慣れるととても便利。

```text
List.Generate( () => #date( 2018, 5, 1 ), each _ <= #date( 2018, 5, 3 ), each _ + #duration( 1, 0, 0, 0 ) )
    = { #date( 2018, 5, 1 ), #date( 2018, 5, 2 ), #date( 2018, 5, 3 ) } // true
```

## List.Generate 推し
カスタム列の追加のような処理も [List.Generate (Power Query) 関数][List.Generate] に任せる方針にする

### 引数はスカラ値じゃなくてもよい
サンプルでは 日付を表す #date( 2018, 5, 1 ) を使ったけれども、スカラ値でなくてもよいので record を使用。
List.Generate の next の処理で record を繰り返し list にする算段

```text
let
    StartDate = #date( 2018, 5, 1 ), EndDate = #date( 2018, 5, 7 ),
    Dates = List.Generate(
        () => [日付 = StartDate],
        each [日付] <= EndDate,
        each [日付 = [日付] + #duration( 1, 0, 0, 0 )]
    ),
    TableFromRecords = Table.FromRecords( Dates )
in
    TableFromRecords
```

record なので[カスタム列の追加]に相当する複数の Field を用意

```text
let
    StartDate = #date( 2018, 5, 1 ), EndDate = #date( 2018, 5, 7 ),
    Dates = List.Generate(
        () => [
                  日付 = StartDate,
                  年 = Date.Year( 日付 ),
                  月 = Date.Month( 日付 ),
                  日 = Date.Day( 日付 )
              ],
        each [日付] <= EndDate,
        each
            [
                日付 = [日付] + #duration( 1, 0, 0, 0 ),
                年 = Date.Year( 日付 ),
                月 = Date.Month( 日付 ),
                日 = Date.Day( 日付 )
            ]
    ),
    TableFromRecords = Table.FromRecords( Dates )
in
    TableFromRecords
```

initial と next に使用した record の定義は同じする必要もあるので関数化
[カスタム列の追加]に相当する Field を record に追加しておくだけ。

```text
let
    StartDate = #date( 2018, 5, 1 ), EndDate = #date( 2018, 5, 7 ),
    DateRecord = 
        ( date as date ) as record =>
            [   // このrecord に field を追加していけばよい
                日付 = date,
                年 = Date.Year( date ),
                月 = Date.Month( date ),
                日 = Date.Day( date ),
                IsWeekEnd = List.Contains( 
                    { Day.Sunday, Day.Saturday},
                    Date.DayOfWeek( date )
                ) 
            ],
    Dates = List.Generate(
        () => DateRecord( StartDate ),
        each [日付] <= EndDate,
        each DateRecord( [日付] + #duration( 1, 0, 0, 0 ) )
    ),
    TableFromRecords = Table.FromRecords( Dates )
in
    TableFromRecords
```
![image.png](https://qiita-image-store.s3.amazonaws.com/0/193509/363f0c3d-04bc-ad9b-fe34-2865516e1e24.png)
ステップを増やさず列の追加ができてよいかなと考えてはいる。ただ、データ型の判定と指定はそこそこ面倒なので手作業でよいかと。

### データ型を予め定義することも
列のデータ型を予め定義したり、Loop の判定に使用する field名 に依存しないこととか。

```text:列のデータ型を予め定義したクエリ
let
    StartDate = #date( 2018, 1, 1 ), EndDate = #date( 2019, 12, 31 ),
    DateRecord = 
        ( date as date ) as record =>
            [
                _date = date,
                日付 = date,
                年 = Date.Year( date ),
                月 = Date.Month( date ),
                日 = Date.Day( date ),
                IsWeekEnd = List.Contains( 
                    { Day.Sunday, Day.Saturday},
                    Date.DayOfWeek( date )
                )
            ],
    ColumnsType =
        type table
            [
                日付 = Date.Type,
                年 = Int64.Type,
                月 = Int64.Type,
                日 = Int64.Type,
                IsWeekEnd = Logical.Type
            ],
    DateFieldValue = 
        (record as record) as date => record[_date] ,
    Dates = List.Generate(
        () => DateRecord( StartDate ),
        each DateFieldValue( _ ) <= EndDate,
        each DateRecord( DateFieldValue( _ ) + #duration( 1, 0, 0, 0 ) )
    ),
    TableFromRecords = Table.FromRecords( Dates, ColumnsType )
in
    TableFromRecords
```
![image.png](https://qiita-image-store.s3.amazonaws.com/0/193509/b32db1a3-3897-caee-9795-90e3a51be080.png)


# その他
- [Power BI 勉強会 - connpass](https://powerbi.connpass.com/)
- [Power BI Desktop を使い始めるには](http://qiita.com/PowerBIxyz/items/6c4735cb5b5712e70981)
- [Excel で Power Query を使い始めるには](http://qiita.com/PowerBIxyz/items/52e563037b855a099ca9)


[List.Dates]:https://msdn.microsoft.com/en-us/query-bi/m/list-dates
[List.Generate]:https://msdn.microsoft.com/en-us/query-bi/m/list-generate