Help us understand the problem. What is going on with this article?

Power Query の List.Generate 関数ってなんだよー

More than 1 year has passed since last update.

システム・コール ジェネレイト・リスト
おそらくというか間違いなく難度が高い関数じゃないかと。でも、使えるようになるとホントできることがガーっと多くなるので強くおススメする関数。ひと通り覚えるまでの過程でいろんなことが理解できたような気がする。

次のリストアイテムを出力する関数に使われる入力は、直前のループで出力されたリストアイテムっていうこと。

List.Generate

List.Generate
指定された値の初期値関数、条件関数、次の値関数、および省略可能な変換関数に基づいて、リストを生成します。
初期値 initial を生成する指定された 4 つの関数に基づいて、値のリストを生成し、条件 condition に対してテストを行います。成功した場合は、結果を選択し、次の値 next を生成します。省略可能なパラメーター selector を指定することもできます。

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

引数がすべて function という難度高めの仕様である。そしてサンプルも。

Example
// 10 で始まりデクリメントが 1 の、0 を超える値のリストを作成します。
List.Generate(()=>10, each _ > 0, each _ - 1) = {10, 9, 8, 7, 6, 5, 4, 3, 2, 1} // true
Example
/*
x と y を含むレコードのリストを生成します (x は値、y はリスト)。
x は、10 未満を保持し、リスト y 内の項目数を表す必要があります。
リストが生成された後は、x の値のみを返します。
*/
List.Generate(
    ()=> [x = 1, y = {}],
    each [x] < 10,
    each
        [
            x = List.Count([y]),
            y = [y] & {x}
        ],
    each [x]
) = {1, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9} // true

うん、まぁ。こういう記述をすればいいんだなというところから始めて、少し慣れたところで振り返るとよいかもしれない。let 式で記述できなくもないけど、record での記述の方がわかりやすいのでしょう。実際に使ってみるとなるほどってなると思う。引数:selector を外してどのような list が出力されているかを見たらいい。実際使うときには、ループが終了する条件とはなんだろね🙄って考えればいいんじゃないかな。

で、"each" キーワードってなんなのかって話は以前ポストした。
ときおり出てくる "each" キーワードと "_" (アンダースコア) とは

チャレンジ

素数のリスト

ふるいにかけた結果にふるいをかけていくロジックをそのままに。ちょうどよいかなと思ったので。

手書きで数字を消し込む感じで
let
    Limit = 100,
    Source = {2 .. Limit}, 
    GeneratedList = List.Generate(
        () => [n = 0, items = Source, condition = true, remove = {}],
        each [condition],
        each
            [
                n = [n] + 1,
                items = List.RemoveItems([items], remove),
                next = [items]{[n]},
                remove = List.Generate(
                    () => next * 2,
                    each _ <= Limit,
                    each _ + next
                ),
                condition = Number.Power(next, 2) < Limit 
            ],
        each [items]
        // each
        //     Record.TransformFields(
        //         [[n],[items],[condition],[remove]],
        //         {
        //             {"items", each Text.Combine(List.Transform(_, each Text.From(_)),",")},
        //             {"remove", each Text.Combine(List.Transform(_, each Text.From(_)),",")}
        //         }
        //     )
    ),
    LastItem = List.Last(GeneratedList)
    // LastItem = Table.FromRecords(GeneratedList)
in
    LastItem
n [n] items next remove condition
0 2,3,4,5,6,7,8,9,10 true initial
1 0 2,3,5,7,9 2 4,6,8,10 true
2 1 2,3,5,7 3 6,9 true

リストアイテムを List.RemoveItems で消し込むのは遅いので、List.Select でフィルタに記述しなおして。
Number.Mod で割り切れる数値を順次検査。

範囲を拡げてもたぶん大丈夫
let
    Limit = 5000000,
    Source = {2 .. Limit},
    GeneratedList = List.Generate(
        () => [n = 0, items = Source, condition = true],
        each [condition],
        each
            [
                n = [n] + 1,
                next = [items]{[n]},
                items = List.Select(
                    [items],
                    each _ = next or Number.Mod(_, next) <> 0),
                condition = Number.Power(next, 2) < Limit 
            ],
        each [items]
    ),
    LastItem = List.Last(GeneratedList)
in
    LastItem
偶数を先に除外しておけばちょっと速くなる
let
    Limit = 5000000,
    Source = List.Generate(()=>3, each _ <= Limit, each _ + 2),
    // Source = {2 .. Limit},
    GeneratedList = List.Generate(
        () => [n = 0, items = Source, condition = true],
        each [condition],
        each
            [
                n = [n] + 1,
                next = [items]{[n]},
                items = List.Select(
                    [items],
                    each _ = next or Number.Mod(_, next) <> 0),
                condition = Number.Power(next, 2) < Limit 
            ],
        each [items]
    ),
    LastItem = {2} & List.Last(GeneratedList)
in
    LastItem

最後の方まであってるかどうか確認してないんですけどね。

テーブルに累計列を追加する

Power Query の List.Accumulate 関数ってなんだよー と同じお題

Excel ワークシート上のテーブルにある[列1]の値の累積値を追加する感じで。要は行をまたいで列の集計結果を追加したいということ。

列1 累計
1 1
2 3
3 5
.. ..
10000 50005000
.. ..
100万行でもまぁ問題ない
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"列1", Int64.Type}}),
    KeptFirstRows = Table.FirstN(ChangedType, 1000000),
    BufferedRows = List.Buffer(Table.ToRecords(KeptFirstRows)),
    Custom1 = List.Generate(
        () => [Index = 0, 累積 = 0],
        each [Index] <= List.Count(BufferedRows),
        each
            [
                Index = [Index] + 1,
                列1 = BufferedRows{[Index]}[列1],
                累積 = [累積] + 列1
            ],
        each [[列1],[累積]]
    ),
    Custom2 = Table.FromRecords(
        List.Skip(Custom1),
        Value.Type(Table.AddColumn(KeptFirstRows, "累積", each null, Int64.Type))
        // コレデキナイExcelアルヨ
    )
in
    Custom2

データソースが Excel ワークシート上のテーブルだから stream を気にしていなくて、テーブル各行をレコードとして list に変換後 List.Buffer。record として取り廻して終了。列の追加も List.Genarate の ループの中で済ましてしまえば、Table.AddColumn の必要はなくなる。

思ったこと🙄

いずれも"繰り返し"する関数なのだけど、

繰り返し 出力の型
List.Generate Do Loop list 出力は List.Accumulate でいうところの state の list
List.Accumulate For Each any stateを出力に含めようとする使い方には向かない

ということいいかなと思ってる。で、再帰呼び出しするよりは List.Generate で こなした方が速さ的にも有利だと思う。ガツガツといろいろ試していたんだけどスタックオーバーフローが起きやすいので再帰呼び出しはおススメしない感じ。

その他

おまけ

んー、やっぱり列名に依存しない記述は大変だ。

let
    Source = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"列1", Int64.Type}, {"列2", Int64.Type}, {"列3", Int64.Type}}),
    KeptFirstRows = Table.FirstN(ChangedType,20000),
    Seed = Record.FromList(
        List.Repeat({0},Table.ColumnCount(KeptFirstRows)),
        Table.ColumnNames(KeptFirstRows)
    ),
    SeedFieldNames = Record.FieldNames(Seed),
    BufferedRows = List.Buffer(Table.ToRecords(KeptFirstRows)),
    Custom1 = List.Generate(
        ()=> Record.AddField([Index = 0], "Value", Seed),
        each [Index] <= List.Count(BufferedRows),
        each
            [
                Index = [Index] + 1,
                CurrentValue = BufferedRows{[Index]},
                Value = Record.FromList(
                    List.Transform(
                        Table.ToColumns(
                            Table.FromRecords({CurrentValue,[Value]})
                        ),
                        each List.Sum(_)
                    ),
                    SeedFieldNames
                )
            ],
        each [Value]
    ),
    Custom2 = Table.FromRecords(
        List.Skip(Custom1),
        Value.Type(KeptFirstRows)
    )
in
    Custom2
PowerBIxyz
#PowerBI とか #PowerQuery とか Microsoft MVP for Data Platform (2016,2017-2018,2018-2019,2019-2020,2020-2021), Access (2011-2015)
https://powerbi.connpass.com/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away