はじめに
PowerBIにはWeb版のPowerBI Service／デスクトップ版のPowerBI Desktopがあり、
接続先データソースのバリエーションはデスクトップ版の方が豊富という特徴があります。
そのため、Webで見たいレポートも開発自体はデスクトップで行い、Webに発行して使うケースが多いようです。
1
今回、次のような要件で構成を考えました。
- データソースはフォーマットの異なる複数のCSVファイル
- 各CSVファイルは週ごとに新しいデータを差分で追加したい
- その際、過去データとkeyが同じデータは新しい方で上書きしたい（差分更新）
- ファイルの配置先は運用担当全員が操作可能なサーバー上にしたい
- 最終形はPowerBI Serviceに発行してデータソースをスケジュール更新したい
- リアルタイム性は不要でコスト面重視
同じような要件で考えている方に参考にしていただければと思います！
※SharePointを使用するため、Microsoft365を契約していない会社の場合は別の方法をとった方がよいかもしれません。
構成図
データソースにSharePointを使用し、CSVのフォーマットごとにフォルダを分ける構成にします。各CSVの週ごとの差分データは、年月日のタイムスタンプを付与してフォルダに格納していきます。
図には表されていませんが、開発時のみPowerBI Desktopを使用し、リリース以降はPowerBI Serviceのスケジュール更新でデータ連携をします。
構築の流れ
リソースを配置する
SharePoint上に、データソースのフォーマットごとにフォルダ分けをしてCSVファイルを配置します。
今回は、以下の構成でフォルダを作成しました。
ドキュメント>Test>Fruitsの下にタイムスタンプ付きのCSVファイル
中身は、その日のフルーツの価格をまとめた次のようなテーブルとします。
FruitsPrice_20220722.csv
| key | name | price |
|---|---|---|
| 1 | apple | 200 |
| 2 | orange | 150 |
| 3 | lemon | 100 |
FruitsPrice_20220729.csv
| key | name | price |
|---|---|---|
| 1 | apple | 180 |
| 2 | orange | 170 |
| 4 | melon | 1000 |
同様に、野菜の価格をまとめたVegetablesフォルダも作成しファイルを2つ配置しておきます。
(ドキュメント>Test>Vegetables>VegetablesPrice_YYYYMMDD.csv)
リソースと接続する
PowerBI Desktopでデータを取得する際、
ファイル>SharePointフォルダーを選択します。
サイトURLに接続先SharePointを指定します。
ここでフォルダパスまで入力すると上手くいかないので、あくまでサイトのURL（/sites/XXXXまで）を入力します。
Microsoft365のアカウントでログインします。
すると、接続先SharePointのファイルが取得できます！
階層の情報はAttributes列の右に隠れているFolder Path列で管理されており、Name列には全階層の全てのファイルが並ぶ形になります。
「データの変換」をクリックするとPowerQueryの画面が開くので、ここから必要なフォルダを絞り込んでいきます。
フォルダを絞り込んでデータを取り込む
データインポート後、「データの変換」をクリックして開いた直後のPowerQueryの画面がコチラです。
ここからは、画面上部のツールと表の上にある式を使って必要なフォルダを特定し、データに加工を施す作業です。
Folder Path列の「▼」をクリックし、テキストフィルターから「指定の値を含む」を選択します。
今回はFruitsのフォルダを取得したいので、「指定の値を含む」に"Fruits"を設定します。
これで、Fruitsフォルダの2ファイルを取得できました。
類似の名前のフォルダがある場合は、/Fruits/と区切り文字も含めて設定すると確実です。
次にファイルの中身を開いていきます。Content列にある「ファイルの結合」ボタンをクリックします。
文字化け等の問題がなければそのままOKをクリックします。
これで、SharePoint上のFruitsフォルダにある2つのファイルの中身を
PowerBI上で1つのテーブルとして扱えるようになりました！
日付の新しい方のデータで上書きする
上書きする際の判断に使用するキー列を選択し、行の削除>重複の削除を選択します。
これで、各キーごとに最新のレコードを残すことができました。
同じ設定を流用して別のフォルダからCSVを取り込む
ホーム>管理>複製をクリックすると、
先ほど作成したフルーツデータの「データ取得→フォルダ絞り込み→日付順に上書き」のステップが丸ごと複製されテーブルが作成されます。
※コピー時は名前が(2)などとなってしまうので、画面右のクエリの設定からわかりやすい名前に変えましょう…
この状態で、フォルダ絞り込みの条件を変えれば取得元のフォルダを変更することができます。
画面右の適用したステップで「フィルターされた行」を選択し、
画面中央の式を直接編集してFruitsだったところをVegetablesに変更します。
そうすると、データソースVegetablesに対しても、先ほどのFruitsと全く同じように加工された状態にしてくれます。
※Vegetablesのデータは、この時点で日付の古い方のデータが採用されてしまっています。
この場合の対処は別の記事に記載します。
データフォルダが3つ以上ある場合も同じように複製していけばOKです♪
後続処理が違う、差分更新チェックのKey名が異なる、などの場合も
複製した後で不要なステップを削除したり、重複削除の式の条件を直接編集するのがスムーズかと思います。
ここまでの処理が終わったら、画面左上の「閉じて適用」をクリックしてPowerBIの画面へ戻ります。
取り込んだデータを確認する
FruitsとVegetablesの2テーブルができていることが確認できました♪
（PowerQueryで名付けた名前がテーブル名になるため、この画像ではYASAIになっています^^;）
Web発行後、データソースの資格情報を更新する
最後に、Webへの発行後、PowerBI ServiceからSharePointに接続する認証を済ませます。
設定画面から
データソースの資格情報でエラーが出ているかと思いますので、
「資格情報を編集」をクリックします。
「OAuth2」、「Organizational」で組織のアカウントを使ってサインインします。
無事に更新されたら、後はWeb側でスケジュール設定をして自動でデータソースをSharePointと同期していくことが可能です！
まとめ
Microsoft365を使用している社内で運用する場合、かなりお手軽な構成かと思います。
データ基盤から構築するほどのデータ量ではないけど何か分析したい、Excelレポートから乗り換えたい、などなど…ぜひお試しください♪