こんな人に読んでほしい
よく hogehogeData_yyyyMMdd.csv のような毎日出力されるファイルをPower BI で扱うことのある人が対象です。
すでにSharePoint Online（以下SPO）のドキュメントライブラリに、大量のデータがあるなかで、特定の条件のファイルだけを対象としたいとき、取り込みの順番をしくじるといつまでもダウンロードが止まらないという事態に陥ります。そういうのに困っている人向けの記事です。
まとめるとこんな感じの内容です
- 使うのは１ファイルだけなのに結合しちゃダメ
- 決まったファイルならSharePoint フォルダではなくWEBを使おう
- 日付で絞ったなら、展開前に動的参照できるようにひと工夫
- Date Created列を使えないならファイル名つかえば？
サンプルデータ
以下のような簡単なCSVデータを用意しました。testSite>ドキュメント>tmp フォルダに毎日販売データが追加されるようなシチュエーションです。
大量のログファイルのうち、対象は１つだけなのに結合しちゃだめ
データの中の特定の日付だけをレポートしたいっていう場合
あなたならどうしますか？
まずは残念なケースから。やりがちだけれどやっちゃダメ。SPOフォルダから結合したあとの絞り込み。
SPOフォルダを選んだあと、ファイルが置いてあるドキュメントライブラリで絞り込み。ここまでは許そう。
しかしそのあとコンテンツの結合ボタンを押してしまうと・・・。
指定したドキュメントライブラリにあった、たくさんのCSVファイルが結合されます。全部使って分析するならいいのですよ。でも、いまやろうとしているのは、１ファイルだけの分析です。
そして営業日でフィルタリングしちゃう。まあ、いいんだけどさ。
今はファイルの数がそれほど多くないので良いとして、これが巨大なファイルを大量に保存されている場所だったらどうでしょう？ Power BI デスクトップの場合、いちどすべてのファイルがダウンロードされたうえで、絞り込みがなされます。これって通信インフラと時間の無駄遣い。もったいないです。
特定の１ファイルって決まっているならWEBをつかおう。
今回は日付がついたログのようなファイルをサンプルにしていますが、たった１ファイルを選択するためにSPOフォルダをつかうのはもったいないです。こういうときには、WEBをつかってファイルのURLを指定して取ってくるほうがよいです。
まず、SPOフォルダの中でとってきたいファイルのURLを調べます。これには、ファイルの三点リーダーから「詳細」をクリックします。
画面右端の詳細画面を一番下までスクロールすると、ファイルのURL（パス）が取得できます。（ファイルのリンクを取得しちゃうと純粋なファイルのURLが取れないので注意！）
こんどは、データソースに「WEB」を選択します。
先ほど取得したファイルのパスを貼り付けます。
コンテンツへのアクセス認証を求められたら、「組織アカウント」を選択します。なぜなら、SharePointの領域は組織の人しかアクセスできない領域だからです。「匿名」というのはブラウザで誰でもURLを入力したらアクセスできる領域のこと。Yahooニュースなんかを取ってくるような場合は匿名でOKです。
もともとURLを直接指定しているので、フォルダで絞り込む必要はありません。１ファイルを狙い撃ちで指定する場合にはSharePoint上のファイルであってもWEB取り込みを使いましょう！
フォルダの中で一番新しいファイルだけを参照する場合
先ほどは参照するファイルを狙い撃ちしましたが、毎日増えていくログの中のなかから最新のものだけをチェックしたいという場合もあるでしょう。動的に１ファイルだけを選ぶにはコツがあります。
今回はSharePointフォルダーから始めます。
この後フォルダで絞り込むまでは同じ。最新のものを取得するので、
最新のファイルだけを取得したいので、Date createdの列を「最も遅い」でフィルタリングします。
一番最近作成されたファイルだけが残りました。ファイルが一つだけなので結合する必要はありません。Content列の「Binary」をクリックするとファイルが展開されます。
１ファイルだけが展開されました。
しかし、喜んだのもつかの間。次の日のログがSPOフォルダに追加されるとどうなるでしょうか。最新のファイルはhogeLog_20240509.csvのはずなのに、20240508のファイルがエラーに表示されてますね。
Power BI デスクトップで更新してみるとエラーになりました。どうしてでしょうか？
クエリの中身を詳細エディタで開いてみると、20240508のファイル名が指定されています。
ちょっとクエリのステップを戻ってみてみましょう。最新日付でフィルタリングしているので、20240508のファイルは既に最新ではありません。
次のステップでは、20240508ファイルが存在しないので、CSVを展開するため、参考にしようとしているファイルが見つからず、エラーになってしまっているんですね。
こういう時には、クエリを書き換えましょう。「フィルターされた行1」というのはエラーが出ていたひとつ前のステップの名前です。前のステップで絞り込まれたファイルの中の最初に出てくるファイルのことを{0}のようにあらわしています。0番目のコンテンツだから後ろに[Content]とつけます。すると、CSV変換の参考にするファイルが動的に指定することができました。先ほどまでは20240508を参照していましたが、今回は自動的に20240509を参照してくれるようになってます。
無事に取得することができました。
必用なぶんだけを取り込もう
実際のログ取得では、ある程度の期間についてファイルを取得したいことが多いです。必要な日数分を絞り込んでから結合させましょう。こんな風に書きます。
= Table.SelectRows(フィルターされた行, each Date.IsInPreviousNDays(Date.AddDays([Date created], -1), 2))
こちらの式では、本日を含む2日間だけを絞り込めます。たとえば何年分もログがあるフォルダに対して、過去31日間のようにしたければ最後の2の部分を書き換えればよいですね。
参考までに、クエリの中の値はパラメータ（変数）にすることもできます。
「パラメーターの管理」から「新しいパラメーター」を選択します。
パラメーターには適当な名前を付けて、種類は10進数。仮の現在の値を入力してOKをクリック
クエリの表示されている場所にパラメーターも表示されました。
パラメーターにつけた名前は、数字の代わりに使うことができます。図のように書き換えてやると、パラメーターに設定した4という数字で過去4日分のデータを取得する指定ができたことになります。
これの何がうれしいかというと、パラメーターはPower BI デスクトップから発行してPower BI サービスにアップロードしたあとで、ブラウザ上で変更することができるんです。
つまり、大量のデータを更新したい場合に、Power BI デスクトップでは直近数日の少ないデータで処理をしておいて、発行（アップロード）したあとにパラメーターを大きな数字に変更すれば、作業がぐっとしやすくなります。
実際にマイワークスペースに発行してみました。セマンティックモデルの三点リーダーから「設定」を開きます。
先ほど設定したパラメーターの名前と、値が表示されていますね。クラウド上ではこの値を大きく変えてから更新をかければオッケーです。運用上もいちいちPower BI デスクトップで変更をして発行をし直したりする必要がありませんから便利ですね。
Date Createdが使えないならファイル名から
実はサンプルのCSVファイルは本日まとめて作ったので、日付がいずれも同じ日です。これでは正確に取り込みたい日数を指定できません。PowerShellでまとめてログを取ったときなんかにはファイル作成日が同じになっちゃうのでこれまでの方法が使えないんですね。
今回の例では、ファイル名に日付が入っています。これをうまく使いましょう。
ファイル名が表示されているName列を選択し、「列の追加」> 「例からの列」> 「選択範囲から」をクリックします。
追加する列名を「日付」に変えて、任意の行の空欄をクリックすると、Name列に表示されているファイル名がコピーされます。
表示されているファイル名の一つを、日付の形になるように手で変更してやります。
今回は最初の行だけではうまく変換できなかったので、2行目もファイル名の数字部分を参考に日付のように手で変更してやると、次の瞬間、他の行がファイル名から日付部分を取ってくるような変換をしてくれました。変換式が表示されてます。ファイル名が一定の法則に従っているならこの方法はとても有効です。
日付の形になったのですが、初期状態では列はテキスト型になっています。日付/時間型に変更してやります。
あとは、さきほどDate createdを指定していた部分のクエリを[日付]に書き換えると、ファイル名に従った範囲指定ができるようになりました。日付で絞る場合には、先にお伝えしたようにCSV変換の参照ファイルがフィルタ範囲外に出ても問題ないように、ファイル名指定を 「前クエリの名前{0}[content] 」のように書いてエラー回避するのを忘れずに。
まとめ
SharePointフォルダ指定の取り込みはとても便利ですが、フォルダ全体を取り込んでからフィルタリングをするのはもったいないです。ファイル単位ならば、必要な範囲のファイルを選択してから取り込むことで、処理がぐっと軽くなります。
パラメーターを使った後で範囲を変更できる方法も非常に便利なので、ぜひ試してみてください。
動的な指定
ファイルを絞り込む場合に、参考にしていたファイルが見つからずにエラーとなってしまうケースが時々あります。ファイルにかぎらず、例えばExcelファイルを指定していたときに、シート名が変わってしまっている場合にもこの何番目という指定は役に立ちます。
Excelのシート名違いについても以前に記事を書いていますので、参考にしてもらえると嬉しいです。