以前法人番号検索ツールを作ったという記事を書きました。
この方法では法人名で検索できたのですが、複数検索には対応できていませんでした。
が!
今回は複数検索に対応した法人番号検索ツールの作り方をお届けします。
Contents
やりたいこと
簡単な流れはこんな感じです。
検索ワードをパワークエリに取込み、それをリスト化。
そしてリストからエンコードしてリクエストURLを生成し、そのリクエストURLに基づいて検索結果を取得する。
こんな流れです。
では、今回使う主な機能(ポイント)です。
- 列の分割と分割結果のタテ展開
- 検索ワードのエンコード
- エンコード結果を使ったURL作成
- 法人番号取得クエリの関数化
(変数宣言とURLの変数化) - カスタム関数による検索結果テーブルの呼び出し
検索ワードテーブルからリクエストURLを作るまで
検索ワードリストの作り方
まず、エクセルに検索ワードの入力欄を作り、パワークエリに取り込みます。
ちなみに、区切り文字を指定しているのは、後ほど区切り文字で分割するためです。そして全角なのは法人番号APIの仕様です。
そして、パワークエリに取り込めば、まず変換タブから「値の置換」を選択します。
で、全角スペースを半角スペースに変換します。
これはエラー対策処理で、検索ワードを入力するときにスペースが全角・半角混在した場合うまく分割できないため、半角スペースに統一するためにこのステップを入れてます。
次に列の分割から「区切り文字による分割」を選択します。
ここで重要なことは、「詳細オプション」を開いて分割方向「行」を指定することです。これにより分割結果が縦方向に展開されます。
このようになります。ちなみに上の区切り記号「スペース」は半角スペースを指しています。
検索ワードのエンコード
実はここが一番ネックでした。
パワークエリでURLエンコード関数と同じことができるのか?
これができたんですね。では見ていきましょう。
列の追加タブから「カスタム列」を選択します。
次に、関数を入力していきます。
Uri.EscapeDateString関数に検索ワード列を代入してエンコードした結果がこちらです。
ご覧の通り、エンコードできました。
念のため、ExcelのENCODEURL関数と比較しましたが、まったく同じ結果になったので安心です。
リクエストURLの作成
このステップでもカスタム列の追加を使います。
そしてここからの方法は二つあるので、両方紹介したいと思います。
「&」でごりごり結合してリクエストURLを作る方法
まずリクエストURLをコピー&ペーストします。
そしてリクエストURLのサンプルはこちらです。
なお、前回記事では所在地も特定できますが、今回は所在地検索はせずに法人名のみで検索する仕様としています。
【リクエストURLサンプル】
https://api.houjin-bangou.nta.go.jp/4/name?id= ≪アプリケーションID≫ &name= ≪法人名(URLエンコード)≫ &mode=2&type=12
次にURLを” ”で囲み、固定値化します。
最後に、法人名部分にエンコード列の値を投入するように、name=以下にエンコード結果列を指定します。
これで「&」で結合する方法でURLが作成できました。
Text.Combine関数で結合してリクエストURLを作る方法
もう一つの方法、テキスト結合関数であるText.Combine関数を使います。
と言っても上の方法とそんなに変わりません。
まずはカスタム列の式にText.Combine()関数を入力します。
次に()内にリクエストURLを貼り付けます。
次にURLを” ”で囲み固定化します。
そしてエンコード列を投入する箇所の前後を”で固定値かしてコンマ(、)で区切ります。
そして使用できる列からエンコード列を選択し、
最後にURL直前・直後を{ }で囲んで完成です。
これでリクエストURLができました。
ここでいったんクエリは閉じてエクセルに戻りましょう。
「閉じて読み込む」から読み込めばOKです。
法人番号検索結果テーブルの作成と関数化
法人番号取得クエリの作成
先ほど作ったリクエストURL(どれでもいい)をコピーし、Webからのデータ取得からデータ取得を進めていきます。
変換して読込んで・・・
あとは右端の列(テーブル)を展開して列名変更したり結合したりして最終的に表示したいテーブルの形に整えます。
例えばこんな感じに、です。
ちなみに、クエリの名称がURLになっているので、画面右のクエリの設定の名前を「法人番号検索」など分かりやすい名称に変えておきましょう。
法人番号検索クエリの関数化
まずはクエリの関数化とは。
今回の場合、インプット(リクエストURL)に応じたアウトプット(法人番号検索結果)を得ようとしています。
つまり、こちらが指定したリクエストURLを法人番号検索クエリに投入できるようにすることが、「クエリの関数化」というわけです。
ではその方法を見ていきましょう。
まず詳細エディターを開きます。
そして、編集画面が表示されます。
これを下図のように、2か所編集します。
この、let上段で入力したものが変数宣言と言われるやつです。
「( [変数名] as [データ型] ) =>」と記載します。ちなみにデータ型はテキスト形式の場合は「text」、数値形式の場合は「number」です。
これを意訳すると、「[変数名]apiURLを[データ型]テキスト形式の変数として使います!」といったとこでしょう。
そしてURLに変数apiURLを投入する形です。
これで完了して画面左のクエリフィールドを開くと・・・
このようにクエリ名の文頭に「fx」と表示され、関数化されたことが分かります。(ちなみに上段のテーブル3がリクエストURLテーブルです)
では、リクエストURLのテーブルから関数クエリを使って法人番号検索を取得できるようにしていきましょう。(ようやく佳境に入ってきました。。。)
作成したURLリストをもとに法人番号を取得する方法
まずはリクエストURLを作ったテーブルに戻り、列の追加タブの「カスタム関数」を選択します。
そして、関数選択画面で下記のように入力・選択します。
すると、このようにテーブル列が追加されました。
そしてここでエラー対策のステップを挟みます。
法人番号検索列を選択した状態で、行の削除から「エラーの削除」を選択します。
このステップを入れておくことで、検索結果該当なし場合にエラーとなり、テーブル展開ステップでのエラーを回避することができます。
そして展開するとこのような感じになるので、不要な列を削除するなどして整形して完成です。
おまけ:郵便番号をxxxxxxxからxxx-xxxxに変える方法
このままでは郵便番号が7桁のままで見にくいので、間にー(ハイフン)を入れてみましょう。
郵便番号列を選択して列の追加タブより例からの列の「選択範囲から」を選択します。
そして、開いた画面で郵便番号だけにチェックが入っていることを確認して、右端の入力欄に変換結果入力します。1行目の場合、「518-0617」ですね。
これを何度か繰り返すと、他の列の変換結果も表示され、上図のように変換関数が表示されるので、意図した結果になりそうな関数になっているかを確認し、OKを押せば完了です。
あとは、変換前の列を削除して返還後の列を並び替えたり列名を変えたりして完了です。
まとめ
長かった…。
まさかこんなに長くなるなんて思わなかったのですが、今回使った機能で特に大事だと思うものを再度あげておきます。
- 列の分割と分割結果のタテ展開
- 検索ワードのエンコード
- エンコード結果を使ったURL作成
- 法人番号取得クエリの関数化
(変数宣言とURLの変数化) - カスタム関数による検索結果テーブルの呼び出し
最後まで、お読みいただきありがとうございました。
- 今回紹介した法人番号の取得方法は、国税庁法人番号システムのWeb-API機能を利用して取得した情報をもとに作成していますが、サービスの内容は国税庁によって保証されたものではありません。
- 短時間における大量アクセスその他本機能の運用に支障を与えることはWeb-API利用規約により禁止されています。従って、複数条件で大量のリクエストURLを送信することはお控えください。