テーブルの行 もしくは リストのアイテムに対し、先頭から繰り返すパターンで取捨選択ができる。
基本
Power Query エディタ GUI 操作では、
- テーブル : [Remove Alternate Rows / 代替行の削除]
- リスト : [Remove Alternate Items / 代替アイテムの削除]
で使用される。
Table.AlternateRows(
table as table,
offset as number,
skip as number,
take as number
) as table
List.Alternate(
list as list,
count as number,
optional repeatInterval as nullable number,
optional offset as nullable number
) as list
たとえば、テーブルの n 行おきに選択するならば、Index 列追加して剰余を計算して行を選択するということ自体が不要ということだ。Table.AlternateRows / List.Alternate、いずれも、行の含まれる値もしくはリストアイテムを評価しない。
応用
100万行 1列の CSV テーブルから n 行おきに列として展開する応用例
いくつかのアプローチが考えられるけれども、評価パフォーマンスの違いが大きい。それぞれ工夫の余地あったとしても、どれでもいいよってことにはならない。
Table.AddIndexColumn → Table.Pivot
よく見かけるアプローチ。集計しない Table.Pivot であっても全行読み込む動作になってしまう。なので、ソースの行数が多くなったとき急激なパフォーマンス低下が発生する。プロセスが利用するメモリ消費多すぎ
そもそも選択する手段ではないし、代用できるとしても結果を得るための性能は低い。
Table.Split → Table.Transpose → Table.Combine
ソースの行数が多くなったとき急激なパフォーマンス低下が発生する。プロセスが利用するメモリ消費多すぎ
let
Source = SourceQuery,
Custom1 = Table.Split(Source, Columns),
Custom2 = List.Transform(Custom1, each Table.Transpose(_)),
Custom3 = Table.Combine(Custom2)
in
Custom3
List.Split → Table.FromRows
ソースの行数が多くなったとき急激なパフォーマンス低下が発生する。加えて、ソースファイルの読み込みが尋常ではない回数行われる。ソースファイルへのアクセス多すぎ
let
Source = SourceQuery,
Custom1 = List.Split(Source[Column1], Columns),
Custom2 = Table.FromRows(Custom1)
in
Custom2
可能な限りストリーミングで処理
Table.AlternateRows / List.Alternate を使用する。特徴はフルスキャンせずストリーミングで処理すること。ファイルの読み込み回数が展開される列数になる。ファイル読込自体は素早く完了するし、ファイル読込が 1 回で済むことはほとんどない。Power Query のプロセスが利用できるメモリ(プライベート ワーキング セット)の最大値を超えるよりも最低限のファイル読込を繰り返した方が評価結果を得るには都合がよいのである。
List.Alternate → Table.FromColumns
let
Source = SourceQuery,
Custom1 = List.First(
Table.ToColumns(Source)
),
Custom2 = Table.FromColumns(
List.Transform(
{0 .. Columns -1},
each List.Alternate(
List.Skip(Custom1, _),
Columns -1, 1, 1
)
)
)
in
Custom2
Table.AlternateRows → Table.FromColumns
let
Source = SourceQuery,
Custom1 = Table.FromColumns(
List.Transform(
{0 .. Columns - 1},
each List.First(
Table.ToColumns(
Table.AlternateRows(
Table.Skip(Source, _),
1, Columns - 1, 1
)
)
)
)
)
in
Custom1
思ったこと🙄
Table.AlternateRows / List.Alternate 単体で必要となるケースはそうないかも知れないね。だけど、どのような結果が得られるのかぐらいは理解しておいた方がいい。
Power Query クエリが評価が遅いねっていうときの多くは、Power Query のプロセスが利用するメモリ消費がしきい値を超えページングが発生していること。なので、可能な限りストリーミングで処理されるように仕向けておいた方がよいのだ。
Power Query エディタでポチポチっと定義するステップ内容はクエリ評価に必要なロジックと考えてしまうのかも知れない。でもさ、もしかするとクエリの評価得るために充分なロジックがあるのかもだよね。
- 可能な限りデータソースで
- 可能な限りストリーミングに
- 可能な限りフルスキャンは最小限に
- 可能な限り値を評価しない
これらを考慮できると Power Query によるクエリの評価パフォーマンスが大きく改善できる可能性は高いと思うね。
その他