Power Queryの基礎 5_CSV結合②

前々回の記事はCSVファイルをBinary.Combineで結合する方法を紹介しましたが、今回はCsv.Documentという関数を使ってデータを抽出する方法を見ていきます。Csv.DocumentCSVファイルやTextファイルからデータを抽出する際に使われるM言語で、csvやtextファイルのデータ結合に際して使われます。CSVは通常、カンマ「,」区切りで構成されたフラットファイル*1であり、textファイルの多くはタブ区切りで構成されています。

Binary.Combineと異なるところは、Csv.Documentは通常、列を追加してBinaryの中身を取り出す方法であり、新しく追加された列にはTable形式でデータが抽出されることです(下図①→②)。

f:id:marshal115:20200517013127p:plain

Binaryはテキストや画像等の情報を格納するコンテナのようなものであり、[Content]列の余白をクリックすると、①のようにファイル名とサイズの情報が表示されますが、ファイルの中身まで表示してくれません。対して、BinaryからTable形式に抽出されたデータは列内の余白をクリックすると、Tableを展開する前の中身を一部プレビューで見ることができます。これにより、Table内に不足情報があるかどうかを確認できるので、クエリを結合する前に必要な情報がある列を事前に残すことができるわけです。

Csv.Documentによるデータの抽出

データセットExcelのPower Queryを使って、フォルダの中のデータを参照するところから始めます。

  1. データ > データの取得 > ファイルから > フォルダから
  2. フォルダパスを入力し、OK

    f:id:marshal115:20200517165139p:plain

  3. フォルダ内の全てのファイルが表示され、フォルダ内にcsv以外のファイルが入っている場合、[Extension]列で.csvだけを選択
  4. [Content]列を選択し、Ctrlキーを押しながら[Name]列を選択(列の複数選択)
  5. 右クリック > 他の列の削除

    f:id:marshal115:20200517170113p:plain

  6.  新しい列を追加する
    ① 列の追加
    ② カスタム列
    ③ 列名の設定(なんでもOK)
    Csv.Document( [Content], [Encoding = 932] )と入力
    ⑤ OKをクリック
    f:id:marshal115:20200517172220p:plain

これで、最初の図の通り、[カスタム]列にTableとしてデータが抽出されているはずです。ここでのポイントは以下2つ。

  • カスタム列を追加する前に不要な列を削除している
  • 手打ちでCsv.Documentとそれ以外の項目を入力している

まず最初の「不要な列を削除」ですが、Excelスプレッドシートで列を削除する概念と異なります。すなわち、

  1. Power Queryは基データ(csv)の中身を実際に削除しているわけではない
  2. ここでの「列の削除」は新たに追加する列(カスタム)を見やすくするための操作
  3. 削除した列は、右側の「適用したステップ」で1つ上のステップをクリックすると、削除する前の状態に戻すことができる

という操作になります。一方、手打ちでCsv.Documentを入力する方法はやや敷居が高いように感じますが、以下の2つの利点があるため、ここでは必ずこのように記述をしてください。

  1. Power Queryの中上級者であれば、自分でソースコードを書くのは普通のこと
  2. M言語を学ぶのに良い題材であり、Csv.Documentを使うベストプラクティスに繋がる

1はExcelで複雑な関数(ネスト関数)を書く時と同じと考えれば、それほど難しいことではないはずです。2はクエリの数を減らしつつ、ベストプラクティスを学べる良い機会になります(次回紹介)。

ちなみに、[Encoding = 932]という記述は前回の記事にある通り、文字化けを防ぐために必要なものとなります。抽出されたデータセットは以下のようになりますが、ここで中身について少し吟味してみます。

f:id:marshal115:20200517175404p:plain

  • Tableの中身はColumn1~Column6まで、6列で構成されている(全ファイル同じ)
  • それぞれの列にヘッダーが存在しており、ヘッダーの昇格(Column1~Column6をヘッダー名に置き換える作業)が必要
  • [Content]と[Name]は不要な列となった

[Name]列を今回の例で残した理由は、結合するデータが時系列データであるため、Table内で[年月]の列がなかった場合、

[カスタム]列を展開する前に[Name]から[年月]を抽出する必要がある

ためです。これを示したのが下図ですが、時系列データで各ファイルの中身が以下のようになっていた(Column1で年月がなかった)場合、前回解説したBinary.Combineを使ってファイルを結合することができないことになります(各ファイルのデータがどの時点であるかを判別できないため)。

f:id:marshal115:20200517213319p:plain

今回は[年月]がBinaryの中に入っているため、心配する必要はないですが、データを結合する前に可能な限り、確認しておくことが重要です。

通常の抽出法

いよいよ下図の[カスタム]列を展開して、データを抽出する手順に入ります。

f:id:marshal115:20200517175404p:plain

 まずは、最も一般的なやり方でやってみます。

  1. データの結合
    A: [カスタム]列の展開
    B: 列の読み込みで「更に読み込む」
    C:「元の列名をプレフィックスとして使用します」をチェックオフ

    f:id:marshal115:20200517183236p:plain
    上図で[カスタム]列の←→アイコンをクリックすると、Power Queryは
    列を展開し、ファイルの中身を全て結合する処理
    を行います。その際、BとCを必ず確認する必要がありますが、理由は以下の通り。

    B: 今回は6列あることが分かっていますので、全ての列を抽出する場合、確認しなくてもよいですが、列数が多いデータから必要な列だけを抽出する必要がある場合は、Column1~Column○の中から、チェックを外すことになります。この手順を踏まない場合、必要な列を選択できないため、殆どのケースでクリックすることになります。

    C: 外さないでOKを押すと、下図のように展開後の列名が
    展開前の列名が付与された状態
    で設定されてしまいます。これを行うメリットは殆どありませんので、展開する前は必ずCのチェックを外す、と覚えて頂いて構いません。

    f:id:marshal115:20200517183939p:plain

  2. 不要列の削除

    f:id:marshal115:20200517190351p:plain
    [Content]と[Name]列は不要のため、ここで削除しておきます。

  3. ヘッダーの昇格

    f:id:marshal115:20200517191021p:plain

    ここまでくると、前回記事のBinary.Combineを行った直後と同じになりますので、年月の不要ヘッダーを非表示 > データ型の設定、等の手順へ入ります。
    f:id:marshal115:20200426220349p:plain
    f:id:marshal115:20200426220924p:plain
    f:id:marshal115:20200426221748p:plain

上記の方法はBinary.Combineよりも手数が増えていることや、年月がデータセットに入っているため、敢えて[カスタム]列を追加してCsv.Documentを使う必要はありません。また、別記事で紹介しますが、この方法は各ファイルの列の並びが同じであることが必須であり、データセットが追加されていたり、列名が変わったりした場合には対処できない弱点があります。

そこで、次回はCsv.Documentを使ってデータを抽出する際のベストプラクティスについて紹介していきます。

*1:データを列挙したテキストファイルで、改行文字によって各レコードが仕切られているもの。Excelとは異なり、複数のシートでのデータを格納するのではなく、1つのファイルで1つのテーブル(データセット)でデータを格納する