Power Query から https 経由で SharePoint の Excel ブックを開く

Power Query (2.23.4035.242　June 2015 Release) で https 経由で SharePoint のドキュメントライブラリに保存されている Excel ブック、および OneDrive for Business にある Excel ブックへのアクセスが可能になっていることを確認しました。(もしかしたら、May 2015 Release からかもしれません、、、、）

Power Query は頻繁に更新されています。数か月前は https 経由で SharePoint や OneDrive for Biz の Excel へのデータ接続はできませんでした。ローカル PC の同期フォルダーを使っているのであれば、同期フォルダー内の Excel ブックにデータ接続可能でした。

今後は同期フォルダーを構成していなくても、https 経由で Excel ブックに接続可能です。（ただし、コンシューマー版の OneDrive へのアクセスは成功していません。([*1] 2022年追記 URLを編集して可能になっています）OneDrive for Business は Office 365 で提供されている OneDrive です。これは SharePoint をベースにしています）

取り込み手順

使い方はいたって簡単です。

[Power Query] タブの [外部データの取り込み] の [ファイルから] にある [Excel から] を選択します。

[ファイル名] に SharePoint ドキュメント ライブラリーの URL を入れます。

この URL は SharePoint のドキュメント ライブラリーであれば、_layouts の前までになります。
OneDrive for Business も同様です。

[開く] ボタンを押すと、SharePoint サイトにある「すべてのサイト コンテンツ」のリストが表示されます。

今回は [ドキュメント] にあるブックを開くので、[ドキュメント] をダブルクリックします。(英語表記は　Shared Documents）

ドキュメント ライブラリにある Excel ブックを選択し、ダブルクリックするか、[開く] ボタンを押します。
すると、Web コンテンツへのアクセス ダイアログが表示されるので、[組織アカウント] を選んで、該当する URL をチェックします。
既定が　[匿名] になっているので注意してください。

組織アカウント（Office 365 の ID）とパスワードでサインインします。

サインインが完了すると、以下のダイアログになるので [接続] をクリックします。

ナビゲーター ウィンドウが表示されるので、取り込みたいシートやテーブルを指定します。

[編集] ボタンを押せば、クエリ エディタ ウィンドウが表示され、クエリの編集をすることができます。
クエリ エディタ ウィンドウではデータの絞り込みや並べ替え、列の削除や追加、データ型の変換を行い、欲しい形でデータを取り込むことができます。

[閉じて読み込む] を押せばワークシートにデータが展開されます。特に編集の必要がなければナビゲーター ウィンドウの [読み込む] ボタンでデータの読み込みが可能です。

これまで、ファイルサーバー上の Excel ブックへのデータ接続は可能でしたが、https 経由でのデータ接続は少なくとも半年前はできませんでした。これにより、\\サーバー名 でのアクセスと同じように https:// で SharePoint 上にある Excel ブックへの接続が可能になりました。

何がうれしいの？

真っ先に思い浮かべたのが「Excel アンケート」です。

アンケートの共有をしている間は、ローカルの PC/Excel でブックを編集モードで開くことができません。

アンケート集計が溜まってくると、ピボットテーブルを使って分析したくなるのですが、Excel Online ではピボットテーブルをゼロから作ることができません。
こんなときには Power Query を使って、SharePoint にある Excel ブックをクエリで読み込むことでピボットテーブルを作り、データ分析が可能になります。もちろん [データ更新] によって、最新のデータにすることも可能です。

次に使えるのは外部ブックのテーブル オブジェクトへのリンクです。これは SharePoint のドキュメント ライブラリーにある Excel ブックに限らず、ローカル PC やファイルサーバーでも同様です。

ここに、外部ブックの範囲に対して VLOOKUP を使ってデータを参照しているブックがあるとします。範囲の場合は再計算によって「その時の」外部ブックのデータを取り込む（＝更新）することが可能です。

このブログで過去に紹介しているように、VLOOKUP や INDEX、MATCH などの「範囲指定」する関数にとって、範囲を「テーブル」にすることで、データの増減に自動的に対応可能になることは、テーブルを使う最大のメリットなのですが、テーブルの場合は外部リンクは使えないのです。このことはマイクロソフトの技術記事でも紹介されています。

Excel テーブル 数式で構造化参照を使う（support.office.com)

ここで「構造化参照を活用するヒント」に以下の記述があります。

“他のブックの Excel テーブルへの外部リンクを含むブックを使用する     ブックに別のブックの Excel テーブルへの外部リンクが含まれている場合は、リンクを含む「リンク先」ブックの #REF! エラーを回避するため、そのリンクされた「リンク元」ブックを開いておく必要があります。リンク先ブックを最初に開くと、#REF! エラーが表示され、その後リンク元ブックを開くと、エラーは解決します。リンク元ブックを最初に開くと、エラー コードは表示されません。” (引用おわり）

つまり、リンク先ブックを開いておかなければ、別のブックのテーブルへリンクしている数式は #REF! エラーになります。

これを回避するために、リンクを使わず、Power Query を使うことで #REF! エラーを回避しつつ、データ更新によって最新データを参照することが可能になります。

この機能拡張によって、Office 365 の SharePoint や OneDrive for Business を Excel の保存先として、従来のファイルサーバーのように使うことが可能になりました。

 

毎月にようにリリースされる新しい Power Query については、英語ですが Office Blogs で新リリースごとに紹介されています。

https://blogs.office.com/

[*1] OneDrive パーソナル (onederive.live.com) の Excel ファイルに接続する方法
https://road2cloudoffice.blogspot.com/2022/04/power-query-onedrive-onederivelivecom.html