[Power BI] カスタム関数をつかったQueryを含むデータセットが自動更新できない

(追記: Excel のブックをそのまま Power BI Service にあげて使用するのではなく、Power Query + Excel Pivot Table レポートを Power BI Desktop でレポートを書き換えています。Power Query クエリの多くをそのまま移行できるのは、モダンExcel + Power BI の組み合わせの使い勝手の良いところです）

データを元にしたレポートやダッシュボードを複数のユーザーと定期的に共有することは、Excel で工夫・時間をかけて実現できますが、Power BI Service を含む Power BI は、その目的のためにつくられたこともあり、Excel での苦労や時間を軽減してくれます。

特に、モダンExcel な使い方をしているユーザーにとって魅力に見えるのは、レポートのデータ更新の自動化 につきるかと思います。

以前紹介した投稿 [Excel] Excel で JSON データを読み込む で利用してる connpass を使ったレポートなども、個人でデータを確認する分には Excel は柔軟で使いやすいのですが、他の人と、定期的に共有するとなった場合の共有方法については非常に悩みました。Excel ユーザーとしては OneDrive の活用が理想だったと思いますが、OneDrive と PowerQuery (取得と変換）の組み合わせはあまり良くありません。OneDrive といえども他社のファイルサーバー/サービス同様にxlsxのファイル保存先としてしか使えず、メールやメッセンジャーなどで保存先のパスを共有しながら、画像を貼り付けるような使い方になってしまいます。

もし、クラウドに保存している xlsx の「データ更新」ボタンが定期的、自動的に押され、そこにあるデータを表示できたら、、、

これは Pivot Table レポートや Power Query を活用し、複数の人とそれらを共有しなければならないデータエンジニアがいつも思うことです。これを可能にするのが、Power BI Service のダッシュボードやレポートで、データセットの設定にある「スケジュールされている更新」です。

この設定を行い、レポートおよびダッシュボードを共有することで、自動更新された最新データの表示が可能になります。以下は17日、18日の設定時に発生したエラーを含む対応がうまくいき、データセットが自動更新された履歴です。成功すると [状態] が完了になります。

解決しなければならなかった課題は複数あったのですが、今回、関連する日本語の情報を見つけることがなかなかできなかったので、そのうちのひとつを今回ご紹介したいと思います。残りもおって紹介したいと思います。

エラーメッセージ

データセットの[更新スケジュールの設定] で表示されたエラーの内容は「現在、以下のデータソースが更新をサポートしていないため、このデータセットの更新をスケジュールできません、、、」「・Query1 のデータソース」でした。

Query1 は具体的なクエリの名前を指しているわけではありません。

さらに具体的な Query に含まれるエラーの原因を探します。上の図の青い文字のリンク「データセットの表示」をクリックすると、エラーが発生しているデータセットの更新の状況を確認することができ、[最新の情報に更新済み] に更新しようとした日時が表示され、その横に注意マークがあるのでそれをクリックしてみました。するとどうやらデータセットに含まれる「動的データソース」があるために更新されない、と明示的に書かれていました。

このデータセットには、動的データソースが含まれています。動的データソースは Power BI サービスでは更新されないため、このデータセットは更新されません。詳細情報: https://aka.ms/dyanmic-data-sources。のメッセージ画面

このダイアログで紹介している https://aka.ms/dynamic-data-sources のページを開くと、実は非常に詳しく Power BI におけるデータ更新について書かれているのですが、これを全部読み込んで理解するのはなかなかつらいものがあります。以下、このページの日本語の URL です。

Power BI でのデータの更新
https://docs.microsoft.com/ja-jp/power-bi/connect-data/refresh-data

今回のケースにおける問題となった動的データソースは、connpass から REST API を使い JSON データを取りこむ際につかった start パラメーターが原因でした。どうしてこれが原因であるかがわかったのは後述する YouTube 動画のおかげです。

    ソース = Json.Document(Web.Contents("https://connpass.com/api/v1/event/?keyword=JAWS&count=100&start=" & p1)),

しかし、この start パラメーターによるページングの機能を使って、対象とするデータを Power BI に取りこむ（import) ことをしなければ集計ができません。この時点で日本語の情報が少なかったので、Power BI の UI を英語に切り替えて、英語でエラーメッセージやダイアログのメッセージを拾って検索することにしました。

ヘルプは上級者も含むユーザーを対象としているため、解決につながる直接的なイメージをその段階では思い浮かべることができなかったかもしれませんが、今思うとこの段階で Web.Contents のヘルプを読み込むべきでした。以下のヘルプの 例1 が解決方法だったのです。

Web.Contents
https://docs.microsoft.com/ja-jp/powerquery-m/web-contents　

解決方法は動的データソース表記を変更する

英語のキーワードのよる検索の試行錯誤の結果、以下の YouTube の解説ビデオを見つけることができました。

Making sure your Power BI web data source refreshes online (RelativePath, Query, BrowserContents)

https://youtu.be/sv_upbpq_Cw?t=546

上記の YouTube をみて、それから Web.Contents のヘルプをみると ? をつかったパラメーターの表記は NG で、ダイナミックURLを受け付けない静的ベース URL に対して、RelativePath や Query プロパティをつかった方法を使えばいいことがわかります。

上述のソースは以下のような表記方法に変わりました。

    ソース = Json.Document(Web.Contents("https://connpass.com/api/v1/event/?keyword=JAWS&count=100",

        [Query=[start=p1]])),

複数のパラメータの表記方法などは Web.Contens のヘルプに書かれています。

これで、パラメーターの変数を変えたいために動的URLを使うカスタム関数を作成し、その関数がクエリに含まれるためにスケジュールした更新が失敗する、といったことを回避することができます。

以上、参考になれば幸いです。