Power Query で Excel ファイルを読み込むときに起きていること(File.Contents 関数)

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

# ファイル形式により動作は異なる
ここでいうファイル形式とは、読み込む Excel ファイルの ファイル形式
## 現在のファイル形式
**実際に読み込まれる模様を観察**
100万行10列のテーブルが保存された Excel ファイル(4.18 MB)を読み込んだ時、Power Query がどれだけファイルにアクセスするかを観察する。プロセスモニタを使って 対象ファイルに対する Read Bytes を集計。

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

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

```text:Table1
let
    Source = Excel.Workbook(File.Contents("D:\Data\Book1.xlsx"), null, true),
    Table1 = Source{[Item="Table1",Kind="Table"]}[Data]
in
    Table1
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193509/c6081e6b-c513-7244-a113-883ef39c7fd5.png)
読み込み自体は何回か行われるけど、ファイルの構造を理解して対象のデータ(ここではテーブル)を読み取っているのでは？と推測。では、先頭行で行フィルタを適用。

```text: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
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193509/82f8a7aa-3aa6-f068-a336-ccf8d6be79d1.png)
明らかに Read Bytes が減りファイルサイズを下回る。ということは、ファイルすべてを読み込んでからデータを取り出すのではなくて、必要なデータだけ抽出しているとみるべきだ。

Power Query エディタでこのメタデータを得るとき、
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193509/aabb2b26-1672-9d3a-f794-01934ef3e3ef.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193509/cd353e0a-d767-71ee-c82e-6a3362b5be04.png)
ファイル全体を読み込むこともないのだ。

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

```text:Table2
let
    Source = Excel.Workbook(File.Contents("D:\Data\Book1.xlsx"), null, true),
    Table2 = Source{[Item="Table2",Kind="Table"]}[Data]
in
    Table2
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193509/bcacd93a-2695-0361-1d99-eb66af40796c.png)
メタデータと必要な部分だけを読み込む予想の範囲を超えず Read Bytes は減る。


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

```text
let
    Source = Excel.Workbook(File.Contents("D:\Data\BIFF.xls"), null, true),
    Sheet1 = Source{[Name="Sheet1"]}[Data]
in
    Sheet1
```
```text
let
    Source = Excel.Workbook(File.Contents("D:\Data\BIFF.xls"), null, true),
    Sheet2 = Source{[Name="Sheet2"]}[Data]
in
    Sheet2
```
を評価した時の showplan.out

```text: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 を 無視する設定で観察すると雑念が含まれないのでよいですよ。運用環境でこの設定することは各自で判断することだ。


# その他
https://powerbi.connpass.com/

http://qiita.com/PowerBIxyz/items/6c4735cb5b5712e70981

http://qiita.com/PowerBIxyz/items/52e563037b855a099ca9

- [Power BI 勉強会 - connpass](https://powerbi.connpass.com/)
- [Power BI Desktop を使い始めるには](http://qiita.com/PowerBIxyz/items/6c4735cb5b5712e70981)
- [Excel で Power Query を使い始めるには](http://qiita.com/PowerBIxyz/items/52e563037b855a099ca9)