More than 1 year has passed since last update.

Power Query workout - List.Generate

Last updated at 2022-01-03Posted at 2022-01-03

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

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

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

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

ウォームアップ

Lv.1 基礎 of 基礎

構文と例

List.Generate (Power Query)

List.Generate(
    initial as function,
    condition as function,
    next as function,
    optional selector as nullable function
) as list

Power Query

List.Generate(
    ()=> 1,
    each _ <= 5,
    each _ + 1
) = { 1, 2, 3, 4, 5 } // true

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

Power Query

List.Generate(
    ()=> 1,
    (current)=> current <= 5,
    (current)=> current + 1
) = { 1, 2, 3, 4, 5 } // true

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

Power 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 で判定する

Power Query

List.Generate(
    ()=> false,
    each _,
    each true
) = {}    // true

Power Query

List.Generate(
    ()=> true,
    each _,
    each false
) = {true}    // true

selector で出力を調整できる

Power 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"} )
)

Power 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で生成しながら変換やフィルタの適用を十分に理解すべき。

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 で表現するなら

すでに用意されている関数を代替できる記述を考えると思いのほかためになる。

{ 1 .. 5 } (Power Query)

List.Generate( ()=> 1, each _ <= 5, each _ + 1 )

List.Numbers( start, count, increment ) (Power 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 に置き換えることがある

List.Generate 関数に置き換えることがある

List.FirstN(
    List.Generate(
        ()=> 0,
        each true,
        each Value.Add( _, 0.1, Precision.Decimal )
    ),
    4
)

List.Dates( start, count, step ) (Power 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
    )

List.Repeat( list, count ) (Power 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 データフローを使うと何かと都合がよい。

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 で扱うようにする。

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 で必要なフィールドを追加していくだけ。

必要なフィールドを追加

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 を調整する。連続データなので可能なことが多い。

initial を調整

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

initial にも適用

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 使った記述と同等。

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

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

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

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 で最適化を検討。

結果がでるまでおよそ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

これも結果がでるまでおよそ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

これは結果が出るまで約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 関数で得られるような結果を日付テーブルに追加したいということ。

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

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 で実現しようとすると大変な処理もサクッとできたりするよね。

その他

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up