Windows Info 第444回

外部ファイルをExcelに読み込む際の作業を効率化するPower Queryの活用

　ExcelにCSVやテキストファイルなどを読み込むときに使うPower Queryは、Excelの一部として出荷されるものの、Excelとは大きく異なる仕組みを持つ。このため、Power Queryは、Excelにテーブルを作り、処理の結果を入れることができるが、そのテーブルにExcelの式を入れることは不可能だった。

　しかし、PowerQueryで文字列としてExcelの数式を入れておき、テーブルが読み込まれたあとに、これを数式に変換することで、数式の入ったテーブルを作ることが可能になる。

そもそもPower Queryとは？

　Excel自体については、ほとんどの読者が知っていると思うが、Power Queryについては解説をしたことがないので、あらためて簡単に紹介しておこう。

　Power Queryとは、Excelに付属するETL（「Extract, transform, load」 抽出、変換、取り込み）ツールである。ETLツールとは、データベースなどに外部から情報を取り込むときに、前処理をするツールの総称（クレンジングツールともいう）。CSVやテキストファイル、あるいはデータベースなどからデータを読み取り、変換処理のあと、Excelのシートにテーブルとして挿入できる。

　元々はExcel 2013で導入されたPower Pivot（現在のExcelデータモデル、データ分析アドイン）の一部が独立したものだ。Power Pivotは、データベースシステムであるSQL Server 2008 R2の一部として開発され、ExcelとSQLサーバーを連携させるためのツールだった。

　Excel 2013で、PowerPivotはSQL Serverから独立し、Excelの一部となった。Excel 2016からは独立したツールとして利用できるようになった。元々の主機能だったPowerPivotは、データ分析アドインとしてExcel本体から分離されたが、Power Queryは、Excel本体の機能の1つとして残った。これは、Power Queryがさまざまなデータを読み込み、前処理するのに便利だからである。

　Power Queryは、リボンのデータタブ左側の「データの取得」などから起動する。Excelを起動した後でないと、PowerQueryを起動できず、CSVファイルとExcelを関連付けして起動するのに比べると一手間多い。しかし、CSVファイルを読み込んだあと、文字エンコードを指定できる、必要なら前処理ができるといったメリットがある。

　Power Queryは、CSVやテキストファイルなどの一連の読み込み処理に関して「クエリ」という情報を作る。これは、ブック内に保存され、CSVファイルなどが更新されたとき、クエリを「更新」することで、クエリで作成したテーブルを更新できる。また、異なるファイルに対して同じ処理をするような場合にクエリをコピーして対象ファイルを書き換えるだけで、まったく同じ処理ができる（出力先のテーブルはクリエごとに異なる）。繰り返しや複数ファイルで同じ処理をするときに作業量を削減可能だ。

　とはいえ、Power Queryは、Excelには似ているものの、Excelとは異なる数式や処理記述（Power Query 数式言語、ここではM言語と表記する）を使う。CSVやテキストファイルを読み込んで、文字エンコードを指定してテーブルを作るといった作業ならば、手順だけを覚えればよいが、残念ながら、M言語習得の敷居はVBAよりも高い。

　Power QueryのM言語は、PowerQuery内でのGUI操作を記録する言語であるとともに、PowerQueryに読み込まれ表形式になったデータの処理言語でもある。GUI操作は、M言語で記録されていくが、表示させない限り、これは見えない。しかし、高度な処理の場合、M言語で直接処理を記述できるわけだ。

　M言語は関数型と呼ばれる言語で、従来の言語とはちょっと毛並みが違う。また、対象は、テーブルやリストで、これらをまとめて処理する。イメージ的にいえば、ワークシートの列をまとめて処理して列を作るような処理をする。とりあえず、文法的な紹介は、Microsoftのサイトの「Power Query M 数式言語のリファレンス」（https://learn.microsoft.com/ja-jp/powerquery-m/）にある。

Power Query内でカスタム列を追加

　まずは、適当なCSVファイルを用意する。どんなものでもいいのだが、ここでは、項目が3つの以下のようなものを使った。

"GID","Name","Code"
2 "0",".notdef","U+FFFF"
3 "1","A","U+0041"
    :

　Excelのデータタブにある「テキストまたはCSVから」を押すと、処理するファイル名を選択する「データの取り込み」ファイル選択ダイアログが開く。そこで適当なCSVファイルを選択すると、ファイルが読み込まれ、ダイアログが開く。

リボンの「データタブ」→「データの取得と変換」→「テキストまたはCSVから」を押すと、ファイル指定のダイアログのあと、このようなウィンドウが表示される。現在のバージョンではちょっと表示がバグっているが、CSVファイルが読み込まれてテーブルとして表示されている。ここで「読み込み」ボタンを押せば、そのままExcelのテーブルが作られるが、データの変換ボタンを押すと、Power Queryウィンドウが開く

　データが正しく認識されているようなら、「読み込み」ボタンを押すが、その前に文字エンコードや区切り記号を修正することもできる。

　「データの変換」ボタンを押すとPower Queryのウィンドウが開く。ここでリボンにあるコマンドを使って、さまざまな処理ができる。動作はウィンドウ右側の「適用したステップ」に順に記録されていく。

Power Queryウィンドウも、見た目はExcelと同じくリボンとテーブルが表示される。リボンからさまざまな操作を実行すると、ウィンドウ右側の「適用したステップ」に操作が記録されていく。操作は、実際には数式ウィンドウに表示されているようにM数式言語として記録されている

　まずは、簡単にM言語を使って、新しい列を追加する。リボンの「列の追加」を選び、「カスタム列」を押す。

リボンの列の「追加タブ」→「全般」→「カスタム列」、で新たに列を追加できる

　すると、ダイアログボックスが開く。ここで、カスタム列の式に以下のようなM言語式を入力する。

Expression.Evaluate("0x"& Text.Middle([Code],2,10))

カスタム列ダイアログで「カスタム列の式」に、Code列にあるユニコードコードポイント表記にある数値を16進数から10進数に変換するM数式言語を入れる

　これは、M言語式で文字列を式として評価する関数Expression.Evaluateと、文字列の一部を取り出すText.Middle関数を使っている。"0x"は、M言語で16進数数値を表すプリフィックス、次の&は、文字列の接続記号である。[Code]は、CSVファイルの３番目の列を表す。

　CSVファイルが読み込まれると先頭行が自動的に列の名前に変換される。3つ目のフィールドには先頭行を使って"Code"と名前がつき、[Code]でアクセスが可能になる。「Text.Middle([Code],2,10)」は、3つ目のCode列から、値を読み取り、その2つ目の文字から10文字を読み込むもの。

　Text.Middle関数では、指定された文字数が読み込めなくてもエラーにはならず、文字列の最後までが取り出される。このため、絶対に越えないと思われる大きな数字を3つ目の引数に指定しておけば、Text.Middle関数で2つ目の引数から指定された位置から最後の文字までを取り出すことができる。

　Code列には、"U+"で始まるユニコードのコードポイント表現が入っており、これで、先頭のU+を除いた16進数部分を取り出せる。これに16進数のプレフィックスである"0x"を付けて、式として評価することで、16進数表現された数値が得られる。これにより、16進数、10進数の変換ができる。

　これは、あくまでもM言語式による演算である。PowerQueryは、Excelにテーブルを読み込ませる前にすべての演算をするため、Excelのテーブルには、数値や文字、日付形式程度しか入れることができない。

　新しい列名に適当な名前（ここではCPとした）を設定してダイアログを閉じる。これで右側の「適用したステップ」に「追加されたカスタム」と表記されたステップが追加され、左側のテーブルの右側にカスタム列であるCPが追加される。

　今度は、Excelの数式を追加してみる。同じく、リボンの「列の追加」→「カスタム列」ボタンを押し、ダイアログを表示させる。今度は、以下のような式を入れる。なお、本質的な部分ではないので、ここでは数式の意味などは解説しない。

"'=XLOOKUP([@CP],UpperD,BlockName,""xx"",1,1)"

同様に、Excel数式の先頭にシングルクオートをつけたものを、ダブルクオートで括ったものをカスタム列の式に入れる。この段階ではカスタムセルには文字列として設定される。最後にリボンホームタブから「閉じて読み込む」→「閉じて読み込む」を使って、Power Queryを終了させるとExcelの新規シートにテーブルが読み込まれる

　式は全体がダブルクオートで囲まれているのでM言語では単なる文字列である。その中身は、Excelセル中で文字列を表すシングルクオート（'）を先頭に置いたExcelの式である。これでExcelに読み込まれた段階で、セルには数式が文字列として入る。万が一、数式中でシングルクオートを使っていた場合、M言語文字列の先頭にはシングルクオートの代わりに、数式に登場しない文字を入れておく。

　最後にリボンのホームにある「閉じて読み込む」→「閉じて読み込む」を押せば、Excelの新規シートにテーブルが読み込まれる。

テーブルには、Power Queryで処理したように、CP列とBlock列が追加されている。CP列は、M数式言語の式を計算した値が入る。Block列には、先頭にシングルクオートが、ついたままになっているので、これをリボンホームタブの「編集」→「検索と選択」→「置換」で削除する。検索する文字列にシングルクオート、置換後の文字列には何も入れないで、「すべての置換」ボタンを使って削除する。削除後Block列は自動的に数式と認識され再計算される

　なお、Power Queryウィンドウ右側の「名前」がこのときのシートのタブ名になる。また、Excelウィンドウの右側に「クエリと接続」作業ウィンドウが表示される。ここに表示されるクエリにも同じ名前が使われる。

　この状態では、最後に作成した列（Power Query中ではBlockと命名した）は単なる文字列のままだ。この列全体を選択して、「リボンホームタブ」→「編集」→「検索と選択」→「置換」を使って、シングルクオートを削除する（置換後の文字列に何も指定しないで実行）。すると、最後に追加した列は、数式となり、自動的に再計算される。

　Power Queryで先頭にシングルクオートを付けないと、テーブルが挿入／更新されたとき、セルは、見た目は数式でも文字列状態を維持し、再計算コマンドでも数式としては評価されない。これを数式にするには、すべてのセルを手動で一回編集状態にする必要がある。しかし、置換で先頭のシングルクオートを削除したときだけ、セルは数式として認識される。

　クエリを更新するたびに置換するのがちょっと面倒だが、これで、手作業で数式列を追加しなくて済む。クエリの更新と置換を、開発タブのマクロや自動化タブのスクリプトで操作を記録すれば自動化できる。

　この方法を使えば、任意のExcel数式をPower Queryの中で指定しておくことができる。Power Queryの処理は、コピーして編集し、別のソースに対しても適用可能なので、ここで数式を入れておくことでテーブルの作成が簡単になる。今回は、カスタム列を2つぐらいしか入れていないが、CSVファイルのデータを元に多数の列に計算式を手動で入れることを考えると、置換処理1回で数式を有効化できるこの方法がラクなことがわかる。

　作業を繰り返す可能性がなくても、似たような作業があったとき、クエリのエクスポート、インポートで他のブックに読み込むことが可能である。なので、できるなら外部ファイルの処理はPowerQueryを経由した方がいい。

　クエリのエクスポートは、Excelウィンドウ右側の「クエリと接続」作業ウィンドウの右クリックメニューにある「接続ファイルのエクスポート」でする。エクスポートされるのは、拡張子がodcの「Officeデータベース接続」ファイルである。

　インポートは「Excelのリボンデータタブ」→「データの取得と変換」→「既存の接続」を使う。表示されるダイアログの「参照」ボタンを使えば、任意のフォルダにあるodcファイルを読み込むことができる。

　外部ファイルをExcelに読み込む場合、データ整形や不要部分の削除、あるいは文字列置換などは、すべてPower Query側でやってしまうことで、作業を自動化できる。外部ファイルが更新されても、同じ手順を繰り返すのが簡単になり、使い方を知っていると知らないでは作業効率が断然違う。