テクテク日記

テクテク=テクノロジー&一歩ずつ(テクテク)

Power Queryの基礎 8_Excelの結合

Power Queryの基礎 7まではCSVの結合について詳細に説明をしてきました。今回はもう一つよく使われるデータソースであるExcelの展開・結合方法について解説します。CSVの結合と同様、Excelの結合はM言語(Excel.Workbook)を使用します。

まずはExcelが保存されているフォルダを参照し、クエリエディタで読み込みます。

f:id:marshal115:20210331215030p:plain

これらのデータを結合するためには、以下2つの方法があります。

  1. Fileの結合ボタンをクリック(=サンプルクエリによる結合)
  2. Excel.Workbookで抽出する

まずは1ですが、以下のように[Content]列の↓↓ボタンをクリックするだけ。

f:id:marshal115:20210331215751p:plain

すると、以下のウィンドウが開くので、①~③までをそれぞれ確認。

f:id:marshal115:20210331220017p:plain

①は先ほどのクエリエディタ内にあるどのファイルをサンプルとして使用するかを選ばせる項目であり、既定では「最初のファイル」に設定されています。②はどのシートを結合するかを選ばせるもので、デモ用のファイルには1シートしか入っていないため、Sheet1を選ぶと③のプレビュー画面が見えるようになります。

ここでOKをクリックすると、以下のように複数のクエリが自動的に生成され、データが結合されます。

f:id:marshal115:20210331220447p:plain

④ 自動的に生成されたクエリ群。詳細は下記を参照。

⑤ ファイル名まで抽出してくれます。この列が必要ない場合、⑥の「適用したステップ」から該当するステップにてSource.Nameを外すか、このままこの列を削除しても構いません。

CSVを簡単に結合する際と同じく、M言語を記述せずともデータを結合できるため、抽出するデータが同じ書式・並び順・シート名等である場合に、この方法を活用すると良いでしょう。ただし、初心者のうちはこれで良いですが、中上級者になれば、④で自動生成された各クエリの意味を把握しておくことをお勧めします(自動生成された各クエリの解説については別記事で紹介します)。

一方で、Excel.Workbookで抽出する方法は以下の通り。冒頭のクエリにおいて、

  1. 列の追加
  2. カスタム列
  3. 新しい列名に「GetData」と入力(列名は何でもよい)
  4. Excel.Workbook([Content], null, null)と記述してOKをクリック

f:id:marshal115:20210331221729p:plain

上記④のExcel.Workbook関数については後ほど解説しますが、一番右に以下のような列が追加されていることが分かります。

f:id:marshal115:20210331222102p:plain

CSVであれば、⑤の段階で[Table]の右側の余白をクリックすると、⑥にはデータセットのプレビューが表示されるようになりますが、Excelではシート名等の情報がまず表示されるようになります。ここがCSVCsv.Documentで抽出した際に見える結果と大きく異なっている部分であり、Excelでは⑥から更にどのシートからデータを抽出するかを選べることに特徴があります。

ここで、通常であれば不要な列を削除するので、[GetData]を右クリック > 他の列の削除で[GetData]列だけを残して、データを展開するのですが、ここでは下記の式を少し書き換えます。

= Table.AddColumn(Source, "GetData", each Excel.Workbook([Content], null, null))

= Table.AddColumn(Source, "GetData", each Excel.Workbook([Content], null, null))[[GetData]]

に変更すると、上述した「他の列の削除」と同じ効果が得られます(正確には削除ではなく、「列を選択」を実施)。このワザを使えば、少ない列名を選ぶ場合、Table.AddColumnの最後に[[列名]]複数の列名を選ぶ場合、[[列名1], [列名2], ..]と記載すれば、Mコードを1行で希望する列を選択できるようになることを意味します。

f:id:marshal115:20210331223134p:plain

ここで、←→をクリックすると、以下のような画面となり、ここで全ての列を選択し、「元の列名をプレフィックスとして使用します」のチェックはずします。

f:id:marshal115:20210331223338p:plain

Name, Data, Item, Kind, Hiddenという列が抽出され、データを結合できる一歩手前となります。

f:id:marshal115:20210331223502p:plain

ここで各列について簡単に紹介しておきます。

[Name]:シート名

[Data]:結合用のデータが入っているTable

[Item]:同じくNameと同じであるが、シート名とテーブル名が同じ場合、[Name]では名称が重複しないように勝手に変更される場合があり、その場合には[Item]を選ぶことになる(下図の例)

f:id:marshal115:20210420175148p:plain
[Kind]:データタイプであり、SheetやTable等がある

[Hidden]FALSEの場合は表示シート、TRUEの場合は非表示シート

上記で最も重要な列は[Data]列であり、試しに[Table]の隣の余白をクリックしてみると以下のようになります。

f:id:marshal115:20210331224033p:plain

図の通り、このままではヘッダーが昇格されていないので、Table.Combine(前ステップ[Data])で全ての列名を抽出して結合を行った場合、結合できたとしても更に「ヘッダーの昇格」を行う必要があります。更に、仮に列の並び順が他のファイルで異なっていた場合、間違った列同士を結合してしまうことになります。ここでExcel.Workbook関数の第2のパラメータの登場となります。

= Excel.Workbook( [Content], null, null )

という式を記載していますが、実際には、以下の定義となっています。

= Excel.Workbook( [Content], useHeaders, delayTypes)

= Excel.Workbook([Content])のままでもデータを抽出することはできますが、この2つのパラメータ(useHeadersdelayTypes)を入れることで使い勝手やパフォーマンスが向上します。

最初のuseHeadersは文字通り、これをtrueに設定しておけば、[Data]列を結合する前に全てのファイル・シートのヘッダーを昇格してくれます。試しに

= Excel.Workbook([Content], true, null)

にした場合、上記の[Table]の隣の空白をクリックすると、以下のようになります。

f:id:marshal115:20210331225631p:plain

この状態で=Table.Combine(前ステップ[Data])を記述すると、列名の違いや列の順番に関係なく、全てのデータが正しく結合されます。

f:id:marshal115:20210331225951p:plain

この最初のパラメータをtrueにするワザは非常に便利で、CSVの結合でTable.PromoteHeadersという関数をネストしないと結合できなかったことに比べ、ExcelではExcel.Workbookの第2のパラメータをtrueに指定するだけで実現できたことになります。ただし、シートの1行目が空白であったり、nullになっていた場合、trueにしてもヘッダーが正しく昇格されないことになるため、使用できる場面は限られます。

第3のパラメータ(delayTypes)ですが、機能としては抽出時のパフォーマンスを改善するためのものであり、以下が導入背景となっています。

delayTypeが設定できる前までは、Power Queryはデータ型の判別精度を引きあげるため、全データを読み込んでいた。しかし、データ量が多い場合、非常に遅いオペレーションとなってしまう。その結果、delayTypeが導入され、trueにした場合、Power Queryが通常使用する列タイプの設定アルゴリズムがそのまま適用され、データ量の多いファイルでもnullに設定した場合よりも抽出パフォーマンスが速くなる”

このことから、Excel.Workbookを使用する際、

  1. 一行目が空白でないことが分かっている場合
    = Excel.Workbook( [Content], true, true )

  2. 一行目が空白かもしれないと疑われる場合
    = Excel.Workbook( [Content], null, true )

を使うことがベストプラクティスになります。

まとめ

  • 結合するデータセットがキレイな場合や、アドホックな結合を行う場合は、[Content]列にて「Fileの結合」で結合
  • Excelの中身が不明、データセットがキレイではない、複数のシートを選択してから結合する必要がある場合はExcel.Workbook関数を使う
  • Excel.Workbookのパラメータをうまく活用すれば、Excelデータ結合のベストプラクティスを実践することができる