関数でいうと、Table.Sort ね。タイトルにはちょっと煽り成分を含めた。Table.Sort って使うことがぼぼ皆無なのだけど、もしかして使ってる人多いのかなと思ったので。
Table.Sort - PowerQuery M | Microsoft Docs
構文は難しいことないよね。だいたいポチポチで済むだろうし。
さきに結論
結論としては、Power Query おせーよってときはきっとページングが発生しちゃってんだろうなぁと。特にソートはね。って感じかな。テーブルのマージもねメモリ消費激しいよね。
使う機会がないのはなぜか
そもそも必要としていない
Power Query を使うフェーズは、これから集計などを行うための準備(Data preparation)。なので、行の順番に執着がないのだ。例えば Excel ピボットテーブルのデータソースにしたり、Excel モデル、Power BI データモデルへのインポートなどがメインなのだけど、それらすべて集計において行の順番にこだわらない。集計対象となる行の存在だけが重要だから。Excel ワークシートにロードするというときは Excel テーブルになるわけだから、テーブルのプロパティでソートを維持してあげればこと足りるのでは？と思うのです。
パフォーマンスの問題
リソース消費が大きくてパフォーマンスが良くない。わずか100万行でもだ。たとえば、
- Power Query でソートを仕掛けること
- Excel ワークシートでソートを仕掛けること
には大きな違いがあって、ソート対象となるデータすべてがすでワークシート上に保存されているソートが速いのはまぁ当然なんだろうなと。ワークシート上にデータがあるってことは概要として順番もあらかじめ把握できているのかもしれないし、有限のデータを対象とすることができるのだから相当の最適化されているものと考えてもよいかなと。
Power Query では変換加工されるデータすべてを積極的にキャッシュすることはないから、ソートするためにキーになる列すべての行をメモリ上に読み込む必要があって、かつ、メモリ上でキーすべてを比較しなければならないのだ。
Power Query のプロセス Microsoft.Mashup.Container ごとの最大メモリ制限は 256 MB で、なんか遅いぜってときは大概張り付きになってる。ページングが発生しているのかもしれない。ソート以外の処理でもメモリは消費しなければならないからページングが発生するなんてことはわりと多いことなのかも。
まぁ、Table.Buffer も同じ理由で使わないほうがよいのだけど、Table.Sort してからの Table.Buffer はいうまでもなくパフォーマンスに超大きく影響することがあるから気を付けるべきなのだ。
では、どうするか
ソートをしない
ソートを完成させるためにはすべての行を走査し比較することが必要なので、できれば使わないほうがよいはずだ。Power Query のテーブルに対する処理の多くでは、すべての行を読み込むことなく順次行ごとに処理が進むようになっているのでメモリ消費は少なく、100万行程度であればすぐ済むことが多いのだ。
MashUp Engine でソートをしない
とはいえ、ソートが必要になることはある。Power Query には処理をデータソース側にプッシュする機能があるのだから、これを積極的に利用したらよい。サポートするデータソースはリレーショナル データベース であるとか制限はあるけれども、通常サーバ側のリソースはつよつよだったりするし、ソートのことをあらかじめ考慮しているデータソースですし。
どうにもできないときもある
Excel ブック や CSV とか ファイルのデータソースが該当。で、最も多くの使われ方かもしれない。引きつづきソートをしないほうがよいというのは変わりはないけれども、Table.Sort 関数以外でも 内部的に Table.Sort が使われている関数があるし、そうでなくても行のソートが関与する関数があるわけで。こうなると、MashUp Engine の性能をできるだけ生かしつつ最適化できるようなことを考えるしかない。
なんとかできないか考えた
ソートに限らずパフォーマンスを向上させるには、処理の対象となるテーブルでいうところの行と列を可能な限り減らすことではある。ただ、ここでは行のソートに注目して試みる。
ソートの対象となる範囲、テーブルでいうところの行をできるだけ少なくするというのがよさそうである。
Excel ワークシート上で RANDARRAY( 1000000, 1, 0, 99, TRUE) なんて感じで 1列100万行のテーブルを用意。このテーブルを参照し比べてみた。
Case 0
100万行を昇順でソート
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ChangedType =
Table.TransformColumnTypes(
Source,
{
{
"Column1",
Int64.Type
}
}
),
SortedRows =
Table.Sort(
ChangedType,
{
{
"Column1",
Order.Ascending
}
}
)
in
SortedRows
おおむね 17 ~ 20 sec に収まる。クエリが実行されているとき、Microsoft.Mashup.Container のメモリ使用量はほぼ上限 256 MB になっていたので、ページングが起きているのでは?と。なんとかしないとねって感じ。
Case 1
必要だった処理ではないのかもだけど、これは速いのはあたりまえだ。だって、先頭 100000行しかソートしていないから。
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ChangedType =
Table.TransformColumnTypes(
Source,
{
{
"Column1",
Int64.Type
}
}
),
KeptFirstRows = Table.FirstN(ChangedType, 100000),
SortedRows =
Table.Sort(
KeptFirstRows,
{
{
"Column1",
Order.Ascending
}
}
)
in
SortedRows
Case 2
同じく本来の目的を達しないけれども、ソートの対象を減らす手段としてあらかじめフィルタを適用し、そのあとソート。
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ChangedType =
Table.TransformColumnTypes(
Source,
{
{
"Column1",
Int64.Type
}
}
),
FilteredRows =
Table.SelectRows(
ChangedType,
each [Column1] < 10
),
SortedRows =
Table.Sort(
FilteredRows,
{
{
"Column1",
Order.Ascending
}
}
)
in
SortedRows
5 ~ 7 sec の範囲で完了。
ソートの前フィルタだけの時、2 ~ 3 sec で完了。
Case 3
Case 0 と同じ結果が得られて、かつ、思ってたよりだいぶ速い。
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ChangedType =
Table.TransformColumnTypes(
Source,
{
{
"Column1",
Int64.Type
}
}
),
GroupedRows =
Table.Group(
ChangedType,
{"Column1"},
{
{
"Rows",
each _,
type table [Column1 = nullable number]
}
}
),
SortedRows =
Table.Sort(
GroupedRows,
{
{
"Column1",
Order.Ascending
}
}
),
ExpandedRows =
Table.ExpandTableColumn(
SortedRows[[Rows]],
"Rows",
{"Column1"},
{"Column1"}
)
in
ExpandedRows
7 ~ 8 sec で完了し、このケースでは ページングが発生しなかったようだ。なるほど。
グループ化でソート対象を 100行に圧縮したことがよかったのだろう。で、そのあと展開処理が必要になるけれども、パフォーマンスへの影響がソートより少なかったとみえる。
思ったこと🙄
テーブルの[グループ化] って結構イケてるな。