[Power Query] OneDrive パーソナル (onederive.live.com) の Excel ブックに接続する方法

Power BI は OneDrive for Business (中身は SharePoint で、URL に my.sharepoint.com が含まれるもの) のフォルダへの接続を前提にしているようで、サービス名が同じでややこしいのですが、個人が使う OneDrive (URL に onedrive.live.com が含まれるもの)への接続は「簡単には」許していないように思えます。それはもう7年も前のことで、以下のような投稿をしています。このときは OneDrive パーソナル（と便宜上呼びます）への接続方法を見つけることができませんでした。（2015年当時）

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

https://road2cloudoffice.blogspot.com/2015/07/power-query-https-sharepoint-excel.html

Googleスプレッドシート(ベータ)に接続する

最近 Power BI ユーザーさんのブログで Google スプレッドシートがベータとはいえ、Power BI から取り込み可能になった、と知りました。[データを取得] から「Google」キーワードで検索すると、ベータですが Google スプレッドシートが表示されるようになりました。

Googleスレッドシートを表示している URL を使い、Google アカウントでサインインして Power BI に取り込めました。また、Power BI Service にアップロードして、資格情報（Google アカウントによるサインインと、プライバシーレベルを Organizational）の再設定を行うことで、[スケジュールされている更新] が完了することを確認しました。

コネクタのリファレンス Googleシート(ベータ)
https://docs.microsoft.com/ja-jp/power-query/connectors/googlesheets

OneDrive パーソナルにある Excel ブックに接続したい

Google スプレッドシート同様に [データを取得] で OneDrive といれても、それらしい、わかりやすいサービスは列挙されません。[OneDrive 内の Excelブック] があればベストなのですが、残念ながら、そういう用途は SharePoint という前提なのでしょう。

データ分析的なフィロソフィーとしては、手入力の Excel をデータソースとしたくないのはわかります。それをするなら Power Apps でデータを収集すべきで、生スプレッドシートをいじらせるのは「綺麗なデータを用意する」という観点からもあまりお勧めできないからでしょう。そう、目指すは以下のような世界。

Power Apps、Power Automate、および Power BI を SharePoint Online と統合する

https://docs.microsoft.com/ja-jp/power-apps/maker/canvas-apps/sharepoint-scenario-intro

しかし、そのデメリットを理解した上でも、Google スプレッドシートが OK ならば、簡単に利用できる OneDrive パーソナルに保存した Excel ブックの Power BI への取り込みは世界中の Excel ユーザーと Power BI ユーザーが感じているはずで、2022年にもなれば何らかの解決方法があるのではないか？と思い、少しだけしつこく探して、、、見つけました！ 2017年の投稿で、たしかにこの Embed の機能が2016年前後に追加されたのを覚えています。（訂正： Excel Web App で 2011年には存在していました）

PowerBI support for OneDrive Personal as data source
https://community.powerbi.com/t5/Power-Query/PowerBI-support-for-OneDrive-Personal-as-data-source/td-p/129125

実際に試して、上述の Google スプレッドシートの手順同様に Power BI Servcie にアップロードして、[スケジュールされている更新] もうまくいきました。その手順をあらためて紹介します。

1) OneDrive の Excel ブックで右クリックメニューで [埋め込み] を選択します

2) この操作がはじめての場合は URL生成のメッセージが右ペインに表示されるので [生成] をクリックします。[生成] ボタンの下に書いてあるように、これによりサインインせずにこのファイルを埋め込んだブログやWebページで表示できるようになります。セキュリティに注意してください。

3) プレビューと iframe コードが表示されるので、この iframe のコード内の、resid= から &em=数 まで (width の前) をコピーします。

4) Power BI に Web ソースとして入力する URL を作ります。以下の文字列に、上記 3) でコピーした文字列を追加し、&app=Excel で閉じます。

https://onedrive.live.com/download? <文字列コピー> &app=Excel

上記の例だと以下のようになります。

https://onedrive.live.com/download?resid=6E1B8001F99D3597%2179199&authkey=AACEgb0W3bmJHXs&em=2&app=Excel

5) Power Query で Web からデータをインポートを選び、URLをコピーします。[基本] で構いません。[OK] をクリックします。

6) ワークシートにテーブル オブジェクトが含まれていればテーブル名が表示され、SharePoint Online (OneDrive for Business) と同じようなナビゲーションが表示されます。

7) この pbix を Power BI  Servcie にアップロードし、データセットの [データ ソースの資格情報] のプラバシーレベルで [Public] を指定。（これは Embed コードは「一般公開」の設定のため - このプライバシーレベルは仕事で使う場合は常に注意ですね）

8) 資格情報を設定することで [スケジュールされている更新] の設定が可能になる。

以上となります。

この設定は Excel の取得と変換の [Web から] でも利用できます。

足かけ7年のモヤモヤが解消されました。

ただし、繰り返しますが、セキュリティとしては匿名アクセス可能な一般公開の URL を生成しています。一般公開されているデータ、特に公になっても問題の無いデータに限られ、不安であれば、SharePoint Online/OneDrive for Business で組織アカウント サインインの元でのデータ活用に切り替えたほうが良いと思います。ベータの Google スプレッドシートが認証を通しているのも納得できます。Goolge スプレッドシートも有力な選択肢ですね。

既知かもしれませんが、参考になれば幸いです。