Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
1
Help us understand the problem. What is going on with this article?
@PowerBIxyz

えっ?Power Query で行の並び替え Table.Sort 使うんですか?って話

関数でいうと、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万行を昇順でソート

Case1
let
    Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
    ChangedType =
        Table.TransformColumnTypes(
            Source,
            {
                {
                    "Column1",
                    Int64.Type
                }
            }
        ),
    SortedRows =
        Table.Sort(
            ChangedType,
            {
                {
                    "Column1",
                    Order.Ascending
                }
            }
        )
in
    SortedRows

image.png
おおむね 17 ~ 20 sec に収まる。クエリが実行されているとき、Microsoft.Mashup.Container のメモリ使用量はほぼ上限 256 MB になっていたので、ページングが起きているのでは?と。なんとかしないとねって感じ。

Case 1
必要だった処理ではないのかもだけど、これは速いのはあたりまえだ。だって、先頭 100000行しかソートしていないから。

Case1
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
同じく本来の目的を達しないけれども、ソートの対象を減らす手段としてあらかじめフィルタを適用し、そのあとソート。

Case2
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

image.png
5 ~ 7 sec の範囲で完了。
ソートの前フィルタだけの時、2 ~ 3 sec で完了。

Case 3
Case 0 と同じ結果が得られて、かつ、思ってたよりだいぶ速い。

Case3
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

image.png
7 ~ 8 sec で完了し、このケースでは ページングが発生しなかったようだ。なるほど。
グループ化でソート対象を 100行に圧縮したことがよかったのだろう。で、そのあと展開処理が必要になるけれども、パフォーマンスへの影響がソートより少なかったとみえる。

思ったこと🙄

テーブルの[グループ化] って結構イケてるな。

その他

1
Help us understand the problem. What is going on with this article?
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
PowerBIxyz
#PowerBI とか #PowerQuery とか Microsoft MVP for Data Platform (2016,2017-2018,2018-2019,2019-2020,2020-2021), Access (2011-2015)

Comments

No comments
Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account Login
1
Help us understand the problem. What is going on with this article?