Power BIでSharePoint上のEXCELを読み込むコネクタ

本記事の目的

Power BIでは提供されているコネクタの数が100以上ある。これだけ多いとどれを使うべきか迷う。例えばSharePointのドキュメントライブラリ上のEXCELファイルを読み込みたい時、どのコネクタを使うべきか。

結論は、とりあえず「SharePoint フォルダー」コネクタを使っておけば間違いなさそう。ファイル名が固定であれば「Web」コネクタを使っても良さそう。でも「EXCEL」コネクタを使う時には以下に注意。

SharePoint上のEXCELをPower BI Desktopから「EXCEL」コネクタで読み込むとき、**読み取り先をインターネットパスで指定しても自動的に（勝手に）ネットワークパスに置き換わってしまう。**これで何が困るのかというと、ネットワークパスだとローカル扱いになり、On-Premise Data Gatewayが必要になってしまう。

※使える環境で試した範囲でまとめましたが、間違いなどあればご指摘いただけるとありがたいです。

前提条件（やりたいこと）

読み込みたいデータはSharePoint Onlineのドキュメントライブラリに置いてある、.xlsx形式のファイルの中身。ファイルの中身は定期的に更新されるので、Power BI Service上でスケジュール更新したい。

「EXCEL」コネクタで直面した問題

Power BI DesktopからEXCELを読み込む（ここまで問題なし）

ローカルにあるEXCELだけでなく、SharePoint Online上のEXCELも「EXCEL」コネクタを使って読み込むことができる。読み込みたいEXCELのファイルをSharePointのドキュメントライブラリから選択して、「アプリで開く」をクリック。

EXCELでファイルが開けるので、ファイル＞情報から「パスをクリップボードにコピー」

するとこんな感じのパスがコピーできる。
「https://XXXXX.sharepoint.com/sites/XXXXXXXX/FileStorage/Book.xlsx?web=1」

EXCELのコネクタで呼び出すときには、後ろの「?web=1」は削除して貼り付ける。

するとPower BI Desktop上で問題なくデータが読み込める。ということで、レポートを作成し、Power BI Serviceに発行。

Power BI Serviceからスケジュール更新の設定をする（ここで問題発生）

無事にレポートの発行ができたので、Power BI Service上でデータの更新をしてみる。

するとゲートウェイが必要だという旨のエラー。オンプレミスではなく、SharePoint Online上なのになぜ...

よく見るとエラー画面に記載されているパスが、先ほど貼り付けたパスと違う形式になっている。

Power BI Desktopで接続を再確認

改めてPower BI Desktop側に戻って接続を確認してみる。
ファイル＞オプションの設定＞データソース設定

やはりEXCELコネクタで貼り付けたパスと違う形になっている。

• EXCELコネクタで貼り付けたパス（httpsから始まるインターネットのURL）：
  https://XXXXX.sharepoint.com/sites/XXXXXXXX/FileStorage/Book.xlsx

• 今見えているパス（共有フォルダなどに利用されるネットワークパス）：
  \XXXXX.sharepoint.com@SSL\DavWWWRoot\sites\XXXXX\FileStorage\Book.xlsx

どうやらEXCELコネクタが自動的にインターネットのURLをネットワークパスに置き換えていた模様。仕方がないので「ソースの変更」をクリックして、あらためてインターネットのURLを指定しなおす。

パスがインターネットのURLに代わり、コネクタのアイコンも「Web」コネクタのアイコンに変わった。この状態で再度Power BI Serviceに発行すると、オンプレミスデータゲートウェイがなくてもデータ更新ができる。

というわけで解決策

「EXCEL」コネクタではなく「Web」コネクタを使う

初めから「Web」コネクタを使えば良さそう。方法は使うコネクタ以外は「EXCEL」コネクタの時と全く同じ。「Web」コネクタを使った場合には、インターネットのURLがネットワークパスに置き換わるようなことはない。以下リンクを参照。
OneDrive for BusinessになっているがSharePointでも同じ要領。

Power BI Desktop で OneDrive for Business リンクを使用する
https://docs.microsoft.com/ja-jp/power-bi/connect-data/desktop-use-onedrive-business-links

「SharePoint フォルダー」コネクタを使う

「SharePoint フォルダー」コネクタを使うと、ファイル名が変更された場合にも対応できたり、同じフォルダにある複数のファイルを結合して読み込めたり、柔軟な運用ができるようになる。「SharePoint フォルダー」コネクタを使っておけば間違いなさそう。

手順は@Yellow11さんが分かりやすくまとめてくださっているのでそちらを参照。

Power BIでSharePointのデータを利用する
https://qiita.com/Yellow11/items/39336e331c397bd94d2a

結論

SharePoint上のEXCELを読むシナリオでは、次のように理解しておけば良さそう。

	評価	説明
SharePointフォルダー	◎	On-Premise Data Gateway不要。指定フォルダ内の複数ファイル結合など柔軟な運用が可能。
Web	○	On-Premise Data Gateway不要。読み込み対象ファイルは単体で、ファイル名は固定。
Excel	△	On-Premise Data Gatewayが必要。読み込み対象ファイルは単体で、ファイル名は固定。

Power BIに使い慣れた人でないと「読み込みたいのはEXCEL"ファイル"だからSharePoint"フォルダー"コネクタは不適切だろう」と考えて、他のコネクタを探して「EXCEL」コネクタを使ってしまったりする。

「SharePoint フォルダー」コネクタも、「フォルダー」コネクタも、フォルダからファイルを選択してちゃんと読み込みしてくれる。「Excel」コネクタや「Web」コネクタよりむしろ便利なので積極的に使うべき。