窓辺の小石(18) PowerQueryでソフト開発

Excelで、不要な記号文字やスペースを置換で削除し表を整形して体裁を整えたのに、元データが古くて、また最初からやり直しなんて経験はないだろうか？こんな作業は、PowerQueryで処理すれば、再度処理を適用できるので、元データの変更にすぐに対応できる。Excelで行われている手作業の大部分は、Excelに搭載されているPowerQueryで実行できる。

ただし、PowerQueryはとっつきにくく、使われている「M formula言語」は、関数型言語でかなり難解。なので筆者も無理には勧めない。だが、「手作業を2回も繰り返したくない」と思うのであれば、ちょっとのぞいてみるのもいいだろう。なお、軽く使うのであれば、プログラミングは不要だ。

PowerQueryは、データベース問い合わせプログラム……だった

PowerQueryという名前からわかるように、このプログラムは、外部のデータベース（データソース）からデータをExcelに取り込むためのものだ。だがPowerQueryは、Excelシート上のテーブルやCSVファイルなどもデータソースとして扱うことができる。

PowerQueryの最大の特徴は、セル単位ではなく、テーブルに対して変換や変形、選択といった処理を行うところにある。入力となるテーブルの行はレコード、列はフィールドとなり、処理はテーブルの構造には依存しない。それは、行はレコードとしてすべて処理されること、列はテーブルをセルアドレスではなく、フィールド名（テーブルの見出し行）で扱うからだ。このため、表の構造が違っても、必要なフィールドさえ含まれていれば処理を繰り返し行うことができる。なお、ExcelのPowerQueryの処理結果は、シート（上のテーブル）になる。つまり、PowerQueryは、テーブルを読み込んで、別のテーブルを出力する。

PowerQueryの処理は「クエリー」と呼ばれる情報で管理されている。PowerQueryは、ユーザーがリボンなどで指定したコマンドをM formula言語としてクエリー内に記録していく。こうしたコマンドには、列の削除や追加（列の単純なコピーや数式を入れた列）、値の置換、選択、さまざまなデータ変換などがある。PowerQuery内でコマンドを実行すると、出力となるテーブルがプレビュー表示され、Excelでの作業とほぼ同じだ。こうして一回クエリーを作ると、あとは、「更新」コマンドをExcelから実行するだけで、元データの変更が自動的に結果テーブルに反映される。

PDFから表を抜き出してC#の定数宣言を作る

筆者は、さまざまな仕事でPowerQueryを使っている。たとえば、さまざまな規格の仕様書PDFに含まれているデータ定義を取り出してソースコードに定数宣言として埋め込むような作業、逆にソースコードから情報を取り出して表にまとめるといった作業などだ。以下のキーボードショートカット記事の表は、PowerQueryを使ってソースコードを処理したものだ。

＞ Chromebookの日本語入力を正しく理解する - キー割り当て検証や設定、使い方
https://news.mynavi.jp/article/20210708-1916613/

実例として、PDF内の表をソースコード（定数宣言）に変換してみる。元データがPDFに入っているとき、PDFリーダーから表をコピーしてExcelに貼り付けても、たいていの場合まともな形にならない。こういうときには、PDFをWordで開き、そこからExcelに貼り付けるとうまくいく。Wordは、PDFの表データを解釈して、自身の表形式データに変換する機能がある（もっともPDFがWordで作られた場合も少なくないが……）。Wordで表として認識されると、複数ページにまたがるようなものでも、ヘッダー、フッターを避けてExcelにコピーが可能になる。

今回は2列のデータをPDFファイルから持ってきた（もちろんWord経由。写真01）。まずは、Excelの「データタブ→データの取得と変換→テーブルまたは範囲から」を選んでPowerQueryでPDFから貼り付けた表データを読み込む（図01)。

写真01: PDF文書の表（VESA MCCS Ver.2.2仕様書）からデータを抜き出してExcelにコピーした

図01: PowerQueryは、上にリボン、下がプレビュー、右側にコマンド履歴（ステップ）がある。リボンからコマンドを実行していくと、ステップにそれが記録されていく。ステップは、実際には、M formulaのプログラムになっている

あとは、コマンドを使って、不要な文字の置換や列の削除や追加を行えばよい。具体的にはName列をコピーしたRemark列を作り、そのあと、リボンのホームタブ→変換→値の置換などを使って、Name列をC#の変数名となるように変換する。最後にカスタム列を作り、Name列、Remark列を使って値を設定する。

こうしてコマンドを実行していくと、左側に「ステップ」が追加されていく。この1つ1つがM Formula言語のステートメントだ。詳細エディタを使うとテキスト形式でM Formula言語のプログラムを見ることができる。M Formulaは、「関数型」と呼ばれるタイプの言語なので、引数に関数が入ることがある点にも注意されたい。

このクエリーでは、処理のほとんどはName列で記号文字を置換する処理で、リボンのコマンドを使って記録したものだ。最後にカスタム列を追加するときにIFを挟んで数式を記述した程度だ。

こうして作られたテーブルが（写真02）だ。このうち「// Define Constant」がC#の定数宣言文を生成させたものでこれが今回の処理結果になる。一回、このクエリーを作っておけば、元データが変わっても更新するだけで出力テーブルが書き換わる。

写真02: これがPowerQueryで処理した結果のテーブル。２つのテーブルが追加されており、右側のテーブルが元のデータから作られたC#の定数宣言。これをエディタなどでソースコードに貼り付ける

こうしたデータ処理は意外に面倒で、ついついエディタの置換機能やAWKなどの1行スクリプトなどの「力業」を使ってしまい、どう処理したのかという記録を残さないことになりがちだ。Excelでやっても、置換機能は履歴が残らず、かといって数式で文字を置き換えると関数が多重（写真03）になり、結果のチェックも面倒になる。慣れないうちは、1回目は、PowerQueryを使うより力業で解決したほうが早い。だが、2回目は、PowerQueryのほうが早い。