気づけばPower Query（パワークエリ）に関しては4回目の記事となりました。小出しにしているわけではないですが、細かいところや背景等をプラスしていくと、どうしても記事が多くなってきます。
私がPower Queryを本格的に使い始めたのが2016年末の時でしたので、2020年10月時点で約4年のキャリアということになります。思えば当時一番最初にやったのが、今回解説するPower Queryによるデータ集計であり、基礎中の基礎であると同時に、最も使用頻度の高い作業になります。
データ集計はデータソースの種類やデータのクリーン度合いによって多くのパターンが存在し、一回の記事では紹介しきれないので、数回に分けて行いたいと思います。以下今回のコンテンツとなります。
ファイルの結合について
Power Queryを使うにあたって、最も基本的な操作がデータの結合となります。結合はデータの種類（Excel, CSV, Database等）によって異なり、典型的な結合はExcelとCSVとなります。この2種類のデータ結合にはいくつか方法があるが、今回はCSVについて見ていきます。
なお、Excelの結合方法は基本的にCSVと同じですが、データ構造*1がCSVと異なることや、使用するM言語（Power Queryの関数）が違うため、CSVの結合よりもやや手間がかかります。
プレビュー画面
Power Queryエディタをもう一度おさらいをしておきます。Power Queryがデータソースに接続すると、下記②のナビゲーションにクエリが表示される ⇒ ③の数式バーに接続した際の関数（M言語）が自動的に入力される ⇒ ④のプレビュー画面に⑤のクエリ設定画面の「適用したステップ」の処理が反映されます。
④のプレビュー画面に表示されているデータを適切なフォーマットに成形していく作業を行っていくわけですが、その際に①のリボンに配置されているボタンや右クリック操作等を使っていきます。
CSVファイルの結合
いよいよCSVファイルの結合を行いますが、Excelを使いますので、事前にPower Queryの設定を済ませておきましょう。
なお、デモで使用するCSVファイルですが、計33ファイル、各ファイルの中身は[年月]、[商品]、[定価]、[売価]、[販売実績]、[販売原価]という列で構成されおり、全ての列は同じ並び順となっています。
Fileの結合
以下がデータを結合するプロセスになります。
1. デモ用Excelを立ち上げる
2. フォルダパスをコピー
3. リボンの「データ」>「データの取得」>「ファイルから」>「フォルダから」
4. コピーしたパスをペーストしOK
5. 新しいウィンドウが出現するので、ここで一旦待機
CSVをフォルダで結合する場合、必ずこのウィンドウが出現しますが、ここで通常（ほぼ100%）は③「データの変換」をクリックし、上記おさらいしたクエリエディタを開くことになります。が、ここでは殆ど誰も解説しないその他のボタン（①と②）について少し解説していきます。
まず、①ですが、こちらをクリックすると、以下の選択肢が表示されます。
「データの結合と変換」 をクリックすると、以下の画面が更に出現します。
a) サンプルファイル「最初のファイル」
とありますが、これは33個あるファイルの中で、どのファイルをサンプルに結合しますか？という意味になります。ファイルの作成日時が異なるため、新しいファイルをベースにするのか、古いファイルをベースにするのか、として捉えてもOKです
b) 元のファイル「932:日本語（シフトJIS）」
こちらはファイル内に日本語が含まれていた場合、データを結合した後に”文字化け”しないよう、エンコード用の言語を選ぶための選択ウィンドウになります。エンコードについては別の結合方法で使うので、別途解説しますが、ここではこのままの932でOKです。ちなみに違う言語を選択してしまうと、ヘッダー名が文字化けしてしまいます
c) 区切り記号
CSVの中身がどのような記号で区切られているのかを指定するものです。こちらも必ず必要になりますが、Power Queryが自動的に検知してくれるので、これもこのままでOK
d) データ型検出
CSVを結合する際、各列のデータ書式（数値型、テキスト型、日付型等）を最初から200行目までのデータを基に、Power Queryが判断してくれる、という意味になります。データ全体をPower Queryにスキャンさせてデータ型を判断させることも可能ですが、Power Queryのパフォーマンスが200行だけをスキャンする場合よりも落ちます。通常ここも変更する必要がなく、このままでOKです。ちなみに、データ型の決定はExcelではしないが、Power Queryでは非常に重要であり、これにはDAX / Data Modelが関与しています
e) プレビュー画面
この画面は結合した後のプレビューであり、例えば先ほどのエンコード932を「なし」に変更すると、下図のようにヘッダー名が全て文字化けしてしまいます
6. Fileの結合画面でOK
上記の通り、クエリエディタが立ち上がり、ナビゲーションにフォルダやら沢山のクエリが勝手に作られてしまいます。何が起こったのかが分からないと思いますが、実はこれで（2016年9月～2019年5月までの）33個のファイルが全て結合されたことになります。試しにプレビュー画面にあるSource_Nameを見てみると、全てのファイル名が全て入っていることが分かります。
この方法は数クリックでデータを結合できるので、お手軽のようにも思えますが、Power Queryを触ったことがない人はおそらくこの段階で消化不良を起こしてしまうのではと思います。というのも、何が起こったかが不明だし、そもそもナビゲーションに出現したクエリ群は何？という話です。
ステップ5の「結合」のその他（「結合および読み込み」、「結合および読み込み先」）をクリックすると、バックエンドで実は同じことが起こるのですが、今度はクエリエディタが立ち上がるのではなく、結合した結果がワークシートに出力されます（下図）。
緑色のテーブルは先ほど見たクエリエディタ内の「プレビュー画面」のデータ、右側の「クエリと接続」に「ナビゲーション」のクエリ群が出現しているのが分かると思います。
一方、ステップ5の②を選び、読み込みをクリックすると、下図のようになります。
同じく読み込みが実行されるわけですが、よくみると先ほどの読み込みと何やらデータが違っているのが分かります。実は、この出力されたデータはステップ5で見えている画面がそのまま出力されたものとなります。
以上のように、データの中身を確認できないことはもとより、ステップ5の①と②は今回の記事の意図するものではないため、③の「データの変換」について見ていくこととします。
Binary.Combine
CSVでデータを結合する場合、最もお手軽にできるやり方がM言語のBinary.Combineを使うことです。M言語の詳細については別記事で紹介しますが、ここでは
① Binary.Combineという関数を使う
② 関数は必ず大文字で始まり、ピリオド「.」で繋ぎ、また大文字で始まる
ことだけを覚えておくと良いです。
先ほどの画面に戻り、③「データの変換」をクリックします。
すると、Power Queryはコピペしたフォルダパスの直下にある全てのファイルを、下図のように抽出してきます。
ここで、以下の手順に従ってBinary.Combineを使います。
① Content列を右クリック
② 「ドリルダウン」をクリック
③ Binaryのリストが生成される
④ 数式バー内にあるSource[Content]をBinary.Combineで括ってEnter
⑤ 結合された初期画面が表示される
⑥ 「1行目をヘッダーとして使用」をクリック
⑦ 年月のフィルターボタンをクリックし、一番下の「年月」をチェックオフ
※Binary.Combineで複数のファイルを一括結合すると、不要なヘッダーまで結合されてしまったため、手動で除外してあげる必要があります
⑧ Ctrl + Aで全ての列を選択し、タブ「変換」>「データ型の抽出」
Power Queryが各列のデータを自動的に判定し、データ型を決めてくれる。今回の場合、整数とテキスト、10進数（小数点以下あり）の3つが自動的に抽出されています。
なお、ここまでの作業は全てUI上でボタン操作で実現さてものであるが、右側の「適用したステップ」を見ると英語になっています。これは、クエリのオプションで私の環境ではクエリステップを「常に英語」に設定しているためであり、これが「現在のインストール言語」であった場合、日本語のステップ名が自動記述されます。
これでほぼ全ての作業が終わったのですが、上記⑧を見ると、CSVという1つだけのクエリで全ファイルの中身を結合できたわけです。先ほどの多くのクエリが自動生成されたやり方と比べると何ともすっきりした形で抽出が完了したわけです。
⑧ 結合したデータを一度Excelのワークシートへ出力するため、左上の「閉じて読み込む」>「閉じて次に読み込む」をクリック
⑨ 「データのインポート」ウィンドウが出現するが、ここでは下図のようにしてOK
⑩ 新しいシートに結合データが出力される
これがCSVを結合するにあたって最も手軽な方法となります。当然ながら、結合後のデータの正誤について確認が必要ですが、Binary.Combineを使えば、データ結合は簡単にできると分かって頂けたかと思います。
さて、この結果、実は先ほどのクエリがたくさん出現したやり方とやや異なるところがあります。下図がそれですが、どこが違うか分かりますか？
クエリの数は言うまでもないですが、実はBinary.Combineを使うと、クエリを一番最初に読み込んだ時の列（属性）を失ってしまうことになるのです。つまり、下図が最初に読み込んだクエリですが、ここでは各ファイルに対する情報で「Content」列以外に、「Name」列のファイル名、「Extension」列の拡張子等の情報が入っていますが、
= Binary.Combine( Source[Content] )
という数式を記述したことにより、Content列のBinary*2の中のデータしか抽出できなくなったのです。
Binary.Combineはこの弱点があるため、
ファイル名やその他属性情報が必要ない場合のみ使用
することがベストプラクティスになります。私の経験上、データの結合作業は時系列データを結合する機会が多いので、各ファイル内に年月日というデータが入っていない、かつ、ファイル名にしかその情報がない場合は、Binary.Combineは使えないことになります。
ちなみに、上の比較図の右側のクエリで[Source_Name]という列が追加されていますが、ここにファイル名が入っていますので、最初の抽出方法はこの弱点を克服していることになります。一方で、クエリが複数増えてしまうので、好き嫌いが分かれるところでしょう。
ピボットテーブル
さて、先ほどはデータを全てワークシート出力する操作を行いましたが、今度はデータを出力しないでクエリから直接ピボットテーブルを作成してみます。
① Excelの右側に出ている「クエリと接続」に表示されているクエリ「CSV」を右クリック > 複製
② 複製されたクエリ「CSV（2）」があることを確認
③ 閉じて次に読み込む
④ 「ピボットテーブル レポート」を選択し、OK
すると、以下のようにピボットテーブルが新しいシートで生成されるので、「年月」を行に、「（税込）販売実績」を値フィールドに持っていきます。
これでデータを出力することなく、ピボットテーブルが作られました。通常のピボットテーブルと異なる点は、データをクエリで保持したままピボットテーブルを作成できたことであり、データをシート上に出力しない分、ファイルサイズが小さくなります。ご参考までに、データを出力した状態からピボットテーブルを作ると、以下のようになります。
ファイルサイズは以下の通りで、「○Pivotのみ」の場合が168KB、「○出力かつPivot」よりも約半分となっています。今回使用したデータは3千行程度ですので、サイズが2倍違っても問題はないですが、これが数十万行もあるような状況であれば大きな差が生まれるのは間違いないでしょう。
このことからも分かる通り、実はPower Queryはシートに出力しないほうがメモリフレンドリー（=ファイルサイズの軽減）であり、ここがワークシートにデータを保持して作業（今回の例ではピボットテーブルの作成）するのと大きく違うところになります。では、全ての場合でクエリの出力を行わないほうが良いかというとそうでもなく、これに関しては今後少しずつ話をしていきます。
データの更新
Power Queryを使うメリットは上記の通り、データを素早く結合し、メモリフレンドリーな状況を作り出せるだけに留まりません。Power Queryが本当に凄いところの1つに
データの更新があっという間に終わる
ことです。試しに、新しいファイルを3つ、既存のフォルダに追加してみます。
ピボットテーブルで右クリック > 「更新」をクリックすると、追加された3つのファイルのデータが即座に反映されるようになります。
なぜこのようなことが起こるかというと、
Power Queryで作られたクエリは参照先フォルダの中に入っている全てのファイルを結合している
からです。これにより、同じ書式（＝列名や並び順が同じCSV）のファイルを追加すればデータが随時更新され、ピボットテーブルを更新すればいつでも最新の数字が算出されるわけです。なお、今回はピボットテーブルを更新しましたが、シートに出力したデータを「右クリック」>「更新」でも同じことが可能になります。
まとめ
Power QueryでCSVファイルのデータ結合を紹介しました。細かいところまで見ていきましたが、以下が今回のまとめになります。
- フォルダ参照による複数ファイルの結合は基本的に「データの変換」をクリック（下図の③）
- 各CSVファイルの「列名」及び「並び順」が同じ場合、Binary.Combineで結合するのがお手軽（※Excelでは使用できないことに注意）
- Binary.Combineで結合すると、クエリの初期画面で表示される[Content]列以外の列が消えてしまう
- 結合したクエリのデータをシートに出力するとファイルサイズが大きくなる
- データをシートに出力せずにピボットテーブルを作ることができる。この場合、ファイルサイズも小さくなる
- データの更新は非常に簡単で、同じフォルダ内（正確には、同じフォルダ、もしくは、そのサブフォルダ内）にファイルを追加するだけで良い
ファイルの結合方法は更に2つありますが、次の記事ではCsv.Documentという関数を使ってデータを抽出する方法をやります。これに加え、クエリ内でのデータのマージやグループ化等の操作も紹介していきます。
