2017/07/08

[Power BI / Excel] 複数にまたがる Web ページからデータを取得する

Excel や Power BI Desktop のデータ取得の機能である Power Query (データの取得と変換) の話題です。

地震データを取得する

最近、また地震が多いのが気になりますよね。地震データは気象庁のホームページで随時公開されています。しかしながら、この公開データは、良い意味でデータ クレンジングのサンプルになり、違う意味では「集計」や「レポート作成」などの後工程をあまり考慮していないデータかもしれない、と愕然とします。
今回のお題とは離れた話題なのですが、ちょっとだけご紹介します。

http://www.jma.go.jp/jp/quake/quake_local_index.html

すべての数字が「全角」です。大きい、小さいの判断をするためには数値にしないと比較できないので、まずはこれを半角に変換する必要があります。同じく日付に使われている数字も全角です。良いか悪か別にして、Excel は全角数字をセルに入力すると、自動的に全角数字を半角の数字に変換してくれます。また、関数も用意されていて、ASC関数が全角のアルファベット、数字を半角に変えてくれます。(反対は JSC関数)

残念ながら Power Query エディターには全角→半角にしてくれる関数はありません。自分でカスタム関数を作って対応します。やることはものすごくベタで、全角のAは半角の A に、全角の1は半角の 1 に、というようにすべてを羅列するようなものです。

一方、Yahoo さんの地震情報は、数字は半角になっています。元ネタは気象庁のデータを使っていると思うので、後工程・再利用を考えて変換されているのでしょう。

Yahoo! JAPAN 天気・災害 地震情報
https://typhoon.yahoo.co.jp/weather/jp/earthquake/list/


全角・半角変換の手間を考えると Yahooさんのページからデータを取得したいところですが、地震の回数が多くなる、ちょっと長い期間でみてみたい、となると最初の1ページのデータだけでは足りなくなります。

Yahooさんのこの情報ぺージは、1ページから300ページまであり、かなり前の情報も Web ページから取得することが可能です。


300ページ目を開くと、今だと 2004年の2月後半からデータを取得することができます。1ページあたりに100件のデータをテーブル内で表示してくれます。
URL は以下です。

https://typhoon.yahoo.co.jp/weather/jp/earthquake/list/?sort=1&key=1&b=101

?から後ろがページを指定するパラメーターですが、一番最後のパラメーターの b=101 が、このページで表示する「開始番号」の扱いです。なので

1ページ目 b=1
2ページ目 b=101
3ページ目 b=201
4ページ目 b=301
・・・・・
10ページ目 b=901

といったルールになっていることがわかります。

複数の Web ページからデータを取得する

Power Query で最新100件(=1ページ目)を超える過去のデータを取得したい場合は、2ページ目、3ページ目、、、と複数のページ上のデータを取得する必要があります。

毎回、たとえば3ページ分のデータを取得して分析しよう、とした場合、すぐに思いつくのは URL 固定で 3 個のクエリを作成し、そのクエリを Append (追加) する方法でしょうか。


https://typhoon.yahoo.co.jp/weather/jp/earthquake/list/?sort=1&key=1&b=1
https://typhoon.yahoo.co.jp/weather/jp/earthquake/list/?sort=1&key=1&b=101
https://typhoon.yahoo.co.jp/weather/jp/earthquake/list/?sort=1&key=1&b=201

この3つのWebページのクエリ結果のテーブルを追加(Append)する方法です。

これでもいいんですが、URL固定、数固定にできない場合などを考慮すると、違う方法も覚えておくと便利です。それが「カスタム関数」を使う方法です。上記の URL であれば、b=1 や b=101 の部分の数値を変数、引数として扱い、結果をもらう、という方法です。

Excel / Power BI Desktop で「繰り返しの処理」のような方法でデータをとりたいな、と感じたときは、上述のように変数と関数を使うことを検討してみます。

データの読み込み処理を関数にする

Excel でも Power BI Desktop でも方法と手順は同じです。Excel の画面で手順を追ってみましょう。Excel 2016 のデータ タブにある「取得と変換」または「データの取得と変換」を使います。繰り返しになりますが、取得と変換はこれまでの外部データの取り込みに代わる新しい機能で Power Query と呼ばれていたものであり、Power BI Desktop のデータの取り込み・変換の機能(ETL機能)と同じものです。製品の更新タイミングによって若干の表現の差がありますが、ほぼ同じ位置や言い回しなので、脳内補完してください。あえて画面は少し前の Excel のスナップショットです。

データ タブの「(データの)取得と変換」と「新しいクエリ」や「データの取得」などのメニューから「Webから」を選びます。


データを取得する Web ページの URL を入れるダイアログが表示されます。
Yahoo!さんの地震情報ページの 1 行目からを表示する URL を入れて [OK] を押します。

https://typhoon.yahoo.co.jp/weather/jp/earthquake/list/?sort=1&key=1&b=1


今回は、Yahoo! さんの一般向けのページなので [匿名] のまま [接続] を押します。


指定したページにあるデータ情報からどのデータを取得するか指定する「ナビゲーターウィンドウ」が表示されます。テーブル形式でデータがまとまっている [Table 0] を選んで
[編集] を押します。[読み込み] を押すと、このまま取り込んでしまうので、いったん [編集] を押し、クエリ エディターにデータを読み込んで、変換・編集作業をします。


クエリ エディターが立ち上がります。取り込んだデータをもとにして、データの型が最初から列の属性として設定されています。気象庁のデータと違って、元データの数字が半角になっていること、マグニチュードの”M”が無いこと、などでマグニチュードは数値データとして、情報発表時刻は日付データとして認識されています。発生時刻が「ABC」の文字になっているのは 2017年7月8日 15時42分 ごろ という「ごろ」がデータに含まれているからです。最大震度が同じく「ABC」の文字になっているのは、半角数字の後に「弱」や「強」の文字がついているものがあったからです。


今回は発生時刻の「ごろ」や最大震度のお「強・弱」を扱う「データ クレンジング」処理は割愛しますが、このクエリ エディターでクレンジング処理をすると、クエリ エディターで「ステップ」として記録され、再利用することができます。

なお、マグニチュードでも文字列が入るデータがあるため、クレンジングの作業は必要です。

このクエリ エディタは基本 GUI で操作しますが、それらの操作手順は VBA のマクロ記録のように Power Query の M 言語で記録されます。M 言語で記述されたソースを編集するために詳細エディターを立ち上げます。[ホーム] タブの [クエリ] グループの [詳細エディター] をクリックしてください。詳細エディターを立ち上げるコマンドは他にも多く配置されています。


詳細エディターが立ち上がりました。詳細といっても、機能的には「簡易」です。
何度かお伝えしていますが、現段階で M 言語を深く習得し、詳細エディターでガリガリと操作を記述するような使い方をマイクロソフトは想定していないと思います。このエディターには本当に最低限の機能しかありません。そういうものだ、と思ってあまり深堀りしないほうがいいと感じています。

この簡易エディターで「引数」の指定をします。let の上に (page as number) => を挿入します。page という変数で、数値(number)型を引数として指定します、という意味です。


これで、page という変数(数値)が let 以下で使えるようになりました。これで 1 から始まる、101 から始まる、201 から始まる、という指定が可能になります。
URL の一番最後の値として置き換えたいのですが、注意点は page は「数値」であり、URL は「文字」であること。よって、数値を文字に変換してあげます。

Number.ToText(page)

この関数で変数 page の中の数値データが、文字のデータに代わります。URL の最後の b=1 の部分を以下のように書き換えます。

[変更前] /?sort=1&key=1&b=1")),
[変更後] /?sort=1&key=1&b="& Number.ToText(page))),


ウィンドウの右下の [完了] を押すと、エラーがなければ、パラメーターの入力を促す画面が表示されます。


画面左のナビゲーションの [クエリ] の上にある [>] 展開マークを押すと、このクエリ エディターで扱えるクエリが表示されます。(Excel の場合は、ブックに含まれるクエリであり、Power BI Desktop の場合は pbix ファイルに含まれるクエリを含みます)


引数(パラメーター)を準備する

ナビゲーションの Table 0 の前に [fx] アイコンからわかるように、この引数を設定する操作によって、Tablel 0 クエリは関数に代わりました。page パラメータに 1 を入れて [呼び出し] を押すと、1 からのデータの表を関数の「クエリの結果として」表示します。101 をいれると、101 からのデータの結果を表示してくれます。このクエリの結果はナビゲーションから削除することができます。削除するまでの操作手順のアニメーション GIF が以下です。


パラメータに、1、101、と入力し関数 Table 0 に渡し、その結果を表にすることができました。この 1、101、、、を手入力ではなくしたいのです。これが Power Query の繰り返しの処理です。

たとえば、常に10ページ分がほしいのであれば、1、101、201、、、901 までパラメータとして渡します。そのため、そのパラメータをリストとして用意します。

手順は、10個の連続した数値を用意して、1から901を作ります。いろいろなやり方があると思いますが、0 から 9 までの連続した数値から以下のように作ってみます。

0 -> 0*100+1 = 1
1 -> 1*100+1 = 101
2 -> 2*100+1 = 201
・・・
8 -> 8*100+1 = 801
9 -> 9*100+1 = 901

クエリ エディターで 0 から 9 までの連続した数値を作る方法は、数式で ={0..9} を使います。この数式の結果は {0,1,2,3,4,5,6,7,8,9} の連続した数値のリストを作成します。

クエリ エディターで、このリストをテーブルに変換して、新しい列を追加して、0*100+1 の数式を使って 1 から 901 までの数字を作ります。そこまでは以下のアニメーションGIFです。


パラメーターを使って関数を利用する

この操作で関数へ渡すパラメーターが用意できたので、このパラメーター列の横に新しく列を追加し、そこに作成した関数 Table 0 を指定して、1列目にある数値をパラメーターとして指定します。
関数 Table 0 はパラメーターで指定されたデータから始まる「表」をクエリの結果として返してくれます。個々の [Table] のリンクをクリックすると、1ページ100件分のデータのみですが、列名の横にある [展開] ボタンを押すと、すべての結果の表(テーブル)を結合して一気にデータ 10ページ 1000件を作成します。


0~9のパラメーターの最大値の 9 をさらにパラメーター化したり、多めの引数を用意して、取り込んだ後でエラー行の削除を行う、クレンジングをする、という方法を組み合わせることができます。

まとめ

今回は複数の Web ページのデータをまとめる、というお題でしたが、ポイントは「関数化」と「パラメーターの準備」による「繰り返し処理」と「クエリ結果の追加統合」の処理だと言えます。この方法を知っていると、Web ページだけではなく、他のところでも応用が利くテクニックです。

以上、少しでも参考になれば幸いです。

2017/06/23

[Excel] Microsoft Flow で Excel を使う

Modern Excel

Power BI系のツールを調べていると Modern Excel (モダンエクセル)という言葉を見ることがあります。
これは Power Query や Power Pivot といった、Power なんとか系ツールを使ってお仕事を効率的にするための新しい Excel の使い方をさす「造語」です。

WHAT IS MODERN EXCEL? - ExcelTV(英語)
https://excel.tv/what-is-modern-excel/

Excel は製品の歴史が古いにもかかわらず、今でも多くのユーザーに使われ、それも業務の第一線で利用されている珍しいソフトウェアです。そのため、現場にこれまで蓄積されたテクニックや技術が数多くあり、鉄板ネタ、その世界では常識、といった手法や使い方が存在します。その反面、レガシーな方法でも多くのことができてしまうため、新しいツールや機能を積極的に取り込む「心の」ハードルが高くなっている、という状況も散見されます。

現在、モダン Excel の世界で Power Query や Power Pivot の緩やかな Excel 本体との統合が進んでいます。最新アップデートの Excel ではデータ タブの [データの取得と変換] は Power Query です。Power Pivot アドインを有効にしていなくてもデータ モデルやリレーションシップ、メジャーを Excel で使うことが可能です。

[Excel] データの取得と変換が標準になりました
https://road2cloudoffice.blogspot.jp/2017/06/excel.html

リレーションシップとデータ モデル
https://road2cloudoffice.blogspot.jp/2015/02/blog-post.html

[Excel BI] ピボット テーブルとメジャー
https://road2cloudoffice.blogspot.jp/2017/05/excel-bi.html

さらに、これから Excel との連携が進んでいくのは Power BI だけにとどまらず、 PowerApps と Microsoft Flow といったツールであるのは、ほぼ間違いないでしょう。OneDrive などのクラウド ストレージに保存されている Excel ブックをデータ ソースとして、PowerApps や Microsoft Flow で利用するパターンです。何やら難しい仕組みのようにも見えますが、これらのツールはタブレットやスマホなどの利用を前提とし、かつアプリ開発や設定もノンプログラミングでできることを目的にしているようです。

Microsoft Flow と Excel 

将来重要性が増すだろうツールの1つである Microsoft Flow (マイクロソフト フロー) という単語を見ると、なにかワークフローを扱って、難しそう、というイメージがあるかもしれません。

Microsoft Flow
https://flow.microsoft.com/ja-jp/


自動化するワークフロー、ビジネス プロセスの自動化、などを見ると難しそうですが、Excel ユーザーの観点から以下のようなことが Flow でできます。

  • Excel を開かずにワークシートのテーブルにデータを自動追加できる
Excel ブックのデータを読み込む、取り込む、といったことは Power Query で可能でした。Power BI系ツールでは難しく、VBA を使わなければならないのはブックへのデータの「保存」や「書き込み」の処理系でした。このエリアが Flow や PowerApps によって可能になりつつあります。

ただし、対象となる Excel ブックは OneDrive や SharePoint Online のフォルダーの中に作成することが必須です。クラウドが利用可能であれば、欲しいデータを欲しい形で「Excel のワークシート(のテーブル)」で扱うことができるので、いろいろとアイディアが膨らみますよね。Excel ブックとして扱うことはもちろん、レポートや分析は Power BI の得意の分野ですから、Excel ブックをデータ ソースとして利用することができます。

どのくらい Flow による Excel ブックへのデータの追加・保存が簡単なのか、順を追って見ていきましょう。

Flow で Excel を使うポイントは「テーブル」

Flow の設定は Web 上で行います。そのため、Microsoft Flow の Web サイトに「サインイン」する必要があります。Office 365 の組織アカウントか、Microsoft アカウントの利用が可能です。Power BI と違って、hotmail などのフリーのメールアドレスの Microsoft アカウントの利用が可能ですが、作成したフローを他の人と共有できません。他のユーザーとなんらかのコラボレーションをする場合は、Office 365 の組織アカウントをベースにする方向性なのでしょう。一人で試すだけであれば、いくつかの制限がありますが Microsoft アカウントでも利用可能です。
他のユーザーとフローを共有しようとすると表示されるアラート

Flow のサイトでは数多くのテンプレート(フローのサンプル)が用意されています。もちろんゼロから作ることも可能ですが、テンプレートを利用することで開発・設定の時間を大幅に削減することが可能です。

Excel ブックを保存先として使う場合のポイントは2つあります。
  • 事前に使用するブックを用意し、ワークシートに「テーブル」を作っておく
  • Excel Online でも、Flow のテスト時にはブックを必ず閉じる(ロックを避ける)
テーブルの列名はどんな名前でもいいです。フローの設定で、用意した列と保存するデータの突合せを行うことができるからです。とはいえ、どんなデータがフローのテンプレートで用意されているかを確認し、必要な列を準備することを考えれば、事前にテンプレートでデータの名前を確認して、それとわかる列名でテーブルを作成することになります。

なお、Excel の「テーブル」をあまり使ったことがない人も結構いるようです。モダン Excel では基本、テーブルの使用が前提となっていると考えて差し支えないでしょう。行・列からなるセル範囲とは全く別の新機能を提供してくれます。テーブルについては当ブログでも過去に紹介しているのでぜひ参照してください。

自動的に Excel にデータを書き込む

Office 365 のユーザーは Office 365 の組織アカウントを使って Flow を使うのが良いでしょう。他のユーザーとのフローの共有はもちろんですが、サンプルとして使う「一定間隔で何かする」という繰り返しの設定の上限がマイクロソフト アカウントを使った場合(無料プラン)と変わります。さらに Flow の Premium ラインセンスを購入することで利用できる機能が変わります。上限を超えた設定をするとアラートメッセージなどでも表示されますが、プランの種類と制限の確認は以下を参照すると良いでしょう。

マイクロソフト フロー プラン
https://flow.microsoft.com/ja-jp/pricing/

シンプルな Flow と Excel の連携のサンプルを紹介します。とてもシンプルですが、これを Excel のみで実現するには、、、と考えながら見ると面白いでしょう。
アカウントはあえて Microsoft アカウントの無料プランと OneDrive を使ってみますが、Office 365 組織アカウントを持っているならば組織アカウントで試すのがいいと思います。サンプルの処理は「15分おきに、特定の文字列と現在の時間を Excel のテーブルに追記する(=最後の行に追加していく)」です。よって、前提として

・マイクロソフト アカウント取得済み
・マイクロソフト アカウントで OneDrive が使える
・できれば Excel が PC にインストールされている(Excel Online のみで操作可能ですが、あったほうが吉)

です。
大まかな流れは、

・Microsoft Flow で新しくフローを登録する
・OneDrive で Excel ブックを新規作成し、データ保存のためのテーブルを準備する
・フローの Excel 操作のアクションで準備したテーブルを指定する
・フローを動かす
・Excel のテーブルにデータが追加されているのを確認する

です。

1) Microsoft Flow にサインイン


Office 365 の組織アカウントを使うのであれば、ポータルのランチャーから [Flow] を選びます。直接 Flow のページへは、https://flow.microsoft.com/ja-jp/ でいきます。

Microsoft Flow サービスのページからサインインします。サインイン後にトップバー(タブ)の [マイ フロー] をクリックして、マイ フローに移動します。


2) [+ 一から作成] をクリックして、フローを新規作成、名前を付ける


フロー名 無題 の無題の部分にフロー名を入力します。[コネクタ] から [スケジュール] をクリックしてフローに追加します。このスケジュールは、設定する間隔で「動き出す」部品です。フローの開発はこのように「部品」を選択して、組み合わせていきます。

3) スケジュール 繰り返しの設定


繰り返しのスケジュールトリガーはプランの最小値の 15 分にしておきます。
Microsoft アカウントを使った無料プランでは、この間隔の最小設定は 15 分です。Office 365/Dynamics365 のプラン は 5 分(ただし、5分未満もお試し版で試すことができます)、Flow プラン1 は 3分、プラン2は 1分(最小設定)です。


次に、Excel へデータを追加するアクションを追加します。上図のように [+ 新しいステップ] から [アクションの追加] をクリックします。

4) Excel 操作アクションの追加と設定

結論から言えば、ここではアクションのみ追加します。OneDrive の Excel ブックとテーブルの事前準備ができていないためです。
[コネクタ] の下にある [アクション] から [Excel 行の挿入] を選択します。


このアクションを選択したのち、OneDrive の Excel ブックを指定することになるので、ここで、OneDrive に Excel ブックを作成します。

今回の Excel への行の追加のサンプルの「フォーマット」は自由ですが、アクションによっては追加できるデータの種類と数が決まっています。事前にテーブルと列を用意するにも、どんなデータを取得し追加するかを調べる必要があるので、実際のところはテーブルを用意する前にここでチェックすることになります。

5) OneDrive 上に Excel ブックとテーブルを作成する

OneDrive上に Excel ブックを作成します。作成するフォルダーは自由です。デスクトップ アプリケーションの Excel でブックとテーブルを作成し、OneDrive にアップロードしたものも当然使えます。私のサンプルでは 1_WORKS という名前のフォルダーを作り、そこに ブック1.xlsx を作成しました。
OneDrive の Excel Online だけで操作する場合の注意点は、テーブル ツールが使えないことです。ちょっと「?」な仕様なのですが、Excel Online にてテーブルの作成は可能で、作成されるテーブルは日本語環境であれば「テーブル1」になります。しかし、テーブルの名前の確認や、名前の変更は Excel Online からできません。

以下のアニメーションGIFは、Excel Online だけでテーブルを作る手順です。 列名は 1, 2,3,4 で連番にしました。テーブル名の指定はできないので、この場合は「テーブル1」になります。[挿入] タブの [テーブル] で作成ができます。


なお、テーブルを作成し終えたら、Excel Online のブラウザは閉じてください。
コンシューマー OneDrive の Excel Online は、ブックを開くと「編集モード」で開いているため、ブックにロックかかり、Flow によるデータ追加が失敗するからです。(これは、業務運用上大きな考慮点になりえます)

6) アクションに Excel ブックを関連付ける

Excel ブック操作のアクションを選択すると、マイクロソフト アカウントを使用しているので OneDrive へサインインになりますが、接続先のクラウド サービスを変更することは可能です。たとえば、OneDrive for Business や SharePoint Online ドキュメント フォルダーにある Excel ブックも、適切なアクセス権が設定されているアカウントを使うことで利用可能です。

Excel 行の追加のアクションを追加して、OneDrive 上のブックを指定します。ブックを指定することで、ブックに含まれるテーブルがリスト表示されるので、それを選ぶと、テーブルに含まれる列すべてが表示され、列に追加するデータを指定することが可能になります。


7) 追加データを列に設定する(行の追加日時の設定方法)

業務利用の場合は、イベント間隔の部品 [スケジュー 繰り返し] と Excel ブックへのデータ追加の部品 [行の挿入] の間に「なんらかの処理をする部品」を配置して、その結果を Excel に取り込みますが、今回は直接 [行の挿入] で入力データを設定します。
文字列はそのまま同じ文字列挿入の繰り返しになりますが、ここに変数や関数があれば、イベント毎に処理をして Excel に書き込むことができます。
Excel ユーザーとして、だいたい一番最初にやりたくなるのは、行が追加された日時(タイムスタンプ)を入れることです。ワークシート関数に NOW() を入れたけど、ワークシート更新するたびに数値が変わる、どうすればいい?のアレです。

列1に文字列の「Excel-Flowテスト」
列2に Flow で日時を取得するワークフロー定義言語の関数 utcnow()
列3に 世界標準時刻 utc に 9 時間追加した日本標準時刻
列4に yyyy/MM/dd HH:mm:ss 表示の日付・時刻

上記データを追加するための列設定は以下です。

列1
Excel-Flowテスト

列2
"@{utcnow()}"

列3
"@{addhours(utcnow(),9)}"

列4
"@{formatDateTime(addhours(utcnow(),9),'yyyy/MM/dd HH:mm:ss')}"

特に列4の英文字の大文字・小文字に注意してください。大文字の MM は月を、小文字の mm は分を、HH は24時間表示など、それぞれ意味があります。
utcnow() や addhours()、formatDateTime() は「ワークフロー定義言語」の関数です。M言語や DAX、R言語も Power BI でやらなきゃなぁ、と思っていたところにワークフロー定義言語 (Workflow Definition Language)がやってきました!これも避けては通れない言語でしょうね。ただ、大きなトレンドは「如何にコードを書かせないか」なので、将来的に学習コストは最小限になることを期待しましょう。
また、関数の前後についているダブルコーテーションは必要です。


ワークフロー定義言語については、まずは以下を参照されるといいでしょう。

Azure Logic Apps のワークフロー定義言語
https://docs.microsoft.com/ja-jp/azure/logic-apps/logic-apps-workflow-definition-language

また、Microsoft Flow は Azure Logic Apps をベースに作成されています。上記のようにワークフロー定義言語などの資料は Azure Logic Apps のものが参照できたりします。
Microsoft Flow と Azure Logic Apps の関係は以下のページが参考になります。

Flow, Logic Apps, Functions, WebJobs の比較
https://docs.microsoft.com/ja-jp/azure/azure-functions/functions-compare-logic-apps-ms-flow-webjobs

8) フローを保存し、動かす

フローの作成をクリックすると、保存され、実行されます。


エラーがなければ、完了をクリックのメッセージが表示されます。[完了]5D; の 5D; はバグですね。


完了を押さずとも、初回のフローが実行されて、その結果が再描画され表示されます。


完了をクリックすると、編集モードが終了します。
Excel ブックを確認すると、以下のようにデータが追記されていることが確認できます。


ダブルコーテーションで囲まれている文字を挿入しているので、Excel としては "2017/06/22 16:20:05" は日付データのシリアル値ではなく、文字列です。列2から列4のワークフロー定義言語の書き方の検討余地はまだあると思いますが、たとえば "@{utcnow()}" の両端のダブルコーテーションを最初からとると駄目です。少し動きが怪しいのですが、ダブルコーテーションをつけることで確実に関数として動きますが、最初から外して関数を利用しようとすると「文字列」として処理されます。ただし、一度ダブルコーテーション付きで登録し動いているフローを、あらためてフローの編集で両サイドのダブルコーテーションをはずすと、ダブルコーテーションがない日付文字列が入ります。どうやらデータ挿入のためのワークフロー定義言語関数の処理と JSON の構成方法に課題がありそうです。まずはダブルコーテーション必須で覚えておいたほうがよさそうです。

ちなみに、これでタイプミスなどでエラーになった、もしくは上述のワークフロー定義言語の書き方を変える、などフローを更新した場合、間隔の15分待つことはありません。[再送信] や [今すぐ実行] をすることで、最小設定時間以内でもフローを動かすことができます。


Excel のテーブルと Flow

Flow で Excel を使う場合は、事前にExcelのテーブルを用意しますが、Flowにおけるテーブル利用時の特徴・注意点は「セルの数式は使用できない」です。数式によって値を算出するセルをテーブルの列として使うことができません。Flow によって新しく追加した行には、テーブルで事前に定義された数式はコピーされません。A列に1、B列に2だけをいれて、C列で =A+B で 3 にするようなパターンです。コピーされないというより、数式がクリアされる感じです。

文字列や数値はそれほど扱いに困りません。何も設定していないセルの書式「標準」で事足ります。一方で、シリアル値は、上記の 8) で紹介したように utcnow() で世界標準時をとり、addhours() で9時間足せば日本の時間になりますが、 2017-06-22T09:50:51.0064743Z のような記述になり、このままセルに入力しても日付データとして Excel は認識せず、文字列として扱います。また繰り返しになりますが、ダブルコーテーション問題もあります。

なんだ、結局使えないじゃないか、と考える前に、この「Excel を起動することなく、自動的に、ブックにデータを追加する」という仕組みは、これまでプログラム開発が必要になるものでした。それ、Excel の仕事じゃないですよね?と言われる範疇のものです。これをノン・コーディングで可能にするのが Flow です。

後処理を Excel 中心にすると、どうしてもビックテーブル(ビックデータじゃありませんよ)で考えがちです。ビックテーブルとは、すべてのデータを含んだ1枚の大きな表です。その Excel のビックテーブルと、数式が使えない、ブックを開いているとロックする、といった Flow の制限を組み合わせると活用が難しく感じられますが、Power BI を使ってデータ ソースとして Excel ブックを指定すると Excel でブックを開くことなくデータを取得することができ、Power Query のデータクレンジングとデータの型変換を持つ ETL 機能を使えば、ダブルコーテーション付きのテキスト文字列を日付のシリアル値に変えることはクリック操作のみで可能です。異なるブックのテーブル間のリレーションシップによるマージも可能です。

制限や考慮点がある、情報がまだまだ少ない、といった状況ですが、モダン Excel の重要な要素の1つであることは間違いないので、引き続きウォッチしていく価値はあると思います。

まとめ

Excel と Flow の関係を紹介してきましたが、Excel を扱うアクションは行の挿入だけではなく、行の取得もあります。取得するを開始する行番号をスキップ数で指定し、そこから指示した行数分のデータを全列分取得します。Flow の内部では、取得したデータは JSON形式で保持され、次のアクションで利用されます。

どんなことが Flow と Excel の組み合わせでできるかはテンプレートを参照するのが一番です。Excel を含むテンプレートの一覧は、Flow のトップページの検索ボックス [最初に使用するテンプレートまたはサービスを探す] で Excel を入力して検索すると表示されます。


以上、長くなりましたが、参考になれば幸いです。

2017/06/15

[Excel] Excel で JSON データを読み込む

この前の投稿でご紹介したように、Power Query が「データの取得と変換」となって Excel の標準機能となり、様々なデータの取り扱いが可能になりました。(2017年6月現在、Office 365 サブスクリプションの 最新の Excel が機能拡張の対象となります)

データの取得と変換である Power Query は、アドイン単体としての機能追加、さらに Power BI Desktop の登場によって、Power BI Desktop の ETL 機能 (Extract, Transform, Load)として拡張が行われてきました。Power Query は、Excel そして Power BI Desktop のデータの取り込み、変換・加工、ロードを受けもつ ETL 機能として今も進化し続けています。

JSON 形式のデータをプログラミングなしで取り込む

この進化し続ける「データの取得と変換」機能で、すぐにでも使ってほしいのが CSV データの取り込み機能ですが、人によっては JSON 形式データの取り込みのほうを重宝するかもしれません。

というのも、JSON 形式のデータ取り込みは、以前からも Power Query を使ってできていたのですが、現在は空のクエリから詳細エディターを開いて Power Query 関数を手で記述することなく、クエリ エディターのクリック操作のみで取り込みが可能になったからです。

また、JSON形式のデータを表形式に変換してワークシート上に読み込むためには、VBAを使う方法がこれまで多く紹介されていましたが、VBAでプログラムすることなく、JSON形式のデータをワークシートに展開することができるようになりました。

Web から JSON でデータを取り込む

実際のところ、JSON形式のデータによるテキスト ファイルをフォルダーから読み込むよりも、Web 上での検索条件設定の結果で、JSON形式のデータが表示されることが多いと思います。

サンプルとして、IT勉強会の告知・募集でお世話になることが多い connpass さんの API を使ってみたいと思います。

https://connpass.com/


connpass さんは、イベントサーチ API を提供していて、検索クエリの条件に応じた一覧を JSON 形式のデータとして取得することができます。

connpass API リファレンス

Web API や REST API でデータ提供サービスをしています、という場合、上記のような API リファレンスのページが必ずありますので、探してみてください。

たとえば "BI" というキーワードでイベントを検索するための URL は以下のようになります。

https://connpass.com/api/v1/event/?keyword=bi

この URL で、以下のような JSON 形式のデータによるレスポンスがブラウザに表示されます。


このデータを、Excel のワークシートに展開できるように2次元の表形式に「変換」して、私たちが読めるようにデータを加工することが、データの取得と変換のクエリ エディターだけでできます。上述のように VBA などでプログラミングをすること無しに可能です。

シンプルなデータの取り込み手順

JSON データの取り込みには2つの方法があります。ファイルから取り込む方法と、Web から取り込む方法です。ファイルから取り込む方法を使っても URL を指定することで Web からの取り込みが可能ですが、今回は「Web から」を使ってみます。


「Web から」のデータ取得は、これまでの Web クエリよりも高機能になっています。HTML ページの table だけではなく、今回のように JSON にも対応しているのです。

connpass の Web API 利用はサインインする必要がありません。以下のダイアログで "PowerBI" キーワードを含むイベントを検索する URL を入力し、OKをクリックします。(上記 JSON サンプルのキーワード "bi" の検索結果の数が多かったので、キーワードを PowerBI に変更しています。注意してください。)


接続が完了するとクエリ エディター ウィンドウが立ち上がり、以下の画面が表示されます。


注意点は、ここでファイルのアイコンの [connpass.com] をダブルクリックしてはいけません。アイコン上でマウスオーバーすると「開くにはダブルクリックします」というツールチップが表示されますが、ダブルクリックすると、現時点ではテキストファイルとして処理されてしまいます。リボンの [変換] タブの [形式を指定して開く] から [JSON] を選んでください



Json 形式で開くと以下のデータが表示されます。


results_returned、events、results_start、results_available の意味は、上述の API リファレンスに解説がありますので、詳細については後で参照してほしいのですが、情報から「指定した(PowerBI)キーワードの検索結果の総数は 19 件で、このファイル(データ)に含まれるのは 10 件、検索開始位置は 1 件目からですよ」という意味です。

あらかじめだいたいの件数がわかっている、もしくは取得する件数に上限を付けることができるのであれば、取得件数を 20 件に指定して全件を一気に取得することができます。PowerBI のサンプルは件数が少ないので、以下の検索条件にしてみます。

条件を変更(URLの変更)のため、クエリの設定の [適用したステップ] の [ソース] の横にある歯車マークをクリックします。


ダイアログの URL を変更します。取り込む件数の上限を 20 とするパラメーターの &count=20 を追記した URL です。


[OK] を押すと、results_returned が 19 に更新されます。


今回は例をシンプルにするために、検索結果で全件を取り込むパラメータを追加しました。また、匿名アクセスで利用が可能なので、認証に関する設定はありません。これらへの設定・対応はもちろん可能です。あらためて別の機会にご紹介したいと思います。

今、この状態は、検索したいキーワードを設定した URL をサーバーに送り、その結果を JSON 形式のデータとして Web 経由で 19 件取得しています。
この表示されているリストの events の List のリンクに、JSON 形式で 19件の検索結果の格納されています。
List のリンクをクリックすると、以下のように List リンク内のリストが 19件のデータとして展開されます。


Record のリンクをクリックすると、クリックした1件分だけの内容を確認することができますが、今回はすべての Record(勉強会)の詳細を一気に展開したいので、ここでリボンにある [テーブルへの変換] をクリックして、19件のレコードを含むリストを、テーブル(表)形式に変換します。すでに1件1レコードとして認識されているので、テーブルへの変換のダイアログのオプションはそのままで、[OK] ボタンをクリックします。


リストだったデータがテーブル形式になりました。


ここで Record リンクをクリックすると1件分のみの展開をしますが、テーブル形式に変換したことによる「列名」の Column1 の横にある [展開] ボタンを押すと、Record に含まれるデータをテーブル形式1件分のデータのみなした場合の列名の一覧が表示されます。ここで取り出す列を絞り込むこともできます。今回はすべての列を選択し、かつ、列名が長くなるので、[元の列名をプレフィックスとして使用します] のチェックをはずして、[OK] ボタンをクリックします。


JSON形式の元データを、21列19行のテーブル形式のデータに変換することができました。クエリ エディターの操作のみで1行もコードを書かずにここまでできました。


テーブル形式になったデータを Excel 向けに加工する

今回は Excel の「データの取得と変換」を使って connpass から検索結果を JSON 形式のデータで取得しました。その後、ここまでの処理・操作で、データはテーブル形式になりました。それぞれの列名がどのような意味なのか、connpass の API リファレンスのレスポンス フィールドで確認することが可能です。

ここで、必要な列や行のみを残す、といった加工が可能です。ここからの処理は、JSON だから特別、というものはなく、普通のテーブル形式のデータのフィルター オプションを操作する感覚でできるのは、クエリ エディターを使ったことがあれば理解できるでしょう。まだ慣れていない方は Power Query によるデータの加工や絞り込みについて、もう少しだけ情報収集するといいでしょう。

この connpass のデータや、特にサーバーからデータを取得した際に、Excel ユーザーが一瞬「おや?」と思うのは、日付データの扱いです。この日付データのトピックだけで結構長いお話になってしまうので、ここで詳細は割愛しますが、1つだけ意識してほしいのは、サーバーの日付形式のデータは、そのまま Excel で扱うことができない場合がある、ということです。


上記はデータ変換をせずにテーブル形式に変換したJSONデータをワークシートに読み込み、列名 started_at の1行目のセルの書式を表示したものです。2017-05-20T13:00:00+09:00 は勉強会・イベントの開始日のデータですが、Excel は単なる文字列として認識し、日付として扱っていません。
多くの場合、Excel は日付「らしい」文字列のセルへの入力があると、日付データの「シリアル値」に変換し、表示形式によって人間が日付と認識できるデータに見た目上変換します。残念ながら、2017-05-20T13:00:00+09:00 という ISO-8061 形式の文字列は Excel によって日付データと認識されなかった、となります。このままではシリアル値として扱っていないため、日付関連の関数の利用や演算ができません。

そのため、クエリ エディターで Excel が日付として認識できるように加工します。
データをワークシートに読み込む前の、クエリ エディター上で、対象となる日付のデータの started_at の列データを変換します。
started_at の列名をクリックし列を選択した状態で [変換] タブの [データ型の検出] を使ってもいいですし、この日付データ型は [日付/時刻/タイムゾーン] と呼ばれるものなので、列名横のデータ型のボタンを押して、明示的に選択することで変換可能です。[ABC 123] のアイコンが地球儀と時計のアイコンに変わります。


この変換ステップを行うことで、[日付/時刻] に変換できるようになります。[日付/時刻/タイムゾーン] に一度変換しないで、いきなり [日付/時刻] を選択すると Error になるので注意してください。データ型ボタンで [日付/時刻] を選ぶと、列タイプの変更 ダイアログが表示されるので、[新規手順の追加] を選んでください。[現在のものを置換] はタイムゾーン付きのデータに変換したステップを置き換えてしまうのでエラーになります。


このように日付データを変換して Excel のワークシートに読み込むことで、シリアル値として扱うことができます。

日本語データも特に問題なく扱うことができています。イベント(勉強会)の概要データの description は HTMLタグを含むテキストデータとして取り込まれています。ここからまた何らかの判断をしたい場合は、クエリ エディターの詳細エディター上で M 言語を使ってやってもいいですし、ワークシートに取り込んだ後で、関数や VBA を使ってもいいでしょう。


実際は考慮すべき点がいっぱい

今回は JSON 形式のデータでも、取得と変換のクエリ エディターを使うことで、プログラミングすること無しに Excel ワークシートに取り込むことができることを紹介するのが目的でした。しかし、それ以外のところで考慮すべきことが出てくるのが実際でしょう。

たとえば、取得する件数。サービスによっては上限が決まっていて、それ以上のデータの取得は、今回のような件数の指定のほかに、ページ数指定や、オフセット指定を使うことが推奨されます。
Power Query / 取得と変換のクエリ エディターで、詳細エディターを使ってこれらへの対応が可能です。ページ数やオフセットの「繰り返し」の処理は、URLを組み立てる一連のステップを「関数化」して、ページ数やオフセットを引数として渡す、という方法を使います。

この件数の上限への対応は結構「頭の体操」的なアイディアが必要になります。場合によっては CData さんの ODBC ドライバーを使うと幸せになれることがあります。
サイボウズの kintone の API も1回のリクエストあたりのデータ取得件数の上限がありますが、CData さんのドライバーを使うことで、その上限を気にせずにデータの取得が可能になります。

CData ODBC ドライバー一覧
https://www.cdata.com/jp/download/?f=odbc

Power Query/Excel 取得と変換/Power BI Desktop も標準機能としてさまざまなデータソースに対応していますが、CDataさんのような専業メーカーさんの ODBC ドライバーを試してみると、意外な発見や、解決策を見つけることができるかもしれません。

また、匿名アクセスではなく、ユーザー名+パスワード、アプリケーション登録による Web キーの利用など、サービスによって認証の方法はさまざまです。
connpass と同じくらい IT 勉強会の告知・募集・管理ツールで人気がある Doorkeeper の場合は、検索の URL の送信と一緒に Header データに認証情報を入れる必要があります。connpass は「Web から」の「基本」を使いましたが、Doorkeeper は「詳細設定」を使って、Header に認証情報をセットして、検索リクエストを送信する必要があります。Doorkeeper の API の解説には、Power BI Desktop や Excel クエリ エディターの具体的な設定方法や手順はないので、苦労するポイントでしょう。

登録して、Public API Access Token を取得
HTTP要求ヘッダーに Bearer を使ってトークンを追加
正直いうと、Doorkeeper API で Authorization Bearer に行きつくまで結構な時間がかかりました。

長くなりましたが、Excel のデータの取得と変換という新しい外部データ取り込みの仕組みを使うことで様々なデータソースへ接続し、様々なデータ形式のデータを扱うことができます。まだまだ進化中ですが、その方向はなるべくコーディングさせない方向で、JSON形式のデータであっても、コーディングなしでテーブル形式に展開して、ワークシートに取り込むことが可能です。

食わず嫌いせずに、この新しい機能をぜひ使ってみてください。

[追記] 最後まで読んでいただいてありがとうございます。取得と変換の機能の一つの「ピボットの解除」もJSON処理同様これまでVBAでなければできないと言われていた処理です。こちらもおすすめ機能なので是非使ってみてください。
https://road2cloudoffice.blogspot.jp/2018/03/blog-post.html

[追記] REST APIで取れないデータをWebページスクレイピングでとる方法を追加しました。
https://road2cloudoffice.blogspot.com/2020/05/excel-web.html
Powered by Blogger.

自己紹介

自分の写真
1989年新卒で日本IBMに入社しダウンサイジング担当としてホストコンピュータと繋げるオフコン、UNIX、PCサーバーのプロジェクトを担当。1997年 MSKK(現日本マイクロソフト)入社、NT4出荷に伴い企業向けサポート部門のビジネスマネージャーとして Excel 使いとなり、2002年 にMSMVPなどをサポートするユーザーコミュ二ティ部門を設立、部門をリード。2006年にMSKK退職後、企業向けのITトレーニング会社・団体に携わり、2014年頃よりPowerBI勉強会主催メンバーの一人として参画、そのコミュニティ活動で MSMVP for Data Platform PowerBI 2017受賞。https://mvp.microsoft.com/ja-jp/PublicProfile/5002635 同年にMVP Awardを返上し、アマゾン ウェブ サービス ジャパンに入社、コミュニティプログラム担当として現在に至る。