Power Query による公開 API の利用 (XML 出力編)


現場社員が自らデータ分析を行うための新しいサービスである Power BI for Office 365 の Power Query を利用して、公開されている API として記載されているデータを利用してみましょう。公開されている API は以下のサイトにまとめられていますので、ご活用ください。

日本の全エンジニアに捧ぐ!現在公開されているAPI一覧【2013年版】
http://www.find-job.net/startup/api-2013

今回のターゲットは、アニメマップさんが提供されている「アニメ番組表 API」です。例えばとして、広告会社の現場社員がテレビ局に今後のアニメ番組の方向性を提案するための現状分析の一環だと考えてください。同じように API が用意されていれば、社内外のデータは同じように分析可能です。
今回は簡単に操作するために、入力形式が URL で、出力形式が XML の東京都の番組データを利用してみましょう。完成すると、このようなレポートになります。

image

なお、今回は Power Query の少し高度な利用方法として、で関数を作成して利用してみます。Power Query では、関数まで作成できてしまうという、言語的な側面も持っている機能です。

XML データの取り込み

XML データの取り込みは、Power Query を利用すれば、超簡単です。

  1. まずは、[POWER QUERY] タブの [外部データの取り込み] – [ファイルから] – [XML から] を選択します。
    image
  2. ファイル名に URL (今回の場合は http://animemap.net/api/table/tokyo.xml) を入力します。
    image
  3. クエリ エディターが起動しますので、忘れないように [クエリの設定] ペインの [プロパティ] – [名前] に適切な名称 (今回は「アニメ番組 (東京)」) と入力し、表の 3 行 3 列目 (「response」の右側) の「Table」をクリックします。これで、取得した XML の response 要素の内容にドリルダウンします。
    image
  4. 表の 2 行 2 列目 (「item」の右側) の「Table」をクリックします。これで、取得した XML の response 要素下の item 要素の内容にドリルダウンします。image
  5. 取得直後の状態です。
    image
  6. 不要な列を削除 (列名を右クリックして [削除] を選択) し、
    image
    列名を変更 (列名を右クリックして [名前の変更…] を選択) します。
    image
  7. [クエリの設定] ペインの [読み込みの設定] を以下のように指定します。
    image
  8. これで、一応、形が出来上がりですが、[放送時間] 列の時間が「25:35」など、所謂 24 時間表記ではないので、次に、この変換処理を行う関数を作成します。
    image

関数の作成

関数の作成は少しコツがあるので、ここで慣れましょう。

  1. 上記のクエリ エディターを保存し閉じて、[POWER QUERY] タブの [外部デーの取り込み] – [その他のソースから] – [空のクエリ] を選択します。image
  2. 忘れないように [クエリの設定] ペインの [プロパティ] – [名前] に適切な名称 (今回は「fConvert24Time」) と入力し、[読み込みの設定] のチェックを全て外します。
    image
  3. [ビュー] – [詳細エディター] を選択します。
    image
  4. [詳細エディター] ダイアログに、以下のように入力します。関数の内容は後ほど記載します。

    let Convert24Time = (OrgTime as text) as time =>
      let
        TempTime = Text.Split(OrgTime, “:”),
        TempTime1 = Number.FromText(TempTime{0}),
        OutputTime = Time.FromText(if TempTime1 >= 24 then Number.ToText(TempTime1 – 24, “D”) & “:” & TempTime{1} else Number.ToText(TempTime1) & “:” & TempTime{1})
      in
        OutputTime
    in
        Convert24Time

    image

  5. クエリ エディターが以下のように表示されれば、関数の作成は完了です。次に、この関数の呼び出します。
    image

関数の内容

関数の内容は、1 行目 (let Convert24Time = (OrgTime as text) as time =>) でテキスト型の引数を一つ持ち、結果が時間型で返って来ることを示しており、2~5 行目に処理内容 (文字列を分割して、24 以上の時間は 24 引いて、時間型の値を生成) を、7 行目には処理で生成された変数 (OutputTime) を、9 行目には 1 行目で指定した名称 (Convert24Time) をそのまま記載します。

1: let Convert24Time = (OrgTime as text) as time =>
2:  let
3:    TempTime = Text.Split(OrgTime, “:”),
4:    TempTime1 = Number.FromText(TempTime{0}),
5:    OutputTime = Time.FromText(if TempTime1 >= 24 then Number.ToText(TempTime1 – 24, “D”) & “:” & TempTime{1} else Number.ToText(TempTime1) & “:” & TempTime{1})
6:  in
7:    OutputTime
8: in
9:    Convert24Time

関数の呼び出し

作成した関数を呼び出すように、最初に作成したクエリに変更を加えます。

  1. 上記のクエリ エディターを保存し閉じて、[POWER QUERY] タブの [クエリの管理] – [ブック] で [ブック クエリ] ペインを開き、「アニメ番組 (東京)」を右クリックし、[編集] を選択します。
    image
  2. [挿入] – [カスタム列の挿入] を選択します。
    image
  3. 「カスタム列の挿入」ダイアログが表示されるので、「放送時間」を選択し、[< < 挿入] をクリック後に、[新しい列名] に「放送時間 (24h表記)」、[カスタム列の式] に「fConvert24Time([放送時間])」を入力します。
    image

レポートの作成

レポートの作成には、いつものように Power View を利用します。

  1. [挿入] – [レポート] – [パワー ビュー] でパワー ビューのシートを追加します。
    image
  2. 以下の設定のタイル視覚エフェクト (レポート左上) を追加します。
    image
  3. 以下の設定の円グラフ視覚エフェクト (レポート右上) を追加します。
    image
  4. 最後に、以下の設定の集合縦棒グラフ視覚エフェクト (レポート下) を追加すれば完成です。
    image

ここまでの手順で、以下のようなレポート画面が表示されているはずです。グラフの色々なところをクリックすると、全てのグラフが絞り込まれた結果としてアニメーション表示され、非常に分かり易いですので、是非試してみてください。
image

更に効率的な利用方法

Power BI for Office 365 サービスの購入を行うと、作成した関数を含むクエリの共有が行えますので、現場社員間でクエリを共有することで、更に効果的なデータ分析が可能です。詳しくは、こちらを参照してください。
image

情報源

Power Query の式に関しては、こちらを参照してください。

Power Query の式について
http://office.microsoft.com/ja-jp/excel-help/learn-about-data-explorer-formulas-HA104003958.aspx

広告