Power Query workout - List.Generate

関数の使い方のような勉強スタイルだと、説明にある類似の使い方にとどまってしまうことがある。なので、

- どのように動作しているのか
- どのように結果を得られるのか

を理解しておくとよいはずで、理屈がわからないハウツーは参考にとどめるべきだと思うのです。

List.Generate 関数では連続するデータを出力するというケースで使うことが多いのだけど、定義した *condition* が *true* である限り繰り返し、直前に生成されたアイテムを参照し生成するという機能をどのように利用するかはアイデア次第。

# ウォームアップ
## Lv.1 基礎 of 基礎
https://docs.microsoft.com/en-us/powerquery-m/list-generate

**構文と例**

```text:List.Generate&#x20;(Power&#x20;Query)
List.Generate(
    initial as function,
    condition as function,
    next as function,
    optional selector as nullable function
) as list
```

```text:Power&#x20;Query
List.Generate(
    ()=> 1,
    each _ <= 5,
    each _ + 1
) = { 1, 2, 3, 4, 5 } // true
```

*each* キーワードを使用しない記述で理解できるかもしれない。

```text:Power&#x20;Query
List.Generate(
    ()=> 1,
    (current)=> current <= 5,
    (current)=> current + 1
) = { 1, 2, 3, 4, 5 } // true
```

record で取り扱うことが多いのは、バリエーションを表現しやすいから。

```text:Power&#x20;Query
List.Generate(
    ()=> [Date = #date(2021, 12, 25), Weekday = "土" ],
    each [Date] <= #date(2022, 1, 5),
    each [
        Date = [Date] + #duration(1, 0, 0, 0),
        Weekday = Date.ToText( Date, "ddd", "ja-JP" )
    ]
)
```

## Lv.2 動作
***condition* は *initial* の評価結果も判定する**
***next* の評価結果も *condition* で判定する**

```text:Power&#x20;Query
List.Generate(
    ()=> false,
    each _,
    each true
) = {}    // true
```
```text:Power&#x20;Query
List.Generate(
    ()=> true,
    each _,
    each false
) = {true}    // true
```

***selector* で出力を調整できる**

```text:Power&#x20;Query
List.Generate(
    ()=> [Date = #date(2021, 12, 25), Weekday = "土" ],
    each [Date] <= #date(2022, 1, 5),
    each [
        Date = [Date] + #duration(1, 0, 0, 0),
        Weekday = Date.ToText( Date, "ddd", "ja-JP" )
    ],
    each [[Date], [Weekday]]    // Record.SelectFields( _, {"Date", "Weekday"} )
)
```

```text:Power&#x20;Query
List.Generate(
    ()=> [Date = #date(2021, 12, 25), Weekday = "土" ],
    each [Date] <= #date(2022, 1, 5),
    each [
        Date = [Date] + #duration(1, 0, 0, 0),
        Weekday = Date.ToText( Date, "ddd", "ja-JP" )
    ],
    each [[Date], [Year]]?
        // Record.SelectFields( _, {"Date", "Year"}, MissingField.UseNull )
)
```
***condition* *selector* 以外で出力を調整**
Power Query の評価システムを理解していれば、ユースケースのバリエーションが増える。List.Generateで生成しながら変換やフィルタの適用を十分に理解すべき。

```text
List.FirstN(
    List.Transform(
        List.Generate(
            ()=> [Date = #date(2021, 12, 25), Weekday = "土" ],
            each [Date] <= #date(9999, 12, 31),
            each [
                Date = [Date] + #duration(1, 0, 0, 0),
                Weekday = Date.ToText( Date, "ddd", "ja-JP" )
            ]
        ),
        each [[Date], [Year]]?
    ),
    each Date.Year( [Date] ) = 2021
)
```

## Lv.3 List.Generate で表現するなら
すでに用意されている関数を代替できる記述を考えると思いのほかためになる。

```text:&#x20;{&#x20;1&#x20;..&#x20;5&#x20;}&#x20;(Power&#x20;Query)
List.Generate( ()=> 1, each _ <= 5, each _ + 1 )
```

```text:List.Numbers(&#x20;start,&#x20;count,&#x20;increment&#x20;)&#x20;(Power&#x20;Query)
(start as number, count as number, optional increment as nullable number) as list =>
    List.FirstN(
        List.Generate(
            ()=> start,
            each true,
            each increment ?? 1 + _
        ),
        count
    )
```
ただし、List.Numbers( 0, 4, 0.1 ) は { 0, 0.1, 0.2, 0.3 } と評価されない。
List.Numbers 関数には Precision.Type を受け入れる引数はないから、List.Generate に置き換えることがある

```text:List.Generate&#x20;関数に置き換えることがある
List.FirstN(
    List.Generate(
        ()=> 0,
        each true,
        each Value.Add( _, 0.1, Precision.Decimal )
    ),
    4
)
```

```text:List.Dates(&#x20;start,&#x20;count,&#x20;step&#x20;)&#x20;(Power&#x20;Query)
(start as date, count as number, step as duration) as list =>
    List.FirstN(
        List.Transform(
            List.Generate(
                ()=> #duration( 0, 0, 0, 0),
                each true,
                each _ + step
            ),
            each _ + start
        ),
        count
    )
```

```text:List.Repeat(&#x20;list,&#x20;count&#x20;)&#x20;(Power&#x20;Query)
(list as list, count as number) as list =>
    List.Last(
        List.FirstN(
            List.Generate(
                ()=> list,
                each true,
                each _ & list
            ),
            count
        )
    )
```

# 鍛錬
連続データの出力として List.Generate を使うことを好んでいる。そして出力された list を table に変換するまでが必要な流れ。
## Lv.1 日付テーブル
日付テーブルのソースとして Power BI データフローを使うと何かと都合がよい。

```text
let
    Start = 2010, End = 2021,
    StartDate = #date( Start, 1, 1 ), EndDate = #date( End, 12, 31 ), 
    Source = List.Generate(
        ()=> StartDate,
        each _ <= EndDate,
        each Date.AddDays( _, 1 )
    ),
    TableFromDates = Table.FromColumns(
        {Source}
    )
in
    TableFromDates
```
ただ、日付(Date)の属性値である年とか月を追加する必要がある。なので、record を List.Generate で扱うようにする。

```text
let
    Start = 2010, End = 2021,
    StartDate = #date( Start, 1, 1 ), EndDate = #date( End, 12, 31 ), 
    Source = List.Generate(
        ()=> [Date = StartDate],
        each [Date] <= EndDate,
        each [
            Date = Date.AddDays( [Date], 1 )
        ]
    ),
    TableFromDateRecords = Table.FromRecords(
        Source
    )
in
    TableFromDateRecords
```
*next* で必要なフィールドを追加していくだけ。

```text:必要なフィールドを追加
let
    Start = 2010, End = 2021,
    StartDate = #date( Start, 1, 1 ), EndDate = #date( End, 12, 31 ), 
    Source = List.Generate(
        ()=> [Date = StartDate],
        each [Date] <= EndDate,
        each [
            Date = Date.AddDays( [Date], 1 ),
            Year = Date.Year( Date ),
            Month = Date.Month( Date ),
            Day = Date.Day( Date ),
            Weekday = Date.ToText( Date, "ddd", "ja-JP" ),
            WeekdayNum = Date.DayOfWeek( Date, Day.Sunday )
        ]
    ),
    TableFromDateRecords = Table.FromRecords(
        Source
    )
in
    TableFromDateRecords
```
ただし、*initial* についても何らかの対処が必要になる。このときどうするか。
*initial* と *next* に同じロジックを定義する もしくは *next* で生成されたアイテムだけ採用しできるよう *initial* を調整する。連続データなので可能なことが多い。

```text:initial&#x20;を調整
let
    Start = 2010, End = 2021,
    StartDate = #date( Start, 1, 1 ), EndDate = #date( End, 12, 31 ), 
    Source = List.Generate(
        ()=> [Date = Date.AddDays( StartDate, -1 )],
        each [Date] <= EndDate,
        each [
            Date = Date.AddDays( [Date], 1 ),
            Year = Date.Year( Date ),
            Month = Date.Month( Date ),
            Day = Date.Day( Date ),
            Weekday = Date.ToText( Date, "ddd", "ja-JP" ),
            WeekdayNum = Date.DayOfWeek( Date, Day.Sunday )
        ]
    ),
    TableFromDateRecords = Table.FromRecords(
        List.Skip( Source, 1 )
    )
in
    TableFromDateRecords
```

```text:initial&#x20;にも適用
let
    Start = 2010, End = 2021,
    StartDate = #date( Start, 1, 1 ), EndDate = #date( End, 12, 31 ), 
    fxDateRecord = ( _date as date ) as record =>
        [
            Date = _date,
            Year = Date.Year( Date ),
            Month = Date.Month( Date ),
            Day = Date.Day( Date ),
            Weekday = Date.ToText( Date, "ddd", "ja-JP" ),
            WeekdayNum = Date.DayOfWeek( Date, Day.Sunday )
        ],
    Source = List.Generate(
        ()=> fxDateRecord( StartDate ),
        each [Date] <= EndDate,
        each fxDateRecord( Date.AddDays( [Date], 1 ) )
    ),
    TableFromDateRecords = Table.FromRecords(
        Source
    )
in
    TableFromDateRecords
```

## Lv.2 記述の工夫
フィールドを追加していくことでもよいが、ネストさせることででシンプルな記述もできる。
ただし、Table.AddColumn 使った記述と同等。

```text
let
    Start = 2010, End = 2021,
    StartDate = #date( Start, 1, 1 ), EndDate = #date( End, 12, 31 ), 
    EndFiscalYear = 3,
    MapFYPeriod = List.Skip(
        List.Repeat( {1 .. 12 }, 2 ),
        12 - EndFiscalYear
    ),
    Source = List.Generate(
        ()=> [Date = Date.AddDays( StartDate, -1 )],
        each [Date] <= EndDate,
        each [
            Date = Date.AddDays( [Date], 1 ),
            Year = Date.Year( Date ),
            Month = Date.Month( Date ),
            Day = Date.Day( Date ),
            WeekdayNum = Date.DayOfWeek( Date, Day.Sunday ),
            Weekday = Date.ToText( Date, "ddd", "ja-JP" ),
            FiscalYearPeriod = [
                FY = Date.Year( Date.AddMonths( Date, - EndFiscalYear ) ),
                MappedPeriod = MapFYPeriod{ Date.Month( Date ) - 1 },
                FormattedPeriod = Text.Format( "FY#{0}P#{1}", { FY, MappedPeriod } )
            ][FormattedPeriod]
        ]
    ),
    TableFromDateRecords = Table.FromRecords(
        List.Skip( Source, 1 ),
        type table [
            Date = Date.Type,
            Year = Int64.Type,
            Month = Int64.Type,
            Day = Int64.Type,
            WeekdayNum = Int64.Type,
            Weekday = Text.Type,
            FiscalYearPeriod = Text.Type  
        ]
    )
in
    TableFromDateRecords
```

## Lv.3 評価戦略
**参照されないフィールドは評価されない**
List.Generate に限った話ではなくて、Power Query 全体で適用される評価戦略。
サンプルの追加したすべてのフィールドは Date フィールドのみを参照している。したがって、必ず評価されるのは Date フィールドだけである。また、Table.SelectColumnsなどで選択されなかった列に対応する *next* で定義したフィールドの式は評価されない。だから、ひとしきり必要と考えられるフィールドは追加しておいてもさして問題ないはずだ。

評価されるフィールド : Date / Year / Month / Day
評価されないフィールド : Weekday / WeekdayNum 

```text
let
    Start = 2010, End = 2021,
    StartDate = #date( Start, 1, 1 ), EndDate = #date( End, 12, 31 ), 
    Source = List.Generate(
        ()=> [Date = Date.AddDays( StartDate, -1 )],
        each [Date] <= EndDate,
        each [
            Date = Date.AddDays( [Date], 1 ),
            Year = Date.Year( Date ),
            Month = Date.Month( Date ),
            Day = Date.Day( Date ),
            Weekday = Date.ToText( Date, "ddd", "ja-JP" ),
            WeekdayNum = Date.DayOfWeek( Date, Day.Sunday )
        ],
        each [[Year], [Month], [Day]]
    ),
    TableFromDateRecords = Table.FromRecords(
        List.Skip( Source, 1 )
    )
in
    TableFromDateRecords
```

```text
let
    Start = 2010, End = 2021,
    StartDate = #date( Start, 1, 1 ), EndDate = #date( End, 12, 31 ), 
    Source = List.Generate(
        ()=> [Date = Date.AddDays( StartDate, -1 )],
        each [Date] <= EndDate,
        each [
            Date = Date.AddDays( [Date], 1 ),
            Year = Date.Year( Date ),
            Month = Date.Month( Date ),
            Day = Date.Day( Date ),
            Weekday = Date.ToText( Date, "ddd", "ja-JP" ),
            WeekdayNum = Date.DayOfWeek( Date, Day.Sunday )
        ]
    ),
    TableFromDateRecords = Table.FromRecords(
        List.Skip( Source, 1 ),
        type table [Year, Month, Day]
    )
in
    TableFromDateRecords
```

```text
let
    Start = 2010, End = 2021,
    StartDate = #date( Start, 1, 1 ), EndDate = #date( End, 12, 31 ), 
    Source = List.Generate(
        ()=> [Date = Date.AddDays( StartDate, -1 )],
        each [Date] <= EndDate,
        each [
            Date = Date.AddDays( [Date], 1 ),
            Year = Date.Year( Date ),
            Month = Date.Month( Date ),
            Day = Date.Day( Date ),
            Weekday = Date.ToText( Date, "ddd", "ja-JP" ),
            WeekdayNum = Date.DayOfWeek( Date, Day.Sunday )
        ]
    ),
    TableFromDateRecords = Table.FromRecords(
        List.Skip( Source, 1 )
    ),
    SelectedColumns = Table.SelectColumns(
        TableFromDateRecords,
        { "Year", "Month", "Day" }
    )
in
    SelectedColumns
```

## Lv.4 最適化の手段
結果を得るまでにとてもコストが高い関数があったとする。List.Generate で最適化を検討。

```text:結果がでるまでおよそ6秒必要なクエリ
let
    fxStartOfYear =
        ( date as date ) as date =>
            Function.InvokeAfter(
                ()=> Date.StartOfYear( date ),
                #duration( 0, 0, 0, 1 )
            ),
    Source = Table.FromColumns(
        { List.Dates( #date( 2020, 12, 29 ), 6, #duration(1,0,0,0) ) },
        type table [Date = date]
    ),
    AddedStartOfYear = Table.AddColumn(
        Source, "Start of Year",
        each fxStartOfYear( [Date] ),     // 行ごとにコストが高い式を評価している
        type date
    )
in
    AddedStartOfYear
```

```text:これも結果がでるまでおよそ6秒必要なクエリ
let
    fxStartOfYear =
        ( date as date ) as date =>
            Function.InvokeAfter(
                ()=> Date.StartOfYear( date ),
                #duration( 0, 0, 0, 1 )
            ),
    Source = Table.FromRecords(
        List.FirstN(
            List.Generate(
                ()=> [
                    Date = #date( 2020, 12, 29 ),
                    Start of Year = fxStartOfYear( Date )
                ],
                each true,
                each [
                    Date = Date.AddDays( [Date], 1 ),
                    Start of Year = fxStartOfYear( Date )
                ]
            ),
            6
        ),
        type table [Date = Date.Type, Start of Year = Date.Type]
    )
in
    Source
```

```text:これは結果が出るまで約2秒ですむ
let
    fxStartOfYear =    // いくつかの属性値が同じであれば得られる結果も同じという特性
        ( date as date ) as date =>
            Function.InvokeAfter(
                ()=> Date.StartOfYear( date ),
                #duration( 0, 0, 0, 1 )
            ),
    Source = Table.FromRecords(
        List.FirstN(
            List.Generate(
                ()=> [
                    Date = #date( 2020, 12, 29 ),
                    Start of Year = fxStartOfYear( Date ),
                    Year = Date.Year( Date )    // 短絡評価に用いる属性値
                ],
                each true,
                each [
                    Date = Date.AddDays( [Date], 1 ),
                    Start of Year = 
                        if
                            Year <> [Year]
                        then
                            fxStartOfYear( Date )
                        else
                            [Start of Year]
                        ,
                    Year = Date.Year( Date )    // 短絡評価に用いる属性値
                ]
            ),
            6
        ),
        type table [Date = Date.Type, Start of Year = Date.Type]
    )
in
    Source
```
## Lv.5 日付テーブルに週番号(ISO8601)を追加
Excel ワークシート関数でいうところの ISOWEEKNUM 関数で得られるような結果を日付テーブルに追加したいということ。

https://support.microsoft.com/en-us/office/isoweeknum-function-1c2d0afe-d25b-4ab1-8894-8d0520e90e0e

ほかの方法でも記述できるけれども List.Generate の鍛錬なので。

```text
let
    StartDate = #date(2010, 1, 1), EndDate = #date( 2022, 12, 31 ),
    Source = List.Generate(
        ()=> [
            Date = Date.AddDays(
                Date.AddYears(
                    Date.StartOfYear( StartDate ),
                    -1
                ),
                -1
            )
        ],
        each [Date] <= EndDate,
        each [
            Date = Date.AddDays( [Date], 1 ),
            ISOWeekNum = [
                StartOfWeek = Date.StartOfWeek( Date, Day.Monday ),
                CurrentWeekDates = List.Dates(
                    StartOfWeek, 7, #duration( 1, 0, 0, 0 )
                ),
                HasJan4th =
                        List.Contains( { 1, 12 }, Date.Month( Date ) )
                    and
                        List.MatchesAny(
                            List.Transform( CurrentWeekDates, Date.ToRecord ),
                            each [Month] = 1 and [Day] = 4
                        ),
                IsMonday = Date.DayOfWeek( Date, Day.Monday ) = 0,
                CalculatedWeekNum =
                    if
                        HasJan4th
                    then
                        1
                    else
                        [ISOWeekNum] + Number.From( IsMonday ),
                Return = CalculatedWeekNum
            ][Return],
            ISOWeek = Text.Format(
                "#{0}-W#{1}-#{2}",
                {
                    Date.Year(
                        Date.AddDays(
                            Date,
                            4 - ( Date.DayOfWeek( Date, Day.Monday ) + 1 )
                        )
                    ),
                    Number.ToText( ISOWeekNum, "00" ),
                    Date.DayOfWeek( Date, Day.Monday ) + 1
                }
            )
        ]
    ),
    TableFromDateRecords = Table.FromRecords(
        List.Skip( Source, each [Date] < StartDate ),
        type table [
            Date = Date.Type,
            ISOWeekNum = Int64.Type,
            ISOWeek = Text.Type
        ]
    )
in
    TableFromDateRecords
```

# 思ったこと🙄
*next* では、直前に生成されたアイテムを参照し新たにアイテムを生成するのだから、 Table.AddColumn で実現しようとすると大変な処理もサクッとできたりするよね。


# その他
https://powerbi.connpass.com/

- [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)