Power Queryの応用_ファイル名を[Data]列のTableの中に挿入

以前の記事でCSVの結合に際してBinary.Combineを使った場合、ファイル名を抽出できないと記載しました。

marshal115.hatenablog.com

一方で、Binary.Combineではなく、Csv.Documentを使用すれば、最終的にデータを展開(結合)する前に、ファイル名をデータ列の[Table]内に挿入することができるようになります。今回はこれのやり方について説明します。

まずは、CSVが保存されているフォルダを参照し、Csv.Documentを追加して列[Content]の中身を抽出します(ここまでのやり方が分からない場合は以前の記事を参照)。

f:id:marshal115:20210330155458p:plain

[GetData]という列が追加されますが、このままではヘッダーがまだ昇格されていない状態となります。

f:id:marshal115:20210330160300p:plain

そこで、式をTable.PromoteHeadersで囲ってあげることでヘッダーの昇格を行うことが可能となります。

f:id:marshal115:20210330160446p:plain

追加された[GetData]列に対して、カスタム列を追加し、

=Table.AddColumn([GetData], "Name", (x) => [Name])

という式を下図のように追加します。

f:id:marshal115:20210330160400p:plain

すると、新しい列[DataToExpand]が追加され、データを結合する前の[Name]列(=ファイル名)を抽出できるようになります。

f:id:marshal115:20210330161058p:plain

最後に[DataToExpand]列を右クリック > ドリルダウンし、ドリルダウン後の数式をTable.Combineで囲ってあげると全てのデータを展開できるようになります。

f:id:marshal115:20210330161657p:plain

 最終アウトプットのイメージ

f:id:marshal115:20210330161833p:plain

このやり方は、手動でソースコードを記述する必要があり、面倒ではありますが、CSVのデータを抽出する際に必ず覚えておくべきテクニックであり、以下3つの利点があります。

  1. Table.Combineを使うことで、列名が異なっている場合でも全ての列名を1つのデータセットとして結合可能(データを漏れなく全て抽出)
  2. 列名は同じであるが、ファイルによって並び順が違っていたとしても、全て結合可能
  3. 結合前のデータセットにある列名を、展開する前に展開後の情報として格納できるため、様々な応用が利く

このようなメリットがあることから、Power Queryを使ってテキスト型のファイルからデータを抽出する際において、利用頻度が非常に高いテクニックと言えます。

ちなみに、Table.AddColumnを利用して、すでにある[Table]型の列内に外部の列をTable.AddColumnで更に追加するテクニックは暗記しておくと非常に便利です。(x)=>というのは関数を記述する際に使用する構文であり、(x)(y)でも、(a)でも何でも構わず、必要条件としてその後に=>を追加してあげる必要があります。=>の後に[Name]が指定されていますが、[Folder Path]に変更すれば、フォルダパスを[GetData]列内に追加できるようになります。

なお、2つの列を同時に追加したい場合は、以下のように記載します。

= Table.AddColumn(前ステップ名, "Name", each Table.AddColumn([GetData], "Record", (x) => [[Name], [Folder Path]]))

f:id:marshal115:20210330164619p:plain

これにより、下図のように、Table.Combineでデータを結合した後、[Record]という列の中から、[Name][Folder Path]の情報を確認できるようになります。

f:id:marshal115:20210330164552p:plain

この[Record]列を展開してあげると、

f:id:marshal115:20210330165004p:plain

最終アウトプットとして、外側にあった[Name][Folder Path]の両方の列が中で展開できるようになります。

f:id:marshal115:20210330165016p:plain

まとめ
  1. CSVファイルを抽出する際、Table.Combine(前ステップ名[Data])のように、Table.Combineを最終的に使用すること
  2. Table.Combineとセットで、Table.AddColumnをうまく使いこなすことで外部にある情報を内部テーブルの情報として再利用できるようになる