１．概要

Power Queryを用いてWebページ上の複数のPDFファイルから表のデータを抽出し、Power BI Desktopの地図ビジュアルにプロットする方法をご紹介します。

国土地理院のホームページに市区町村の位置情報が公開されているので、ここから全国の市区町村の位置情報のテーブルを作成しそれをPower BIのマップのビジュアルに描画したいとします。
位置情報は、ページ「日本の東西南北端点の経度緯度」内の「都道府県及び市区町村の東西南北端点の経度緯度」の表「市区町村の位置情報(リンクは全てPDF形式)」からとれます。
しかし、この位置情報の表は都道府県別に47のPDFファイルに分かれているので、Power Queryでこれらを全て開いて位置情報の表のデータを抽出し1つのテーブルにまとめてから、地図に描画します。

Webスクレイピングによるファイルからデータの抽出とテーブルへの加工の手順は以下のとおりです。
まず、表にある都道府県別のPDFファイルのリンクのURLのリストのテーブルを作成します。つぎに、各都道府県のPDFファイルからファイル内の全ページの全ての表のデータを抽出しテーブルにまとめるクエリを作成し、これをカスタム関数にします。最後に、都道府県別にこのカスタム関数を展開し1つのテーブルにまとめて整形します。

２．ファイルのリンクの取得

ここでは、ホームページ上の各ファイルへのリンクを取得しテーブルにまとめるクエリを作成します。

まず、[データを取得]から[Web]を選択し、[URL]に国土地理院のホームページを入力しOKすると、

[ナビゲーター]のウィンドウが開くので、[例を使用してテーブルを追加]をクリックします。

すると、[例を使用してテーブルを追加]のウィンドウが開き、上の画面を下にスクロールしていき参照したいテーブルが含まれていることを確認します。
下のテーブルに移り、列1の行1のセルをダブルクリックすると抽出できる文字列などのリストが出てくるので、スクロールしながらまずは北海道[341KB]を探し（北海道など途中まで直接入力すると候補が絞られる）クリックします。
続いて、列1の行2のセルをダブルクリックし同様に青森県[109KB]を探しクリックします。
すると、以下の行に都道府県名が自動で薄い色で入力されますが、各セルの先頭の県しか取得されていないので、さらに列1の行３のセルをダブルクリックし岩手県[97KB]を入力すると以下の行にファイル名が自動で入力され、47都道府県全て入力されていることを確認したら、今度は列2にリンクのファイル名を取得します。
列2の行１のセルをダブルクリックし北海道のファイル名である000230937.pdfを探しクリックします。続いて、列1の行2のセルをダブルクリックし同様に青森県の000230938.pdfを探しクリックします。
すると、以下の行にファイル名が自動で薄い色で入力されますが先頭のセルの県のみなので、列1の行３のセルをダブルクリックし岩手県の000230939.pdfを入力します。
これで47都道府県分全てのファイル名が入力されていることを確認したらOKし、このカスタムテーブルを読み込みます。

新しいクエリが作成されているのでクエリの名前を適当に変更しておきます（ここではgetpdfとします）。

[列1]と[列2]にそれぞれ都道府県名とファイルへのリンクの入るテーブルが作成されているのですが、少し加工します。
Power Queryエディターで、[列1]と[列2]に対して[列の分割]の区切り記号をそれぞれ[と/（[–カスタム–]を選択し入力）として都道府県名とファイル名の部分だけを抽出します。

そして、不要な列を削除し、列名をそれぞれprefectureとfile_nameと変更しておきます。

３．PDFファイルからデータを抽出

３．１．PDFからデータを抽出しテーブルに加工するクエリの作成

ここでは、Web上のPDFファイルに接続しページ内の表を抽出してみます。

どの都道府県から始めてもよいのですが、ここでは例として茨城県のPDFファイルで作成します。
まず、[新しいソース]から[Web]を選択し、[URL]に茨城県のPDFファイルのURL（https://www.gsi.go.jp/common/000230944.pdf）を入力しOKすると、[ナビゲーター]のウィンドウが開くので、[Page001]を選択します。
プレビューを確認すると、このファイルの1ページ目内の全ての表がテーブル形式で取得できていそうなのでOKします。

作成されたクエリの[クエリの設定]の[適用したステップ]の1つ目のステップ[ソース]を選択すると、茨城県のPDFファイルのページや表をレコードとしたテーブルが表示されます。

ここでは、全ページのページ内の全ての表をとりたいので、[Id]列が”Page***”であるレコードだけ抽出します。[Id]列の[テキストフィルター]で[指定の値で始まる]でPageとします（[詳細エディター]を開き直接入力してもよい）。
すると、PDFファイル内の各ページがレコードのテーブルができます（以降のステップは削除しておきます）。

ちなみに、この状態でデータウィンドウの[Data]列の各レコードにはTableが入っていますが、このいずれかのセルをクリックすると各ページ内の内容がデータウィンドウの下部に表示されます。

この各ページのTableの内容を展開すれば全ページの内容を抽出できそうです。そこで、[Data]列について全ての列を展開します。

 

すると、茨城県のPDFファイル内の全ての表のデータがテーブルに入っています。

ここからPower Queryを用いて都道府県や市区町村ごとに役所・役場や東西南北端点の緯度・経度のテーブルに加工するのですが、Power Queryの標準的な処理を繰り返していけば実現できるのでここでは各ステップの詳細の紹介は割愛させていただきます。大雑把な手順だけ紹介すると以下のようになります。

・先頭の不要な行を削除
・各レコードに都道府県市区町村を付与した列を追加
・役所・役場と東西南北端点の緯度・経度の各列を作成
・不要な行を削除
・列見出しを作成

 茨城県の場合は以下のように整理されます。クエリの名前を適当に変更しておきます（ここではgetpagesとします）。

３．２．カスタム関数の作成

つぎに、このクエリgetpagesを、ファイル名をパラメーターとしたカスタム関数にします。

[詳細エディター]を開き、先頭のletの前に1行挿入しファイル名のパラメーターを定義しておきます。
そして、Web.Contents関数のURLについて、ファイル名部分を消し前半部分とファイル名（パラメーター：filename）を&で結合するようにします。

これでカスタム関数getpagesができました。
このカスタム関数の挙動を確認するために、試しにパラメーターを入力して呼び出して結果を確認してみましょう。
例えば、filenameに000230949.pdfと入力すれば、

東京都のPDFファイルの全ページの全ての表が整理されたテーブルが出力されます。

４．全ファイルのデータの取得

都道府県別のファイル名のリストからそのリンク先のPDFのデータを取得してきて、テーブルに整形します。

ここで、クエリgetpdfに戻り、作成したカスタム関数getpagesを呼び出します。

[列の追加]の[カスタム関数の呼び出し]を選択し、[新しい列名]を適当に入力（ここではpagesとしました）し、[関数クエリ]で[getpages]を選択し、パラメーター[filename]は[列名]で[file_name]を選択します※。

※ ここで、もしFormula.FirewallのWarningが出るようでしたら、[ファイル]の[オプションと設定]の[オプション]で、[現在のファイル]の[プライバシー]の[プライバシーレベル]を[プライバシーレベルを無視…]にしておけば、実行できるようになります。

データウィンドウの生成された[pages]列の各都道府県のセルのTableをクリックすると各都道府県のデータがデータウィンドウの下部に表示され、Tableに各都道府県のテーブルに入っていることが分かります。

この[pages]列の全ての列を展開します。

すると、全都道府県の全ての表のデータがテーブルに出てきます。

いったんこれで必要なデータを抽出してくることはできました。

ここからはPower Queryを用いてビジュアルを作成するのにそれぞれ適切なテーブルへ加工していくことになります。
ここでは、緯度・経度を10進法の数値型に直しまた緯度・経度の列ごとの縦持ちのテーブルに整形しますが、これもPower Queryの標準的な処理を繰り返していけば実現できるのでここでも各ステップの詳細の紹介は割愛させていただきます。大雑把な手順だけ紹介すると以下のようになります。 

・役所・役場と東西南北端点のピボットを解除し緯度・経度のデータを縦持ちに
・60進法表記の緯度・経度を列の分割を用いて度、分、秒に分け10進法表記に変換
・不要な列を削除
・緯度と経度で緯度・経度の列をピボット
・自治体区分を追加

最終的なテーブルは以下のとおりです。

 

５．地図にプロット

このテーブルを用いて、ビジュアル[マップ]により地図上に全国の役所・役場をプロットしてみると以下のようになりました。

６．最後に

Power Queryを用いてWebページ上の複数のPDFファイルから表のデータを抽出し、Power BI Desktopの地図ビジュアルにプロットする手順を説明してきました。
Webスクレイピングとなると、Pythonなどでパッケージを用いたスクリプトを書いたり、ページソースの中から正規表現で必要なデータを抜き出してきたり、といろいろなスキルが必要な場合もありますが、Power Queryをうまく用いると必ずしもこれらに頼らなくともある程度のWebスクレイピングが実現できそうです。
Power QueryでWebスクレイピングしてテーブルまで作成できてしまえば、Power BI Desktopでそれを用いてすぐにビジュアルを作成できるので便利かと思います。

村野 直樹（Naoki Murano）