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

CSV ファイルをはじめとするシンプルなフォーマットを Power Query で読み込むとき、どのようなことが起きているか観察するとよいと思うのです。Power Query で変形加工を行うということはどういうことなのかの理解が捗るはずだ。

Power BI Desktop と Excel いずれでもモミモミして観察したけど、顕著な状況が観察できた Excel で話を進める。ログ量を減らすためにわずか 2,000行 で確かめているので、クエリ評価のパフォーマンスまでを確認しているのではない。

# 思ったこと🙄
速く処理をさせるという目標ではなく、処理が遅くならないことを目標としたほうが健全かなと思う。もちろんこれらには、間違ったロジックやフローを選択していないことが含まれるよね。CSVだから遅いねということはあるけれども、シンプルなフォーマットだからできることもあるのだ。

定義したクエリやステップまたはフローを解釈し、それらから必要な所作としてファイルが読み込まれるのだから、思ってた通りの処理だけにとどまっているのかぐらいの確認ができるよう準備しておきたいものである。列挙したパターンの結果はごく一部ね。

意図的にファイルを読み込む処理に変更を仕掛けることはとても難しいだろうねと思っているのだけど、そんなこと考えている余裕があるなら、ファイルデータベースからもっと高速な処理が期待できるデータソースやフローに変換したほうがよいのでは？と。

ファイルの読み込みは多くの場合で高速なはずだから、

- ファイルの読み込みは1回だけとは限らず複数回行われることがある。
- ファイルの一部を読み込んだり、全体を読み込んだりもする。

ということだと思うね。で、よく観察し理解することでトラブルシューティングに生かせるのだね。

# プロセスモニタで観察
2,000行10列のテーブルを表現するCSVファイル読み込みをプロセスモニタで観察。

```text
let
    Source = Csv.Document(
        File.Contents("D:\Data\R2000C10.csv"),
        [Delimiter=",", Columns=10, QuoteStyle=QuoteStyle.None]
    ),
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
    PromotedHeaders
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193509/abf6a86b-899e-c203-eae5-e214eba3fa97.png)

このときのログはこの通りで、先頭から部分的に読み込んでいること含め複数読み込みされていることがわかる。このうちいくつかの読み込みについて確認。

**列数を指定しない**

```text:Columnsを指定しない
let
    Source = Csv.Document(
        File.Contents("D:\Data\R2000C10.csv"),
        [Delimiter=",", Columns=null, QuoteStyle=QuoteStyle.None]
    ),
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
    PromotedHeaders
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193509/ae70ab0f-d3a6-636e-7d74-16e89fd24a7a.png)
先頭 4K byteの読み込みが増えた。ごくわずかなイベントではあるからそれ自体は気にすることはないのだけど、列数を指定しないことで発生する事象についてよーく考えておくべきだ。Power BI service によるデータセット更新が失敗する原因になりますからね。

**オプション [Background Data] をOFF にする**
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193509/d152c706-7d9b-6019-9e02-f0b5d99f6deb.png)

```text:BackgroundData&#x20;Off
let
    Source = Csv.Document(
        File.Contents("D:\Data\R2000C10.csv"),
        [Delimiter=",", Columns=null, QuoteStyle=QuoteStyle.None]
    ),
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
    PromotedHeaders
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193509/ed7d02f9-0106-28f0-0db0-647ae1291df7.png)

クエリの評価結果のための読み込みを終えたあと、Power Query エディタのプレビュー用途で追加読み込み。オプションをOFFにすることで追加読み込むをしなくなる。ただし、Power Query エディタでの作業が引き続き行われるのであれば ON のままが都合がよいことが多いし、もうPower Query エディタ 上での作業がないのであれば OFF にしたらよい。

**1行目をヘッダとして使用しない**

```text:1行目をヘッダとして使用しない
let
    Source = Csv.Document(
        File.Contents("D:\Data\R2000C10.csv"),
        [Delimiter=",", Columns=10, QuoteStyle=QuoteStyle.None]
    )
in
    Source
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193509/c12c0720-d87b-8b55-65b8-48b7abb52710.png)
先頭 4K bytes の読み込みがヘッダ用の読み込みなのだろう。わずかなイベントなので気にすることはない。

**先頭行の選択**

```text:先頭行の選択
let
    Source = Csv.Document(
        File.Contents("D:\Data\R2000C10.csv"),
        [Delimiter=",", Columns=10, QuoteStyle=QuoteStyle.None]
    ),
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    KeptFirstRows = Table.FirstN(PromotedHeaders,1000)
in
    KeptFirstRows
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193509/c8d00206-ce52-886c-1c3d-cf28f56cbecc.png)
ファイル終端まで読み込まれることはない。

**最終行のみ選択**

```text:最終行のみ選択
let
    Source = Csv.Document(
        File.Contents("D:\Data\R2000C10.csv"),
        [Delimiter=",", Columns=10, QuoteStyle=QuoteStyle.None]
    ),
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    KeptLastRows = Table.LastN(PromotedHeaders, 1)
in
    KeptLastRows
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193509/4fd54b79-6698-0599-60b4-032262396c72.png)
ファイル終端まで読み込むことで最終行を取得

**行フィルタの適用**

```text:行フィルタの適用
let
    Source = Csv.Document(
        File.Contents("D:\Data\R2000C10.csv"),
        [Delimiter=",", Columns=10, QuoteStyle=QuoteStyle.None]
    ),
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    FilteredRows = Table.SelectRows(PromotedHeaders, each ([Column1] = "1"))
in
    FilteredRows
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193509/93b4913a-f2e1-5568-afd9-98a93be5c7b1.png)
全行に対し検査が必要だからすべて読み込みが必要になる。なので、可能であれば先頭行のみの読み込み( Table.FirstN )を使ったほうがよいときがある。

**テーブルのソート**

```text:ソート
let
    Source = Csv.Document(
        File.Contents("D:\Data\R2000C10.csv"),
        [Delimiter=",", Columns=10, QuoteStyle=QuoteStyle.None]
    ),
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    SortedRows = Table.Sort(PromotedHeaders,{{"Column1", Order.Ascending}})
in
    SortedRows
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193509/11773e58-6cf9-1730-931e-fc7ab39f89ca.png)
ファイル終端までの読み込みが2回発生する。
2回読み込むからソートが遅いのではなく、2回読み込ませてもソートが遅いのだ。

**列の選択**

```text
let
    Source = Csv.Document(
        File.Contents("D:\Data\R2000C10.csv"),
        [Delimiter=",", Columns=10, QuoteStyle=QuoteStyle.None]
    ),
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    RemovedOtherColumns = Table.SelectColumns(PromotedHeaders,{"Column1"})
in
    RemovedOtherColumns
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/193509/453d31f7-905f-620c-d508-f98425332655.png)
カラム プルーニングをサポートしているわけがない。

# その他
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)