[Power BI Tips] 年月や連番がファイル名にくっついてる Web 上のファイルをデータソースにしたレポートでもスケジュール更新を有効にしたい場合

今回説明に使う Power BI Report

先日公開した New Car Sales in Japan です。



日本自動車販売協会連合会 (JAPAN AUTOMOBILE DEALERS ASSOCIATION) が公開しているブランド別新車販売台数確報の Excel がデータソースです。

ページを見ると年月が選択できるドロップダウンと [Excel ダウンロード] というリンクがあります。
リンクの URL を見ると、
http://www.jada.or.jp/contents/data/brand/kakuho/excel/brand_fix202003.xls
のようになっています。

試しに年月を 2020年2月 にしてみると、Excel のリンクは
http://www.jada.or.jp/contents/data/brand/kakuho/excel/brand_fix202002.xls
です。

選択可能な最も古い年月 2016年1月 にしてみると、Excel のリンクは
http://www.jada.or.jp/contents/data/brand/kakuho/excel/brand_fix201601.xls
です。

なるほど、規則性はわかりました。

ということで、これでデータソースの Excel ファイルは取得できるのですが、2016年1月～2020年4月現在までだと 52 ファイルとなり URL も 52 個となってしまいます。また、Power BI Desktop で直に URL を指定して Excel を取得すると、毎月増えるこのファイルに対応できません。毎月ファイルが増えるということは毎月 URL が増えることになります。Power Query で動的に URL を作成することは可能ですが、そうすると Power BI Service でスケジュール更新（自動更新）ができません。

実はこの点が解消できず、1年越しの個人的課題になっていました。Azure 等のサービスを使用して、つまり有償で対応しちゃえばできるんですが、それだとやっぱりなんか悔しいわけです。で、今回違うブログ記事を書いている時に、神が降りてきました。結論、Power Platform のチカラで自動更新に対応しました。やっぱり アーキテクチャ が解決する

それを実感しました。

実は1年越しの課題だったんです

↑ のレポートですが、1年前に作ってました。2019年4月5日に 1st バージョンを公開してます。
ただ、その時はスケジュール更新に対応していませんでした。

スケジュール更新ができなかったその訳は

もともとどうやって 1st バージョンを作っていたかというと、以下の感じです。
結構複雑なので、飛ばしていただいても OK ですし、またわかる必要もありません。参考までに載せておきます。

1st バージョンのデータ取得方法 (Power Query)

まず二つのリストを用意します。年月リスト と 年月から作成する URL リスト です。

これが年月リスト (YearMonthList)

これが URL リスト (FilePathList) です。
冒頭で説明したようにファイルが 52 個あるので、URL もそれだけあります。しかも毎月増えるのです。このリストではそれを Power Query で実現しています。

次は上記の2つのリストの値をひとつずつ受け取って、実際に Excel をダウンロードし、データを読み出す関数です

GetExcelData という関数です。結構力業ですwww
でも基本的にマウス操作のみで Power Query エディターからできることだけでやっています。

あとは上記２つのリストと関数を呼び出すクエリを作ります。

やってることはそれほど難しくなく、リストをループさせながら、現在行の値を GetExcelData 関数に渡して、その結果をがっちゃんこってやっています。

さて、この方法で Power BI Desktop では全く問題ありません。ですが、Power BI Service へ発行しスケジュール更新を設定する。その時刻になると実行されるのですが、これは失敗します。もちろん手動更新も失敗します。

理由は Power BI Service では動的に変わるデータソースを利用したレポートの自動更新は、現時点ではできないからです。

ではどうやって対応したか？

ここ数回 Power BI Tips の記事で フォルダ系コネクタ を扱っていました。で、ふと思ったのです。

「そっか、いったんファイルをどこかに保存しといて、フォルダ系コネクタ を使用して定期的に増える n 個のファイルを取得すればいいんじゃね？」

というわけで、フォルダ系コネクタ のうち、みんなが手っ取り早く使える SharePoint フォルダー にファイルを当月分のみ保存することができればいいわけです。
はい、こういうのは Power Automate が得意とすることですね。

Power Automate の構成

**Power Automate の構成図はこんな感じです。

1. 繰り返しトリガー
2. HTTP アクション
3. Cloudmersive で XLS を XLSX に変換
4. SharePoint サイトにファイルをアップロード

1か月に1回ファイルが増えますので、この Flow も1か月に1回動かすようにしています。（①）
HTTP トリガーで日本自動車販売協会連合会のサイトから該当月のファイルをダウンロードします。（②）

ここでひとつ困ったことがあります。残念ながら、日本自動車販売協会連合会が提供しているファイルは xls (97 -2003) 形式なので、そのままだと都合があまりよろしくありません。というわけで、xls を xlsx に変換するサービスを探したところ、Cloudmersive というサービスを発見しました。しかも Pwoer Automate のコネクタ があります。無償のプランもあります。あら、便利😁

はい、無事に変換したファイルを SharePoint サイトにアップして終了です。
あとは Power BI Tips で前回までに説明しているように、Power BI Desktop で SharePoint フォルダ－ を選んで、データを取得して、レポートを作成してください。

自分で言うのもなんですが、なかなか 汎用性の高いアーキテクチャ だなと、感心してしまいました。

参考までに Flow の全体像を載せておきます。

これが全体像。

前半で [現在の時刻] ⇒ [タイムゾーンの変換] で yyyymm を作成して、それを URL と連結して変数に保持することで、当月のファイル名を作成しています。

後半は [HTTP] でファイルを取得して、[Cloudmersive] でファイルを変換後、[SharePoint] にファイルを作成しています。

やりたいことをまっすぐやる、非常に素直な Flow です😎

大事なところは…？

「使用している業務システムで、毎月 CSV をエクスポートしてダウンロードし、Excel でデータを整えて、先月までのデータとマージして、グラフを作成しています」

こんな人も多いでしょう。今回紹介している方法で置き換えられるのはダウンロード対象のファイルが URL で決まることが必要です。

• URL の最後にファイル名がある（←紹介しているケースはこれに該当）
• URL の後ろにクエリパラメータ (?YearMonth=202004) のように対象のファイルを指定する何かを指定

こういう場合は今回の方法が適用できる可能性があります。
一方 URL にそういった対象のファイルを指定するものがない場合は、適用できないかもしれません。別の工夫で実現できる可能性はあります。

いずれにしろ、これ自体で流行りの RPA 的な要素がありますし、既に使用されている RPA ツールと組み合わせて、ファイルを取得するところは RPA に任せて、フォルダ系コネクタ でアクセスできる場所に保存するといい感じに連携できるでしょう。

まとめ

いかがででしたでしょうか？
Power BI だけで実現できないことは Power Platform の他のサービスの チカラ を使って、得意なことを任せると実現可能なことが広がります。
特に Power Automate は使いどころ次第です。ぜひぜひ、柔軟に考えてみてください！

以上ですー。最後まで読んでいただきありがとうございました。また気が向いたら、Power BI Tips シリーズ書きます。
皆様からのリクエスト、お待ちしております。

何かリクエストがあれば、以下までー🤗
Twitter: https://twitter.com/yugoes1021
Facebook: https://www.facebook.com/yugoes1021
LinkedIn: https://www.linkedin.com/in/yugoes1021/