お題の例として累積値を求めようかなと。Excel だとピボットテーブル使えやー、Power BI だとメジャーを書けやーってことではあるんだけど、そういうこともあるよねって思いなおしたので。
Power Query エディターでデータを揉むとき、テーブルでいろいろ考えてしまいがちなんだけど、行(record)、列(list) という見方もしていかないとならないのです、結果的に遠回りってこともありますからね。
お題
Excel ワークシート上のテーブルにある[列1]の値の累積値を追加する感じで。要は行をまたいで列の集計結果を追加したいということ。
| 列1 | 累計 |
|---|---|
| 1 | 1 |
| 2 | 3 |
| 3 | 5 |
| .. | .. |
| 10000 | 50005000 |
| .. | .. |
見かけた方法
別にネガティブな評価をしているのではないです。ただ、テーブルでいろいろ考えてしまってるのでもったいないなぁということだけです。
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
ChangedType = Table.TransformColumnTypes(
Source,
{"列1", Int64.Type}
),
AddedIndex = Table.AddIndexColumn(
ChangedType,
"Index",
1,
1
),
AddedCustom = Table.AddColumn(
AddedIndex,
"累計",
each
List.Sum(
List.FirstN(ChangedType[列1], [Index])
),
Int64.Type
),
RemovedOtherColumns = Table.SelectColumns(AddedCustom,{"列1", "累計"})
in
RemovedOtherColumns
たぶん3000行くらいからヤバい感じになるかなと。同じ計算を繰り返しているのも改善点かな。
Table.AddColumn はその行の範囲だけで処理が吉なんだけど、Mashup Engine が悲鳴をあげてるかも。
すぐ遅くなるよね。なぜなのか？
端的に表現すると Mashup Engine (Power Queryの中の人ね)の得意とするところを手当なく無慈悲に否定しているからで、本題にそれるので少しだけ。例えば、
let
Source = Table1,
Added列2 = Table.AddColumn(Source, "列2", each [列1] + 1, Int64.Type),
Added列3 = Table.AddColumn(Added列2, "列3", each [列2] * 10, Int64.Type)
in
Added列3
というクエリがあったとして。[列1] + 1 の評価結果を [列2] として追加し、[列2] * 10 の評価結果を [列3] として追加するシンプルな内容。このとき、入力である Table1 の行に対しどのような処理がされているか。
- すべての行に対し[列2]追加し終わってから、
- すべての行に[列3] を追加する
という動作ではなくて、ある程度の行数の処理が完了したら、できた分を小出しに次の処理に回すスタイル(stream)になっていて、手持ちのリソースだけでできるだけ多くの行を処理するという感じだから。なので、できるだけムチャ振りしない。
お題の解決(できるだけ)
本題の List.Accumulate
List.Accumulate
リスト内のアイテムから要約値を収集します。
accumulatorを使用して、リストlist内のアイテムから要約値を収集します。省略可能なシード パラメーターseedを設定することもできます。
((state, current) => state + current ) を使用して、リスト {1, 2, 3, 4, 5} 内のアイテムから要約値を収集します。
List.Accumulate({1, 2, 3, 4, 5}, 0, (state, current) => state + current) = 15 // true
ちょっとわかりにくいなと。
ソースになる list の順番にリストアイテム(current)を accumulator に渡し、繰り返しの処理をしてくれる。List.Transform と違うのは状態(state)を持っていて、List.Accumulate の結果は最後の状態(state)。
| state | current | 次の state | |
|---|---|---|---|
| #1 | 0(seed) | 1 | 0 + 1 |
| #2 | 1 | 2 | 1 + 2 |
| #3 | 3 | 3 | 3 + 3 |
| #4 | 6 | 4 | 6 + 4 |
| #5 | 10 | 5 | 10 + 5 |
| return | 15 |
サンプル
List.Accumulate が扱う値は数値(number) である必要はないので、list を扱う方法にする。得られた list を列として テーブルに追加すればよいではないか。
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
ChangedType = Table.TransformColumnTypes(
Source,
{"列1", Int64.Type}
),
KeptFirstRows = Table.FirstN(ChangedType, 50000),
ColumnToList = List.Buffer(KeptFirstRows[列1]),
Accumulated = List.Accumulate(
ColumnToList,
{0},
(state, current) => {current + List.First(state)} & state
),
Custom1 = Table.FromColumns(
{
ColumnToList,
List.Skip(List.Reverse(Accumulated))
},
Value.Type(Table.AddColumn(ChangedType,"累積", each null, Int64.Type))
)
in
Custom1
使った関数など
List.Accumulate
(state, current) => {current + List.First(state)} & state
でリストアイテム(current)をリスト(state)の先頭に詰んでいて、
List.Skip(List.Reverse(Accumulated))
でひっくり返しているのは、
(state, current) => state & {current + List.Last(state)}
リスト(state)の後ろにリストアイテム(current)追加するスタイルだと、List.Last が高コストぽい感じだから。
List.Buffer
リストをバッファー処理します。
リストlistをメモリにバッファー処理します。この呼び出しの結果は、安定したリストです。
Mashup Engine が ヒャッハー。って感じになるのは、このバッファしたリストについては一括で処理を進めてよいよということで、ストリーミング処理でこのあとリストアイテムが追加されることがないから。
Table.FromColumns
列と指定した値のリストからテーブルを作成します。
列名と値を含む入れ子になったリストを含むリストlistsからcolumns型のテーブルを作成します。一部の列の値が他の列よりも多い場合、NULL 許容列では足りない値が既定値の 'NULL' で埋められます。
List.Zip と似た感じ。
#思ったこと🙄
Excel ワークシート関数と同じで、できるだけ多くの Power Query 関数を知らないとならないかなと。そして、それらをどう組み合わせるか。
Power Query で 累計を集計するのはちょいムチャな感じはあるけれども何とかなるかな。とはいえ、途中にスタックオーバーフローがチラチラ見えていたので、List.Accumulate じゃくて List.Generate の方がよいような気がしないでもない。
Power Query の 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 = List.Accumulate(
Table.ColumnNames(Source),
[],
(state, current) => Record.AddField(state, current, 0)
),
BufferedRows = List.Buffer(Table.ToRecords(KeptFirstRows)),
Custom1 = List.Accumulate(
BufferedRows,
{Seed},
(Rows, CurrentRow) =>
[
PreviousItem = List.First(Rows),
CurrentItem = List.Accumulate(
Record.FieldNames(Seed),
[],
(Fields, CurrentField) =>
Record.AddField(
Fields,
CurrentField,
Record.Field(PreviousItem, CurrentField) +
Record.Field(CurrentRow, CurrentField))
),
Return = {CurrentItem} & Rows
][Return]
),
Custom2 = Table.FromRecords(
List.Skip(List.Reverse(Custom1)),
Value.Type(KeptFirstRows)
)
in
Custom2
``