2015/01/27

Power Query を使って絞り込んだデータを取得する - 日付編

SharePoint リストなどからデータを取得し、Excel で加工、集計をする場合、おおよそ全件データを取得し Excel のテーブルにデータを展開してから、ピボットテーブルを使ったり、ワークシート関数を使うことが多いと思う。現在 Excel は百万行を超えるレコード(行)を扱うことができるため、実務上はほぼ問題ないが、場合によっては、絞り込んだデータのみを扱いたいときがある。

過去のデータは一切関係なく、たとえばセミナー申込みの管理などで今日(または指定日)以降に実施される予定コースの登録者アイテムのみが欲しい、といったケースだ。後工程でデータの加工、集計をするのだが、そこで絞り込みを行うのではなく、必要なデータ「だけ」がテーブルに存在していたほうが都合がよい場合だ。

SharePoint リスト接続は全件データをテーブルもしくはピボットキャッシュに取り込む。Office データ接続は上記に加えてデータ モデルに全件データを読み込んでから、Excel 側での処理が行われる。

OLE DB クエリ接続もしくはモデル OLE DB クエリ接続の Power Query の場合も、基本的には全件データを Power Query に取り込んでいる。しかし、Power Query の場合は、Power Query エディターを使って、データの絞り込みや、並べ替え、カスタム列の追加を行ってから Excel に渡すことができる。

(注) SharePoint リスト接続、Office データ接続、OLE DB クエリ接続、モデル OLE DB クエリ接続については、こちらの投稿を参考にしていただきたい。

Power Query のクエリ エディターの画面で絞り込みを行っているところ
絞り込んだデータをどうしても欲しい場合は、Office データ接続によるデータ取得ではなく、Power Query を使わざるを得ない。2015年1月の段階では、Power Query のモデル OLE DB クエリ接続は Excel Online でのデータ更新ができない制限があるが、この制限の実務上の問題がなければ、Power Query を使うことになる。

日付による絞り込み

絞り込みの条件はさまざまあるが、今回は上述のように「日付」に注目して、その絞り込み方法を紹介したい。
とはいえ、Excel ユーザーのとっては有難いことに、Excel のオートフィルターを使った絞り込みと同等の操作を行うことで実現が可能だ。

Power Query エディターの「日付/時刻フィルター」
Excel オートフィルターの「日付フィルター」
Power Query の場合、このオプションに加えて関数を使って指定することが可能なのが特徴だろう。特に「今日」より~ という指定が可能なことだ。

Excelのオートフィルターでも今日より後、といった「今日」という指定は可能だ。
しかし、この「今日」のボタンを押すと、「2015/1/25」といった今日の文字の値が入力される。Excel のオートフィルターという使い方では問題ないが、データ接続のデータ更新により SharePoint リストなどのデータ ソースからデータを抽出する場合は「今日」は「その日」でなければ困る場合が多い。
Power Query エディターの日付/時刻フィルターの「今日」や「明日」の扱いは、Excel のオートフィルターとは異なる。
「今日」を指定しても Excel のように日時を指定するのではなく、Data.IsInCurrentDay([実施日]) という関数を使って配列(複数件データ)を取得して、それをテーブルに展開するのだ。

Power Query エディターの日付/時刻フィルター オプションの「今日」
「今日」を指定したときの式
この指定であれば、データ更新をすると「その日」のデータを持ってくることができる。

関数を使った指定

上図のように、Power Query エディターで絞り込みなどをすると、その操作はすべて「式」として記録さている。

たとえば、「今日より以降(後)のデータ」を指定するときは、オプションのダイアログから「次の値より後...」で「今日」を入力することができない。
Power Query 行のフィルター選択
 ここで日を指定すると数式は以下のようになる。

今日よりも後を指定したいのであれば、赤い線の部分が「今日」になればよい。
ワークシート関数の Today() や Now() に相当するものが入ればよさそうなことは想像に難くない。
それが、DateTime.LocalNow() 関数だ。

実際に数式を直接書き換えてみた結果が以下だ。
ダイアログからは DateTime.LocalNow() 関数はバリデーションチェックによって入力ができないが、数式バー(のようなもの)では直接入力、修正が可能なのだ。

シリアル値ではない日付形式

Excel における日付や時間(時刻)の扱いは「シリアル値」を使っている。Excel ではないプラットフォームではシリアル値が使われていないため、日付・時間の扱いには注意が必要になることが多い。

Power Query では「DateTime」型が基本である。 DateTime 型は Date 型と Time 型から成り立っている。たとえば既出の DateTime.LocalNow() は DateTime 型を返す。

DateTime.LocalNow()  ->  2015/01/07 11:47:45

ここから日付だけを抜き出したい場合は、Date プロパティを参照する。

DateTime.Date(DateTime.LocalNow())  -> 2015/01/07

時間だけを抜き出したい場合は、Time プロパティを参照する。

DateTime.Time(DateTime.LocalNow())  -> 11:47:45

年や月や日を抜き出す場合、ちょっとしたテクニックが必要になる。
DateTime 型に含まれる Date 型からプロパティ参照する。そして、Year や Monty、Day プロパティを参照すると、その戻り値は「数値」になる。

Date.Year([申込日])  -> 2015
Date.Month([申込日])  -> 1
Date.Day([申込])  -> 7

ゼロパディングしたい場合、1月は 01、7日は 07 の場合はテキストに置き換える必要がある。戻り値は「数値」なので、以下のようにする。

Number.ToText(Date.Day([申込]), "00")  -> 07

もちろん、12 や24 の場合も問題ない。

ならば、DateTime から ToText を使えばよさそうだと思うだろう。
しかし、en-us, ja-jp などのカルチャが関係しそうな記述がヘルプにあるのだが、 フォーマットオプションについては期待する動きを見つけられていないので注意されたい。(現状、私は使用していない)

DateTime.ToText([日付], "yyyy")  -> 2015 (文字列)
DateTime.ToText([日付], "yy")  -> 15 (文字列)

DateTime.ToText([日付], "d")  -> 2015/01/07 (文字列)


最後にシリアル値であれば 1 をプラスすることで1日後となるが、DateTime の場合は、AddDays メソッドを使う。

Date.AddDays([申込日], 2)  ->  2015/1/9 (申込日が 2015/1/7の場合)
Date.AddDays([申込日], -30)  ->  2014/12/8 (申込日が 2015/1/7の場合)

Power Query で利用できる関数は以下のページにある。解説が親切ではないので、いろいろと試して確認するのがいいだろう。

Power Query fomula categories
https://weu-odcsup.office.com/en-SG/article/Power-Query-formula-categories-125024ec-873c-47b9-bdfd-b437f8716819

2015/01/18

Power Query を使った重複行の削除

重複行の削除もしくはユニークなデータのリスト作成も実務で Excel を使うユーザーにとっては往々にして直面する課題である。

いくつかある重複行の削除

現在、重複行の削除で Excel 2007 以降に追加された データ タブ - データ ツールにある「重複行の削除」がもっとも紹介されている機能だろう。


表の重複している項目を削除する(Microsoft atLife)
http://www.microsoft.com/ja-jp/atlife/tips/archive/office/tips/002.aspx


たしかにこの機能により重複行を削除し、ユニークデータのリストの作成が可能なのだが、元データが更新されれば、同じ作業をし直さなければならず、この機能を活用するシーンは私自身はあまりない。正直、単純で汎用性が乏しく実務では応用した活用が難しいのだ。


一方、ピボットテーブルを使うことで元のデータが更新されても重複行を削除した表/リスト作成が可能だ。この方法であれば、元データが更新されてもピボットテーブルの「更新」をすることで対応できる。



ただし、注意したいのは、ピボットテーブル レポートのピボットテーブルは構造化参照可能なテーブルではない。ユニークデータリストを作って終わり、ではなく、そこから何らかの集計・計算・データ利用において、構造化参照によるテーブルの利用ができないため、他への再利用が難しい。テーブルではないことから、データの増減が発生したときの構造化参照テーブルのメリットも使えない。



上記の例ではデータカウントを取ることが目的ではない。逆にピボットテーブルなのでカウントの取得は簡単だ。しかし、このブログでも再三紹介しているように、現在そして今後 Excel はテーブル機能をベースにして拡張、新機能が追加されている。なるべくテーブルを使った課題解決方法を手にしておきたいところだ。

参考までに、もちろん、VBA を使った重複行削除のテクニックもある。

重複行を削除する(OfficeTANAKA)
http://officetanaka.net/excel/vba/tips/tips14.htm

VBA を使えば、重複行削除をしたユニークデータのリストを作成し、それをテーブルに変換することができる。

ただし今では VBA プログラミングをせずに「機能」だけで上記を実現できる。 それが Power Query だ。

Power Query を使った重複行の削除

Power Query については以前にも一部の機能を紹介しているが、そこでも述べたように Power Query はサーバーやクラウドからデータを Excel に取り込むことだけを目的としたアドインではない。Excel のテーブルもデータ ソースとして指定が可能だ。

そして、Power Query のクエリ エディターの「列の削減」で「重複部分の削除」の機能があるのだ。
これを使うことで、「重複行の削除」と同様のことができる。


もちろん、Power Query で取り込んだデータは Excel のテーブルとなる。構造化参照可能なユニークデータの「テーブル」となる。これでピボットテーブルの重複行削除でできなかったことが可能になる。

Power Query が Excel をデータ ソースに変える

しかし、本当に重要なのは、重複行の削除ができることではない。

データを Excel のテーブルとして持ち、Power Query を使うことで、Excel をまるで「データベース」のように扱うことができる、ということが重要なのだ。これが Power Query をすべての Excel ユーザーに勧めたい理由である。 重複行の削除はほんの一例でしかない。

蓄積されたデータの中から必要なデータを抜き出し、それを加工したい、分析したい、集計したい、というニーズは Excel ユーザーにとっては「通常」のことだと思う。

そしてその作業を「繰り返して」はいないだろうか。更新されたデータ(ワークシート、テーブルといったデータの固まり)を対象に、同じ条件で抜き出す、加工する、レポートを作る、といったことだ。

手作業でデータを抜き出すのであればオートフィルターを使っているだろう。そこから絞り込んだデータを他のワークシートやブックにコピーしていないだろうか。

実践ワークシート協会の VBA セミナー スタンダードを受けた受講者であれば、それら一連の作業を VBA で行うことができるだろう。

Power Query を使うことで、Excel のテーブルをデータ ソースとして指定し、抽出するための複数条件をクエリ エディターを使って設定し、結果を Excel のテーブルとして出力する、それらすべての設定を「クエリ」として保存し、再利用が可能なのだ。

そして、Power Query は条件を設定し抽出するだけではない。カスタム列の追加も可能なのだ。ピボット テーブルの集計フィールド、Power Pivot の集計列と同じだ。データ型の変換までできる。

以下のアニメーション GIF では、Data2 列の数値を文字列変換し、ゼロパディングで 1 を 001 にして Data 列の文字列と結合させ、文字列の Data3 を数値に変換している。列の順番も変更可能だ。このようなデータの操作・加工も Power Query で可能で、ある意味、元のテーブルからまったく別のテーブルを作っているようなものだ。


Power Query のクエリ エディタの式で使える関数はワークシート関数でもなく、Power Pivot の DAX 関数でもない。しかし、マイクロソフトが公開している記事を参考にしながら Excel のワークシート関数の知識で試してみればその使い方はわかると思う。重要なのは全く別のものでなく、かぶっていることが多い、だから、Excel ユーザーであれば「わかる」だろう、想像してみよう、ということだ。

上記のアニメーション GIF でも、関数の Number.ToText の引数として、"000" を入れたのはワークシート関数の TEXT 関数の知識からだ。それで期待通りの動きになるのはさすがマイクロソフトと言わざるを得ないだろう。

少しでもこの情報が参考になれば幸いである。

2015/01/11

Excel Online / Excel Web Access (Excel Services) - データ接続の更新 SharePoint リスト

Office 365 SharePoint Online に Excel ブックを保存して他のユーザーと共有する、という使い方でもメリットがあるが、できれば保存したブックの中を簡易的に確認したい、編集する必要はなく参照だけで良い、という使い方もあるだろう。
 
このような場合、Office 365 SharePoint Online では以下の使い方が用意されている。
 
・ Excel Online で Excel ブックを開く
・ Excel Web Access Web パーツ(Excel Services) でサイトのページに貼り付ける
 
いずれの場合も「現時点での最新データを見たい」という目的であることは明確だ。
 
ブックで扱うデータがワークシートへの手入力の場合、Excel ブックの SharePoint に保存した状態を参照できる。ある意味、それが最新であり問題はない。問題になるのは「データ接続」している場合である。
 
Excel はさまざまなデータ ソースに外部データ接続機能を使って接続できるが、今回は SharePoint リストに接続したブックを Excel Online や Excel Web Access Web パーツで扱う場合を紹介したいと思う。
 
注意点としては、一部 TechNet や MSDN に明確に書かれていない方法を紹介することになる。米国マイクロソフトの英語版 Office ブログやフォーラムで Microsoft の担当者からの情報などを参考にしているが、私自身はそれを元に TechNet/MSDN といった公式技術文書で同様の記述をまだ見つけ出すことができていない。その点は留意されたい。
 
1つではない SharePoint リストとのデータ接続方法
 
SharePoint リストのデータを Excel にインポートする方法の代表格は SharePoint リストの「リスト」タブにある「Excel にエクスポート」だろう。
 
 
通常業務でリストのアイテムを Excel に取り込んで PC で集計・分析・レポートを作るのであれば、このエクスポートでほとんど問題がない。
 
ところが、この接続方法を使ったブックを SharePoint に保存し、それを Excel Online で開こうとして以下のようなメッセージを見たことがある人は多いだろう。
 
 
[詳細の表示] ボタンをクリックすると以下のダイアログが表示される。
 
 
このメッセージを見れば、多くの人は「SharePoint リストを使った Excel ブックは Excel Online で使えないのか」と思っても仕方ない。この接続方法を含んだブックのデータ接続は Excel Online で使えない、サポートされていないことは事実である。
 
実は SharePoint リストのデータを Excel にエクスポートする方法は数種類ある。区別を明確にするために、「接続のプロパティ」の「接続の種類」で使われている名称を使って分類したものが以下だ。
 
a) 接続の種類 : SharePoint リスト
SharePoint の [リスト] タブの [Excel にエクスポート] を使ったデータ接続。
 
b) 接続の種類 : Office データ接続
Excel のデータ タブの [その他のデータソース] の [OData データ フィード] を使ったデータ接続。
データ モデルは強制的に作成される。
 
c) 接続の種類 : OLE DB クエリ
PowerQuery の [その他のデータソース] の [OData フィードから] を使ったデータ接続。
ただし、データモデルの作成はしていないタイプ。
 
d) 接続の種類 : モデル OLE DB クエリ
PowerQuery の [その他のデータソース] の [OData フィードから] を使ったデータ接続。
データ接続作成の際、データモデルの作成も指定。
 
Excel と Office 365 SharePoint Online との接続という意味では上記の4つがある。
 
「SharePoint リスト」という接続の種類を含んだブックは Excel Online では利用できないが、MSDN や TechNet、Office Online などを調べると、OData フィードによる接続は Excel Online で利用可能、という記述を見つけることができる。
 
 
ところが、OData フィードによる接続も上記のように「Office データ接続」、「OLE DB クエリ」、「モデル OLE DB クエリ」の3種類存在し、かつ、そのまま利用しても、いずれの OData フィードのデータ接続でデータ接続の「更新」(refresh)ができないのが現状だ。

結論からいえば、ある「設定」をすることで、Excel Online や Excel Web Access Web パーツでもブックのデータ接続を更新して最新のデータを見ることが可能だ。その手順およびそれに対応した接続方法を紹介する。
 
Office データ接続で SharePoint リストをエクスポートする
 
Excel Online や Excel Web Access Web パーツ(Excel Services) での利用を考えているならば、SharePoint リストからのデータ取得は接続の種類「Office データ接続」を使うべきと言える。この接続方法であれば、ある設定(アプリ権限の付与)をすることで Excel Online 上でデータ接続更新が可能になる。
 
では、Office データ接続による OData データ フィードの構成をしてみよう。
 
1) エクスポートしたい SharePoint リストの URL を控える。
 
たとえば、以下のようにブラウザで SharePoint リストを表示した時、控えておきたい URL は "_layouts/15/start.aspx#/Lists/Seminar/" の前にある "https://jpwa.sharepoint.com/sites/r2co/" を控えておく。

 
2) Excel のデータ タブ - その他のデータ ソース の OData データ フィードで接続を構成する。
 
データ タブの [OData データ フィード」を開く。
 
 
データ接続ウィザードのダイアログが開く。
控えておいた URL の後に "_vti_bin/listdata.svc " と入力して [次へ] をクリックする。
このサンプルの場合は、"https://jpwa.sharepoint.com/sites/r2co/_vti_bin/listdata.svc "と入力する。
 
 

[追記] ここで Office 365 へのサインイン画面が表示される場合がある。一度、接続に対してアカウントとパスワードを登録することで、接続情報を削除しない、パスワードを変えないかぎり、接続の際のサインイン画面をスキップすることが可能になる。
[追記終わり]

テーブルの選択をする。ここでのテーブルは SharePoint の「リスト」を指している。
取り込みたいリストにチェックを入れて [次へ] をクリックする。
 
 
ファイル名や説明を変更できる最終ダイアログが表示される。Excel Services などの認証は変更せずにこのまま [完了] ボタンをクリックする。
 
 
データのインポート ダイアログが開く。表示方法の選択肢があるが、テーブルとしてインポートするのであれば、そのまま [OK] をクリックする。ここで [このデータをデータ モデルに追加する] オプションがチェック済みになっていてグレイアウトされている。強制的にデータ モデルを作成していることがわかる。
 
 
SharePoint Online のリストが Excel のテーブルとしてインポートされた。
 
 
この素のままのテーブル データを見るより、ピボット テーブルを使ってレポート形式にした方が実用的だ。このテーブルを利用してピボット テーブルを作成する。もちろん、この前の処理でのデータ インポートで「ピボット テーブル レポート」を選択して、素のテーブルを取り込まないことも可能だ。
 


OData データ フィード接続を含んだブックを SharePoint に保存する

このブックを SharePoint Online のドキュメントライブラリに保存する。
一旦ローカルに保存したものをアップロードしてもよいし、直接 SharePoint Online のドキュメントライブラリーを指定してもよい。この時、上で作ったピボットテーブルだけを Excel Online で表示・参照させたい場合は、ブラウザーオプションでピボットテーブルだけを指定しておく。
 


ではこのブックを SharePoint Online から Excel Online を使って開いてみる。
SharePoint リスト接続と違うのは、ブックを開いたとき、SharePoint リスト接続のような警告メッセージは表示されずに、指定したピボットテーブルが表示される。
 

なお、この状態でピボットテーブルのフィールドリストを操作してデータの分析が可能であり、これだけでも使い道は多いにあるだろう。

しかし、まだ、これだけでは SharePoint とのデータ接続のデータ更新は成功しない。Excel Online でデータ更新しエラーになる状況をアニメーションGIFでとったものが以下である。なお、データは上記とは別のブックで、データ接続更新設定をしていない別のテナント(Office 365) で実施したものになる。


SharePoint リスト接続とは違う以下のエラーメッセージが表示され、データ更新に失敗している。

----
外部データの更新が失敗しました。
ブック内のデータ モデルを処理しているときにエラーが発生しました。もう一度やりなおしてください。

このブックに指定されている1つ以上のデータ接続を更新できません。
以下の接続を更新できませんでした:
----

接続名はデータ接続ファイルを保存した時に指定したものが表示される。

Excel Online でデータ接続更新を可能にする設定(アプリ権限付与)を行う

冒頭に述べたように私自身が Office Online/TechNet/MSDN 内で公式な技術文書として探し出せていないのが、この設定である。ただし、この情報ソースは米国マイクロソフトの英語による社員ブログやフォーラムでマイクロソフト社員より提供されているものである。

(参照)
Office Blogs - Project Online and Excel Web App: Cloud data improves reporting
Project Online の OData フィードを Excel Web App で利用しデータ更新を可能にする設定について書かれたブログ(2013年3月29日)
http://blogs.office.com/2013/03/29/project-online-and-excel-web-app-cloud-data-improves-reporting/

[SOLVED] Excel service refresh issue
SharePoint リストを OData データ フィードで Excel 2013 で取り込み、Excel Online でデータ更新できない件について、MSFT Support から、この設定を提示しているフォーラムの投稿(2014年12月20日)
http://community.office365.com/en-us/f/172/t/284523.aspx

もし、Office データ接続 OData データ フィードを使った Excel Online でのデータ更新が成功している場合、すでにこの設定が他の管理者権限を持っているユーザーによって行われていると考えられる。この設定登録はサイト コレクションレベルでの登録だが、設定そのものはOffice 365 の「テナント」レベル(契約している Office 365 全体)にも登録される。そのため、例えば、テスト用のサイト コレクションでアプリ権限付与設定を行い、テスト終了後にサイト コレクションに登録された権限付与設定を削除しても、テナントレベルの登録を削除しない限り、テナント全てのサイト コレクションで有効状態になっている。そのため、該当するサイト コレクションで登録していなくてもデータ接続更新が可能になっている場合がある。

[追記] 上記の表現は正確でなかった。リンク先の記事の XML で「テナント」範囲での指定をしているからだ。スコープの指定がサイト コレクションであれば、登録したサイト コレクションのみ有効になる。サイト コレクションのみ有効になる XML は追記した。
[追記終わり]

登録するアプリのプリンシパル ID は "00000009-0000-0000-c000-000000000000" である。現在このプリンシパル IDのアプリ名は "Power BI" もしくは "Microsoft Power BI Reporting and Analytics" となっているはずだ。上述の Office Blogs では "Microsoft Azure Analysis Services" だった。(2013年3月末)
今後もアプリ名(Title)が変わる可能性があることに注意されたい。

1) テナントでの権限付与状態の確認

上記のプリンシパル ID のアプリへの権限がテナントに登録されていないことを一応確認する。
この確認は全体管理者権限を持っていないとできないのでユーザーの権限に注意すること。

管理ポータルを開く。


もしくは、以下から管理ポータルを開く



SharePoint 管理センターに移動する。


SharePoint アプリの管理へ移動する。左サイドバーの [アプリ] をクリックする。


アプリの権限をクリックする。


アプリの表示名に「Power BI」もしくは「Microsoft Power BI Reporting and Analytics」が無い、もしくは、「00000009-0000-0000-c000-000000000000」を含んだアプリIDが無いことを確認する。


なお、登録されているアプリはそれぞれのテナント環境で違うので上記図と同じにならない場合もあることを留意されたい。

2) サイト コレクションレベルでの確認とアプリの登録

登録は管理センターからはできず、サイト コレクションから行う。どのサイト コレクションから登録しても結果としてテナント レベルの登録になるが、一応、Excel Online で使いたいブックを含んだサイトから登録する。

[追記] テナントレベルの登録になるのは、後述する XML によるアプリの権限要求でテナントレベルを指定したためだった。
参考: http://msdn.microsoft.com/ja-jp/library/office/fp142383(v=office.15).aspx
追記したアプリの権限要求 XML で登録作業をしたサイト コレクションのみ有効にすることが可能。
[追記終わり]


SharePoint サイトに移動して右上の「歯車アイコン」から「サイトの設定」を選択する。


[サイト コレクションの管理] の [サイト コレクションのアプリの権限] をクリックする。
サブサイトのサイトの設定画面を開いている場合は [トップ レベルのサイト設定に移動] をクリックして、[サイト コレクションのアプリの権限] をクリックすること。


Microsoft Power BI Reporting and Analytics が無いことを確認する。



次はアプリの登録とアクセス権の設定をするのだが、これまで行ってきたメニューからの操作が現状ではできない。登録画面の URL を直接入力することになる。

現在、[サイトの設定 > サイト コレクションのアプリの権限] の画面を開いている。その URL は以下のようなものだ。/_layouts/ より前の部分はそれぞれの環境で違うが、/layouts/ 以降は同じだ。

https://hogehoge.sharepoint.com/sites/hoge/_layouts/15/start.aspx#/_layouts/15/appprincipals.aspx

この appprincipals.aspxappinv.aspx に変更して Enter キーを押す。

すると以下の画面が表示される。


アプリID: に 00000009-0000-0000-c000-000000000000 を入力し、[参照] ボタンをクリックする。
タイトルに [Power BI](違う場合もある)、アプリ ドメインに [analysis.windows.net] が表示される。タイトルはテナントの SharePoint Online のリリースによって違う場合があることを確認している。

アプリの権限要求 XML に以下の XML をコピーして貼り付け、[作成] ボタンをクリックする。

<AppPermissionRequests><AppPermissionRequest Scope = "http://sharepoint/projectserver/reporting" Right="Read"></AppPermissionRequest><AppPermissionRequest Scope = "http://sharepoint/content/tenant" Right="FullControl"></AppPermissionRequest></AppPermissionRequests>

[追記] http://msdn.microsoft.com/ja-jp/library/office/fp142383(v=office.15).aspx を参考にして、必要ない projectserver の AppPermissionRequest Scope を除き、サイト コレクションでの権限にしたものが以下になる。

<AppPermissionRequests>
  <AppPermissionRequest Scope = "http://sharepoint/content/sitecollection" Right="FullControl"></AppPermissionRequest>
</AppPermissionRequests>

この XML で Excel Online のデータ接続更新が可能を確認している。
[追記終わり]


タイトル名のアプリを信頼しますか?という確認画面がでるので、[信頼する] ボタンをクリックする。



サイトの設定画面にもどるので再度[サイト コレクションのアプリの権限]を開いて登録されていることを確認する。繰り返しになるが、アプリのタイトルはテナントのリリースによって違うことが確認されている。重要なのはアプリIDであることに留意されたい。


この状態で、(追記: アプリ権限要求の XML で Scope をテナント指定していれば)再度テナントレベルを確認すると以下のようにアプリが登録されていることがわかる。


なお、上記の操作でアプリのタイトルが「Power BI」になっているが、この操作をしたテナントでは Power BI for Office 365 のサブスクリプションは購入していない。

もし Power BI for Office 365 をすでに購入していた場合は、テナントレベルでのアプリの権限で「Power BI」というアプリの表示名が表示されるが、そのアプリ ID は 00000009-0000-0000-c000-000000000000 ではない。これで判別ができるだろう。

3) Excel Online でデータ接続更新の確認

Office データ接続 OData データ フィードによるデータ接続を使ったブックをアップロードし、レポートのアプリ ID を登録してアクセス権を付与した状態で、はじめて Excel Online 上でのデータ接続更新が可能になる。実際に試してみよう。

以下は、上述でアニメーションGIFで失敗例としてあげた使ったブックと同じものである。
上記手順でアプリの登録と権限付与を行い、データ ソースである SharePoint リストでアイテムを追加登録した状態で Excel Online でデータの更新をした。


Excel Web Access Web パーツをサイトに貼り付け、データ更新を実行したのが以下だ。



いつものお約束 - 注意点

実務で実際にこの機能を運用すると、以下の事にすぐ気づくはずだ。

1) データ更新しても、その状態でブックは保存されていない

よって、次に開いたときやブラウザを F5 でリロードすると「元のデータ」に戻る。

これは、通常のローカル PC の Excel のピボットテーブルを考えてもらえれば想像に難くない。データ更新しても、ブックを保存しないで Excel を終了させているようなものだ。

ただ、この設定をすることで、[Excel Online で編集] においてもデータ接続の更新が可能になるので、編集モードにしてデータ接続の更新をすれば「保存」したことになり、データも最新になった状態になる。

結局、参照のみの Excel Online でのデータ更新や、Excel Web Access Web パーツでのデータ更新より、Excel Online の編集モードでデータ更新、そして保存、という運用になってしまう。PC の Excel で更新、アップロード、という手間がなくなった、ということだ。

2) データ接続の自動更新はできない

データ接続のプロパティで自動更新のオプションがあることを知っている人も多いだろう。


これは使えない。設定してもなんの変化もない。
理由は、データ モデルが更新されていないためである。データ モデルはピボットキャッシュのようなものだと考えれば理解できる人もいるだろう。実データはデータ モデルを介してサーバー側からとりこむため、データ モデルを更新しないかぎり、ピボットテーブル レポートのデータは更新されない。そして、データ モデルの接続プロパティの [定期的に更新する] オプションはグレイアウトされて設定不可能になっている。


3) Office データ接続のみ有効で PowerQuery によるデータ接続の更新はできない

PowerQuery のデータ モデルを使った接続 (モデル OLE DB クエリ)であっても、上記のアプリ ID とアプリ権限設定後、Excel Online や Excel Web Access Web パーツ内でのデータ更新はできない。

以下のメッセージが表示されエラーになる。

外部データの更新が失敗しました
ブック内のデータ モデルを処理しているときにエラーが発生しました。もう一度やり直してください。
このブックに指定されている 1 つ以上のデータ接続を更新できません。
以下の接続を更新できませんでいた:
Power Query - List01
接続: Power Query - List01
エラー: OnPremise エラー:問題が発生しました。もう一度やり直してください。
テーブル "List01" の処理中にエラーが発生しました。
トランザクションの別の操作が失敗したため、現在の操作は取り消されました。


もう一度やりなおして、、、とあるが、何度やり直してもデータの更新はできない。
Power Query によるモデル OLE DB クエリ / OData フィードは Power BI for Office 365 の Power BI サイトで使用する。

現状、複数あるデータ接続が、使用する機能別に用意されているため難解になっていることは否めないが、ここは過去の資産の蓄積と将来のために追加された新機能として理解するしかないかもしれない。

まとめ

Office 365 SharePoint リストと Excel 連携を最大限に活用するならば、SharePoint のリスト タブにある「Excel へエクスポート」(SharePoint リスト接続)を使わず、Excel のデータ タブにある「OData データ フィード」(Office データ接続)を使ったほうが便利になりそうなことは理解できたと思う。

しかし、ものすごく便利になるか、といえば微妙であるのは否めない。
ローカル PC の Excel で集計・分析し、それを SharePoint にアップロード、アップロードした時点での情報を Excel Online/Excel Web Access Web パーツで表示、という運用で多くはカバーできるのも事実である。

Office データ接続の OData データ フィードで、Excel Online / Excel Web Access Web パーツのデータ更新が可能になるメリットを享受できるが、たぶん、実務でこの機能を求めるのであれば「自動更新」というニーズがあるはずだ。
残念ながら、Excel Online と OData データ フィードだけでは自動更新のニーズを満たすことはできない。

この自動更新のニーズを満たすのが Power BI だと考えている。

事実、Power BI には以下の設定オプションがある。


残念ながら PowerBI はサブスクリプション購入したばかりで実務運用のレベルまで使っておらず、かつ、その設定も確実に理解していないため、これ以上の紹介はできないが、近いうちに紹介することができるだろう。

非常に長いエントリーになってしまったが、マイクロソフトによる日本語ドキュメントがまだ整備されていないようなので、何等かの参考になれば幸いである。
Powered by Blogger.

自己紹介


PowerBI コミュニティ勉強会の 沼口 です。
https://powerbi.connpass.com/
最近の Excel は Office 365 のクラウドサービスと 連携する方向性が打ち出されています。この「Road to Cloud Office」ブログでは、Excel ユーザーの視点から Power BI Service や、Office 365 の活用方法を模索した結果をお伝えしています。
Microsoft MVP for Data Platform 2017-2018