ビジネスユーザーが自分で分析を行う際に使用するデータのうち、最も一般的なものがCSV（テキストファイル）やExcelとなります。今回はこの2つのデータの違いについて見ていきます。両者はどちらもExcelから開くことが可能であり、特にCSVは取り扱いに注意が必要なファイルとなります。
概要
CSV：Comma Separated Values
- 文字通り、カンマで区切られた書式のデータ
- Excelやメモ帳で開くことができる
- Excelで開いた場合、1シートにだけ表示される（複数シートは認識しない）
- Excelで開いてセルに数式を入れた場合、再度開くと数式が入っていたセルは全て値貼り付けに変更されてしまう
- 保存する書式がUnicodeやUTF8があり、前者は日本語の書式に対応している
- データは圧縮されずに保持されるため、保存したファイルのサイズはExcel形式よりも大幅に大きくなる
- CSVは異なるシステム間でも認識できるように設計されているため、ソースデータとして最も扱いやすいファイルである
- データを取得するにはBinary.CombineやCsv.DocumentといったM言語を使用する
Excel：xlsx、xls、xlsm等多くのタイプを持つ
- ビジネスにおいて数値を扱う際に最も利用されるファイル
- CSVよりも多くの書式で保存でき、xlsm*1やxls*2、そしてxlsx*3等が挙げられる
- 複数のワークシートで構成されており、非表示シートやテーブル、名前付参照範囲、印刷範囲等もPower Queryで認識される
- CSVとは異なり、[GetData]列からTableを展開すると、Name, Data, Item, Kind, Hidden等の属性が現れ、更にワンステップの処理が必要となる
- 最も一般的な書式（.xlsx）は圧縮効率が高く、ファイルサイズがCSVと比べ小さい
- データを取得するにはExcel.Workbook等のM言語を使用する
- テーブル形式のデータを格納できることから、ステージングクエリ*4を作るための土台として活用できる
データ抽出時の考察
Power QueryからCSVやExcelに格納されているデータを取得する際の留意点として、大きく分けて以下2つあります。
- クエリのパフォーマンス
- ステージングクエリ（Stanging Query）の活用
前者について、概要でも述べている通り、Power Queryのデータ抽出パフォーマンスは
となります。すなわち、ExcelよりもCSVにあるデータを抽出した場合のほうが早いことになります。どれだけ早いかについては、Power Queryの神様の1人であるChris WebbさんのBlogで確認ができます。
結論として、
ようです。こちら、実務で両方のファイルを扱ったことのある方ならわかると思いますが、確かにCSVのデータを抽出したほうが圧倒的に早いです。なぜExcelのほうが遅いのかについて、公式の説明があまり見当たらないので何とも言えないですが、恐らくはファイル構造に問題があるのではと思っています。
可能性として、Excelは複数のシートでデータを格納できること、ファイルを圧縮した状態で持っていること等が関係しているのではないか、という推測が成り立ちます。一方、CSVは基本的にファイル構造が1シートであり、かつ圧縮構造ではなく、ファイルのストラクチャがシンプルであることから、データを抽出するのにPower Queryが余計なことをやらなくても済むのではないか、と考えられます。
次に後者のステージングクエリですが、以下の特徴が挙げられます。
このうち2について、解説する必要があります。ステージングクエリを使う理由は、3のパフォーマンスの差を埋めるためのものであると考えて差し支えありません。CSVからデータ抽出したほうが早いのであれば、全てのデータソースをCSVとして格納すればよいという結論になるが、元々Excel書式であった複数のファイルをCSVへ変換する手間や、それぞれのファイル内に複数のシートが存在していた場合のことを考慮すると、現実的ではない場合があります。
そして実は、Power BI等からステージングクエリがあるExcel内の該当するテーブル（=ステージングクエリ）を参照した場合のクエリパフォーマンスはそれほど悪くないため、実用的なやり方として考えてよいわけです（下図）。
なお、Power BIからワークシートに出力されたExcelのワークシートの行数制限（100万行）があるため、この制限を超える行数のステージングクエリをワークシートに出力する必要がある場合はシートを2つ以上に分ける必要があります。
最後に、ステージングクエリを作る際に重要なことは、
ステージングクエリは必ずワークシートに出力された状態にしておく必要がある
ということです。Power Queryエディタの中でステージングクエリを作っても、その情報に外部からアクセスすることはできず、あくまでメモリ上にデータが読み込まれた状態にしておく必要があるわけです。
まとめ
- CSVをデータソースとして使うメリットはアクセス速度が速いこと、デメリットはオリジナルファイルのデータサイズが大きくなってしまうこと
- Excelをデータソースとして使うメリットは
a) Excel内にある多くのデータにアクセスできること
b) ステージングクエリを構築できれば、アクセス速度が改善されること
c) オリジナルファイルのデータサイズがCSVよりも小さいこと - Excelをデータソースとして使うデメリットは、Power Queryでそのままアクセスした場合、データ抽出のパフォーマンスが著しく落ちてしまう可能性があること
- Power Queryを使う際、それぞれのメリット・デメリットを考慮したうえで、シナリオ別に最適な方法を採用すること
