データ置き場難民
Power BIを使っていると、データの置き場所というのはいつも悩ましい問題かと思います。
わたしもかれこれPower BIを使い始めて4年目になりますが、ずっとこの問題に付きまとわれております。
特に弊社事情なのですが、
・メインデータの一つであるERPからのデータが格納されているOracleへの接続が、IT管理のゲートウェイ経由
・よって、自前のゲートウェイがインストールできない。（もしかしたらできるかもしれないのですが、何年か前に自分であれこれやってどうにもならなかったので、諦め・・・）
・よって、共有サーバーにファイルを置くと、Service側でスケジュール更新ができない。
・ちなみに、CDSも使えない。
というわけで、アドホックな細かいデータは自分のOneDriveに、定期的に追加されるようなデータはSharePointのドキュメントライブラリに格納しているというのが目下のところです。
データフローでSharePointフォルダーコネクタを使ってみる。
①AccesからPower BI
顧客から毎月もらっているある程度の容量のデータがございまして。
2年ほど前まで、みんな大好きAccessで処理してレポート作成していたのですが、なんとかPower BI化に成功。
2年前までのデータは、元データからAccess内で整形してため込んでいたので、それを年次CSVに吐き出して、SharePointのドキュメントライブラリに格納して、Power BI DesktopのSharePointフォルダーコネクタで読み込み。
こちらの記事を参考にしました。
書いてある通りにポチポチやれば使えたので、何がどうなってるかは理解しなくともとりあえずできました。
毎月もらっているデータはExcelなので、これもSharePointのドキュメントライブラリに格納して、こちらはWebコネクタで毎月のファイルを個別に読み込んでおりました。
まぁ言ったら10分20分の作業なんですけど、毎月ポチポチやるのメンドウ・・・でも、大規模修繕をするのはもっとメンドウ・・・・で、ポチポチやり続けること2年・・・。
なんか気のせいかもしれないんですけど、pbixファイルの挙動がめっちゃ遅くなってきたような気がしてきて、やっと重い腰を上げました。
②Power BIデスクトップからPower BI データフロー
これを機会に、直接Power BIで読み込むのではなく、Power BI データフローで読み込もうと思いました。
毎月受領するExcelファイルを、SharePointのドキュメントライブラリに格納して、SharePointフォルダーコネクタで読み込み。
データフローがGAしたばかりの時には、データフローでSharePointフォルダーコネクタが選択できなくて、一度Power BI Desktopで読み込んで、クエリの内容を詳細エディタでコピー&ペーストなんてやってました。
今はちゃんと選択できます。
手順は先ほどご紹介したQiita記事と全く同じです。
コツとしては、
記事の手順に従って、サイトのルートURLを指定して、そこから該当のフォルダでフィルターすると、こんな感じになり、
Content列の右のアイコンをクリックすると、サンプルファイルとか関数とかが自動生成されて、ファイルが結合されるのだけど、クリックする前にクエリの名前を変更するのがおススメです。じゃないと、自動生成されたフォルダ名とかがこんな感じになっちゃいます。
ま、後からも変更できるんでコツってほどでもないですけど・・・・。
で、問題のExcelなのですが、なんでこうなった？？と心が叫びたがっちゃう感じの・・・・
なんで商品を右に並べちゃった？
しかも、なんで1列目と2列目に商品コードと商品名を縦に並べちゃった？？
③落とし穴
で、このクソExcelをクエリで編集するわけなんですが、なぜかデータが上手く読み込まれない・・・・。
（しかも、データサイズがまぁまぁいい感じなので、読み込むのにめっちゃ時間がかかった上にこれ・・・）
「DataFormat.Error: We couldn't convert to Number. XXX Request ID: 4c51c456-29e3-df2d-2cdb-f4d52d514801.」
みたいなことを言われ怒られます・・・・。
ちょっと何言ってるかよくわからないので、しかたなくPower BI Desktopで読み込んでみると、なんか、このヘッダーの処理が最初のファイルにしか適用されていない模様で、ヘッダーの内容がデータに含まれちゃってる・・・・。
なぜ？？なぜなの？？？
もしかして、SharePointフォルダーコネクタってcsvじゃないと使えないの？？？
（そんなわけない）
④「サンプル」クエリだよ！
あーでもない、こーでもないと昨日の午後いっぱいを無駄にしたわけなのですが、
このクエリたちをあらためてよく見て、気が付いた！！！（やっと！！！）
「サンプル」クエリだよ！！！
はい、わたくし、ヘッダーの処理などの編集をクエリ1に施していたわけです・・・・。
フォルダコネクタは、サンプルクエリ（この例でいくと「サンプルファイルの変換」クエリ）を全てのファイルに適用して、その適用された状態を結合したのが本体のクエリ（この例でいくとクエリ1）なわけですね・・・・。
なので、各ファイルに適用させたい処理は、サンプルクエリ側に書いてあげないといけなかったわけなのです・・・。
というわけで、サンプルクエリに必要な編集をしたら、ちゃんと読み込みできましたYO!
どうでもいいけど、フローのステップについているアイコン、分かりやすくて好きです。Desktopにもつけばいいのに。
まとめ
・フォルダコネクタの各ファイルの加工はサンプルクエリで行うべし。
・本体クエリは各ファイル加工後に結合されたものがデータソースとなる。
落とし穴は自分で掘っていた、というオチでした・・・・
・・・これでPower BI歴4年目とかお恥ずかしい限りなのですが、もし同じ内容でお悩みの方のお役に立てば幸いです・・・・