Excel ファイルのうち、可能な限り必要な部分だけ読み込んでいると考えられる。(多分そうなんだろうけど、言質を取るまでのものではない)

ファイル形式により動作は異なる

ここでいうファイル形式とは、読み込む Excel ファイルのファイル形式

現在のファイル形式

実際に読み込まれる模様を観察
100万行10列のテーブルが保存された Excel ファイル(4.18 MB)を読み込んだ時、Power Query がどれだけファイルにアクセスするかを観察する。プロセスモニタを使って対象ファイルに対する Read Bytes を集計。

ファイルアクセスは1回とは限らないし
Excel ファイルの場合、複数のアクセスが行われる
必要な部分だけ読み取る

ということがわかる。Power BI Desktop で読み込んで確かめた。多少の差はあるけど Excel もほぼ同じ

Table1

let
    Source = Excel.Workbook(File.Contents("D:\Data\Book1.xlsx"), null, true),
    Table1 = Source{[Item="Table1",Kind="Table"]}[Data]
in
    Table1

読み込み自体は何回か行われるけど、ファイルの構造を理解して対象のデータ(ここではテーブル)を読み取っているのでは？と推測。では、先頭行で行フィルタを適用。

Table1(先頭10000行)

let
    Source = Excel.Workbook(File.Contents("D:\Data\Book1.xlsx"), null, true),
    Table1 = Source{[Item="Table1",Kind="Table"]}[Data],
    KeptFirstRows = Table.FirstN(Table1,10000)
in
    KeptFirstRows

明らかに Read Bytes が減りファイルサイズを下回る。ということは、ファイルすべてを読み込んでからデータを取り出すのではなくて、必要なデータだけ抽出しているとみるべきだ。

Power Query エディタでこのメタデータを得るとき、

ファイル全体を読み込むこともないのだ。

別のパターンでも確認。このファイルに数行のテーブルを追加し読み取ったときどうなるか。

Table2

let
    Source = Excel.Workbook(File.Contents("D:\Data\Book1.xlsx"), null, true),
    Table2 = Source{[Item="Table2",Kind="Table"]}[Data]
in
    Table2

メタデータと必要な部分だけを読み込む予想の範囲を超えず Read Bytes は減る。

以前のファイル形式

Access connectivity engineを利用
以前のファイル形式(*.xls とか)ね。BIFF8 くらいなら確かめようあるけど、それより前を確かめることはない
特徴としては ACE(Access connectivity engine : MS Access のエンジンね)を利用している。Excel ファイル全体を読み込んでしまう動作になっているはずで。読み込んでいるとき、なんか違うなぁとか感じたら大体はこの影響。列数の制限とかあったかな。

let
    Source = Excel.Workbook(File.Contents("D:\Data\BIFF.xls"), null, true),
    Sheet1 = Source{[Name="Sheet1"]}[Data]
in
    Sheet1

let
    Source = Excel.Workbook(File.Contents("D:\Data\BIFF.xls"), null, true),
    Sheet2 = Source{[Name="Sheet2"]}[Data]
in
    Sheet2

を評価した時の showplan.out

showplan.out

---------------------------------------------
DATE: 0
VER:  16.00.14301

NOTE: Currently does not handle subqueries, vt parameters, and subqueries
NOTE: You may see ERROR messages in these cases



--- temp query ---

- Inputs to Query -
Table 'Sheet1$'
- End inputs to Query -

01) Scan table 'Sheet1$'



--- temp query ---

- Inputs to Query -
Table 'Sheet1$'
- End inputs to Query -

01) Scan table 'Sheet1$'


---------------------------------------------
DATE: 0
VER:  16.00.14301

NOTE: Currently does not handle subqueries, vt parameters, and subqueries
NOTE: You may see ERROR messages in these cases



--- temp query ---

- Inputs to Query -
Table 'Sheet2$'
- End inputs to Query -

01) Scan table 'Sheet2$'



--- temp query ---

- Inputs to Query -
Table 'Sheet2$'
- End inputs to Query -

01) Scan table 'Sheet2$'

互換性を保っている程度だろうから、このファイル形式を積極的に利用することはないかなと。
Power BI データフローで利用するときデータゲートウェイ必須のはずだし。

思ったこと🙄

処理の開始がファイルすべての読み込みではない。それができるファイルフォーマットだもの。ちゃんと考えられているよね。
同じファイルに保存されたテーブル間のマージはどうなるかに重要さを考えていない。だって、メタデータ部分の取得が重複するかしないかだけだから。File.Contents 関数でファイルを読み取るときというのは、多くの場合で高速だしね。

忘れてたけど、Query option の [Background Data] を無効にして Privacy Levels を無視する設定で観察すると雑念が含まれないのでよいですよ。運用環境でこの設定することは各自で判断することだ。

その他