PowerPlatform 中級編8 PowerBI③

前章ではPowerBIレポートのイメージを作成しました。イメージを元にレポートを作成します。

完成形

今回は、完成した形を先にお見せします。

消耗品は、餌２種類のみとしました。理由は餌に比べてトイレ用品はライフサイクルが長い（一回購入すると1ヶ月以上は持つ）ため、記録を待っているとレポートが作れない状態なので除外しました。後日記録が整ってから追加したいと思います。また、ペレットも牧草に比べて持ちが良いため、ペレットに関しては仮の終了フラグを立てて運用しています。本来なら牧草よりもペレットの方がライフサイクルが長いです。

PowerBIレポートの制作は文面での説明が難しいため、ポイントポイントを説明していきます。実際には自分の手を動かして作ってみてください。

ポイント1 PowerQueryの操作

とにかくネックとなる部分はPowerQueryの操作です。自分がつまづいた部分を記載していきます。

PowerQueryではテーブルを操作して目的のものを作っていきますが、同じテーブルを使いたい時があると思います。そういう時は「コピー貼り付け」ではなく、「複製」を使いましょう。理由は、コピー貼り付けだとコピー元の表の関係（リレーション）を引き継いでしまうためです。関係無いのに関係を引き継いでしまうと、データ取得時にエラーを発生させる要因となります。

リレーションはPowerBI上の左項目３番目「モデル」で確認できます。表と表との関係は線でつながっています。関係ある表はつながりを残しておかないといけませんが、関係がないのにつながってしまった表については線を消しましょう。

表が増えていけば増えていくほど関係も増えていき、見た目も複雑になります。名称にも工夫を、例えばコピー元の表を残しておくのであれば「親」を頭につけておくとか、ペレット関連なら頭にペレット、牧草関連なら頭に牧草という風に、自分も人も見て分かるような命名規則をつけておいた方が良いです。

PowerQuery操作時、一番頭を悩ますのは思っている値になかなか行きつかない事です。例えば今回のケースで、なかなかライフサイクルの算出まで行きつくまでに時間が掛かりました。

どうしても一足飛びで目的の値に行きつこうと考えてしまいがちですが、無理な場合は一つ一つのパーツをクエリに分けて組み上げていくのが結果的には早道になります。例えばライフサイクルの場合、

• まずは消耗品テーブルを複製し、日時昇順・消耗品終了フラグ付きを抽出する。
• 次にそれを複製して、最初のデータのみを消す。（２個目以降のデータを残す）
• それぞれのクエリにインデックス行を付与する。
• それぞれをインデックスでマージ（結合）する。
• 最初のクエリの日時を「開始日」、２番目のクエリの日時を「終了日」として、「終了日」ー「開始日」をカスタム行で算出する。←これが個々のライフサイクル
• マージしたクエリを更に複製し、グループ化してライフサイクルを平均する。

という順番で複製とマージを繰り返し、最終的に出た平均ライフサイクルを消耗品フラグ最終日に加算して、次回消耗品終了日を算出します。

熟練者ならもっと良い方法があるかもしれませんが、慣れるまではこういう地道な方法が一番筋道立っており組み上げも速いです。

ポイント2 フィールドの操作

厄介なのはPowerQueryだけでなく、PowerBIの型の部分にもあります。PowerQueryで望みのデータを取得できても、なぜか思い通りの視覚化ができない場合はこの部分を疑ってください。

よくある事として、数字が数字として認識されていないというケースがあります。数値でグラフを作ろうとしても、何故か件数のカウント値でグラフができてしまっている場合がこれに当てはまります。

フィールド値にチェックを入れて、「列ツール」という項目を見るとその列の情報が出ています。この中の「データ型」がテキストになっている場合、当該の状態になってしまいます。これを数値に変えましょう。「整数」「10進数」「固定小数点数」が選択できますので、該当する型を選択します。選択後、型が変わると不具合が発生する可能性がありますといったダイアログが出ますがOKしてしまえば良いです。（そもそもこの状態が不具合なので）これで数値操作ができるようになるので、合計なり平均なり自分の求めたい処理を選択すれば不具合は解消されます。

日付データも同様の不具合がよく発生します。テキスト型だと日付として認識されないので、時系列データを扱う際に問題が発生する可能性があります。そこでこの場合でもデータ型を変更します。日付型は「日付と時刻」「日付」「時刻」が選択できますので、該当する型を選択します。◯月◯日のような独自の書式を使いたい場合は、「書式」の項目で調整をかけてください。

その他、フィールドで選択した項目が視覚化フィールドにうまく反映されないとか（ドラック＆ドロップで入れていけば良い）、見た目が思った感じではないとか（視覚化のプロパティを設定する）、色々とネックとなる部分はあるのですが、上記２ポイント以外は手で調整できるレベルの内容でしたので、慣れていくことが上達の早道だと思われます。

レポート公開

レポートファイル「.pbix」は、オンラインに上げることでWEBないしスマホアプリで閲覧できるようになります。

データソースと書かれた部分に、サイトのURLが書かれたリストがありますので、設定を選択します。ここに認証を通してサイトをデータソースとして使えるようにします。認証を「OAuth2」（先進認証）を選択し、資格情報を編集する を選択すると、Microsoft365の認証画面になるので、認証を通します。問題なければ保存します。

資格情報が通っていれば、アップしたデータセットの設定の中の「データソースの資格情報」にSharePointが追加されており、問題なければ上図のように表示されています。問題がある場合は「資格情報を編集」を押し、再度資格情報を入れます。

この状態になれば、データソースの更新が可能ですので、最新のデータがレポートに反映されます。（ペレットは仮フラグを立てているので０になってしまっていますが、牧草の残量は予想値にかなり近いものになっていました）

iOSアプリで動かすと下記のようになります。アプリで観れるとかなり身近なものになりますね。とりあえず当初立てた目標・あるべき姿には到達できました。

今回、SharePointリストをデータソースとしたので、データの管理自体はクラウド上で行えました。（レポートの作成もできるといいのですが）これが例えばローカルのデータだった場合、クラウドとオンプレを繋いでおかないとデータの更新は行うことができません。「オンプレミスデータゲートウェイ」というアプリを導入すればそれが可能です。また需要がありましたら説明いたします。

次のステップ

目標達成ということで、振り返りを行います。