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のドキュメントライブラリから選択して、「アプリで開く」をクリック。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/596092/2f469a14-67aa-3eec-efcd-24bfbbab3a17.png)

EXCELでファイルが開けるので、ファイル＞情報から「パスをクリップボードにコピー」
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/596092/7ed40529-ba3c-8a8a-3898-36cdbaea5b9f.png)

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/596092/d54800cd-e4cd-d913-39f8-714fd1fcc2f3.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/596092/a55256ab-b44d-0cf8-ae05-83145c1fe513.png)

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

### Power BI Serviceからスケジュール更新の設定をする（ここで問題発生）
無事にレポートの発行ができたので、Power BI Service上でデータの更新をしてみる。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/596092/7f715792-cbe0-04ca-53ef-101108b9a99a.png)

するとゲートウェイが必要だという旨のエラー。オンプレミスではなく、SharePoint Online上なのになぜ...
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/596092/18c921e9-cf5f-93e5-5b7a-e5a4fade44f0.png)

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

### Power BI Desktopで接続を再確認
改めてPower BI Desktop側に戻って接続を確認してみる。
ファイル＞オプションの設定＞データソース設定
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/596092/6f2c4f8a-16d1-6ef2-6633-adc9e7f630a0.png)

やはり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を指定しなおす。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/596092/53eb4996-26a7-3b2f-096d-80600e9e31df.png)

パスがインターネットの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」コネクタよりむしろ便利なので積極的に使うべき。