Edited at

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

システム・コール ジェネレイト・リスト

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

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


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