2021年6月8日 / 最終更新日時 : 2021年12月30日 officeの杜 Microsoft365

Power BIでデータ解析を視覚化する　活用編

これまで2回に渡って、Power BIの基礎編、作成編と紹介してきました。ここまでで概ね、Power BI Desktopを用いて、データの整備や解析は出来ます。今回のエントリーはPower BI自体に備わってるいろいろな機能での操作で、データ解析を活用してみようという内容が目的になります。

また、Power BIとPower Automateの連携やデータ反映の自動化なども挑戦してみたいと思います。

今回使用するアプリと資料

Power Queryの活用

Excelでもおなじみのデータの加工に貢献してくれるPower Query。他のBIツールと違い、こういった機能があるのが、Power BIの利点の一つ。全く同様にPower BIでも利用が可能であるため、すでに学習済みの人にとっては学習コストゼロで、Power BI上で利用できますし、そうでない人も今後Excel上でも役立てる事が出来るので、学んでおくべき機能です。

但し、Accessのクエリ等とはちょっと異なるので、その機能と癖はよく見極める必要があります。

データソースの変更

自分個人だけの利用であっても、pbixファイルを他人と共有する場合であっても、問題になるのが「データソース」の更新。途中で同じデータ形式だけれど、違うExcelデータにしたいとなると、データソースを変更して更新を掛けないと、Power BI上でデータの入れ替えが出来ません。また、この時のデータソースはファイルに対してのフルパスで指定されているため、他人に渡した場合、当然その場所にそのファイルは存在しません。

そこで、こういったデータソースの変更を要するシーンに於いてデータの入れ替えをする手順はよく使うので覚えておく必要があります。変更手順は以下の通りです。

  1. Power BI Desktopでデータを読み込ませた状態にする
  2. フィールドにデータソースが表示されてるので、右クリック -> クエリの編集を実行
  3. Power Queryが起動するので、リボンより「データソースの設定」を開く。
  4. データソース設定が出てくるので、xlsxなどへのフルパスを選び、ソースの変更をクリック
  5. Excelの場合、ファイルパス画面が出てくるので、参照をクリックして新しいファイルを指定し、OKをクリックする
  6. データソース設定も閉じる
  7. Power Queryに戻ってきたら、プレビューの更新をクリックしてみて最新の情報に入れ替わっているか検証。
  8. 最後にリボンより「閉じて適用」をクリックしてPower BIに反映する
  9. 再度データソースに対してPower BIが読みに行くので、これでエラーが出なければ完了。
  10. 最後、Power BI上でファイルに保存するのを忘れずに。

図：データソース設定画面

列分割

時々現場のデータで見かける厄介な形式として、1つの列内（セル内）にて、複数の値がカンマ区切りなどで入ってるケースがあります。本来Power BIで解析する前にこういったデータはデータソースの側で前処理をしてから分析を行うべきですが、そうもいかないケースというのもあります。そこでこういったデータに出くわした場合に、Power Queryにて１つの列毎にデータを切り出す機能が列の分割です。

  1. Power BI Desktopでデータを読み込ませる
  2. フィールドにデータソースが表示されてるので、右クリック -> クエリの編集を実行
  3. Power Queryが起動する
  4. カンマ区切りの値が入ってる列をクリックする
  5. 右クリック -> 列の分割 -> 区切り記号による分割
  6. コンマで区切り記号の出現毎のままで、OKをクリック
  7. この段階で、指定の列の値がカンマ区切りの値毎に列に分離されて、列が生成されます。
  8. 閉じて適用をすると、分割した列のソースで適用がなされます。

図：列の分割機能

図：データ列の値に応じて列が分割された

クロス集計

いわゆるピボットテーブルと同じものですが、ピボットテーブルと異なり、データの再利用がしやすいので、Excelで活用する場合もピボットテーブルよりもPower Queryでクロス集計する機能を使ってクロス集計したほうがオススメです。今回は、金属名・年度・金額の3列のデータからクロス集計を作ります。

  1. Power Queryにて集計されていない生データのExcelを読み込ませる
  2. フィールドにデータソースが表示されてるので、右クリック -> クエリの編集をクリック
  3. クロス集計する時の列名にあたる列（今回は年度）の列を選択します。
  4. リボンより変換 -> 列のピボットをクリック
  5. ダイアログが出るので、値列には集計する数値の列を指定（今回は金額列）する
  6. オプションの値の集計関数はデフォルトが合計となってる。平均や中央値、最大最小、カウントなどを選べるので状況に応じて指定する
  7. すると、年度の分だけ列が作られ、金属名にてクロス集計された表が作られる
  8. あとはこれを元にレポートを作成すれば完了

図：オプションで集計関数の指定が可能（合計や平均）

ピボット解除

データをもらったらデータではなく、「集計された表だった」というケースが現場では多々見受けられます。しかも、セルに直打ちされているため、そのまま二次加工に使おうとすると非常に使いづらいということを作成者が分かっていないケースがあります。大本の生データがほしいのに。これを二次加工するにはマトリックス変換をしてあげなければなりません。

多くのこうした集計表はクロス集計表のようなスタイルになっており、これらから生データを復元する機能がPower Queryのピボット解除。解除してしまえばPower BI Desktopでそのデータを元に解析を行う事が可能になります（但し、もともとの表が集計表であるため、個々のデータは集計値でしかないため、大味な分析にはなってしまいます。今回は、金属の価格推移表を元にマトリックス変換してみます。

  1. Power BI Desktopで集計表のExcelを開く（表はテーブル化しておきましょう）
  2. 開いたら、フィールドにデータソースが表示されているので、右クリック -> クエリの編集をクリック
  3. 列項目の部分だけを選択する（サンプルだと金属名の列を除いて年度の列だけ選択する）
  4. 変換⇒列のピボット解除をクリックする
  5. リボンより閉じて適用をクリックして反映する
  6. マトリックス変換が完了したので、このデータを元にレポートを作成する。

図：縦横変換とは違いますよ

図：Power Queryでマトリックス変換出来た

図：推移表を作ることが出来た

DAX式と列追加

Power Queryにて既存のデータソースに対して、Accessのように計算式で列を追加するといったシーンで利用するのが、DAX式。例えば数値の列に対して一定の係数を掛けた値を演算した結果を列として加えたいといったようなケースで利用することが可能です。合計、平均、カウントの他にIFなどもあり、詳細はDAX関数リファレンスを見ながら作ることになります。

人口動態統計のxlsxからPower BIへ読み込ませて、DAX式を使って演算してみます。今回は、行毎に値の評価を行うCalculate関数とSUM関数を使ってみたいと思います。

  1. Power BI Desktopを起動し、人口動態統計のxlsxファイルを読み込ませる
  2. 右サイドバーのアイコン「データ」を開く
  3. リボンより「新しいメジャー」をクリックする
  4. Excelのように数式入力欄が表示され、メジャー = が表示されてる状態になる。
  5. 数式欄は、出生構成割合 = SUM(Sheet[出生総数]) / CALCULATE(SUM(Sheet[出生総数]),ALL()) と入力する
  6. 出生構成割合の列をクリックして、リボンより書式 -> パーセンテージ、小数点以下は3桁表示に変更する
  7. この出生構成割合は、データの項目としては列として表示されません。列として表示したい場合は、テーブルツール -> 新しい列をクリック後、6.と同様の書式にすると良いです。但し、数式は出生構成割合2 = Sheet[出生総数] / SUM(Sheet[出生総数])となり、5.と異なります（列とメジャーでは挙動が異なる）。
  8. レポート画面に戻り、視覚化から「テーブル」を追加、値として年度、都道府県、出生総数、出生構成割合を追加。出生構成割合が行の出生総数 / 全体の出生総数合計のパーセンテージ表示になっているか確認。
  9. あとはこのデータソースを元に可視化を追加してレポートを作成すれば良いです。

今回計算対象としては全体合計にしましたので、calculate関数では引数としてALL()を利用しています。実際の現場では、全体ではなく、特定の列の、例えば今回で言えば年度などでフィルタして各年度の総合計に対する割合といったような計算を行うシーンが多いのではないかと思います。この場合、ALL()の引数には、Sheet[年度]を追加したりします。

図：1行ずつ評価して全体に対する割合を出してみた

M言語を使ってみる

言語仕様

Power QueryにはM言語と呼ばれる独自のスクリプト言語環境が装備されており、これまでもクエリ編集で加工を加えた結果は、実は後ろでこの言語によってスクリプトが自動生成されていたりします。基本的な型は

letで様々な処理をし、inでそれを出力する。また、引数を取る関数のようなスタイルの場合には、以下のような形で構築します。こうすることで、実行時にパラメータ入力が要求され、そのパラメータに応じてクエリが実行されて結果を吐き出すようになったりします。

図：こんな感じでクエリにはスクリプトが定義されてる

ユニークな値を取り出す

このM言語を利用して、特定の列に於けるユニークな値を取り出してみたいと思います。前項の金属価格推移マトリックスのピボット解除後のデータにある金属名の列かに対して、M言語でユニークな値を取り出してみます。

  1. Power Queryにて、ピボット解除後のデータを出しておく。
  2. 左サイドバーで右クリック -> 空のクエリをクリック
  3. クエリ1が作成されて、入力欄に、「=List.Distinct」と入れてみる（これがユニーク値を取り出す関数です）
  4. 次の画面にて、列の選択を実行します。
  5. テーブル名と列の選択にて金属名を選択してOKをクリック
  6. 呼び出しをクリックする
  7. ユニークな金属名が抽出される（呼び出された関数として作られる）
  8. リボンより、ホーム => 詳細エディタを開く
  9. すると今回の処理のM言語が書かれている。初めからこのような形でコードを記述すればダイレクトに処理がなされます。

より高度な記述をする事で、たとえばREST APIでリクエストした結果、複数ページに渡るケースで、次のページのデータを続けて受信して、全レコードを取得した結果を表示させるといったようなことも可能になります。

図：M言語の記述

図：ユニーク値が抽出出来た

Power Automateとの連携

Power BI上から設置ボタンをクリックすることで、Power BI上のデータをPower Automate側に渡し、フローを実行する為の機能として、Power Automate Visualと呼ばれる特殊なカスタムビジュアルがプレビュー版ですが、Microsoftから提供されています。以下の手順でビジュアルを追加し、Power Automate上でフローを構築する事で、様々なタスクランナーを構築可能です。

Power BI側の作業

今回はガントチャートのpbixファイルを利用して、この機能を試してみたいと思います。

  1. Power BI Desktopでファイルを読み込む
  2. リボンより、その他の資格エフェクトをクリックし、AppSource空をクリックする
  3. ダイアログの検索窓よりautomateで検索して、Power Automateを見つけ、追加をクリック
  4. 視覚化にPower Automateのアイコンが表示されるのでクリックして、ビジュアルを追加
  5. 4.のPower Automate Dataにデータソースから適当にデータを追加する
  6. 4.で追加したビジュアルの右上にある「…」をクリック -> 編集をクリックする
  7. Power BI Desktopの中にPower Automateの画面が表示される。現在４つほどフローテンプレートが表示されています。Power BI Desktopでログインしてるテナントに自動で接続されるので、アカウントが複数ある場合は注意。
  8. 左上にある「＋新規」をクリックしてインスタントクラウドフローをクリック。クリックされたPower BIボタンというフローが自動で追加されます。

ここまでが、Power BI Desktop側の処理となります。

図：カスタムビジュアルとして追加する

図：ビジュアルにデータを追加した様子

Power Automate側の作業

前項に続けて、Power Automateにフローを普通に追加していくことになります。今回はTeamsにメッセージを送信してみようと思います。

  1. 新しいステップをクリックする
  2. 検索窓でTeamsを検索し、「メッセージを投稿する（v3）」を選ぶ
  3. チーム、チャネルを選びます。
  4. メッセージをクリックすると、Power BI側の送信項目であるデータソースの列名などが出てくる。今回は、ユーザIDとタイムスタンプ、タスク名等をメッセージに組み込んでいきます。
  5. 列名をクリックすると自動的にApply to eachのループが自動で作成されてレコード単位でTeamsに投稿されるように構築されます。
  6. 保存をクリックする
  7. 左上のレポートに戻るの下にある「←」をクリックして、Power Automateの一覧画面に戻る
  8. 今作成した「ApiConnection」と名前のついてるフローのラジオボタンをクリックして、適用をクリックする
  9. レポートに戻るをクリックする
  10. 戻ると、Power Automateビジュアルが「Run Flow」というボタンに変化する。この文字は、視覚化の書式にある「Button text」で変更可能。

図：Teamsのフローを構築してみた

図：適用を押さないと紐付けされないので注意

図：ボタンが完成した

実行してみる

作成したボタンは、クリックではなく「Ctrl+クリック」で実行されます。送信されるデータの上限は1000レコードになりますので、送信する場合には注意が必要です。送信を実行すると「トリガー中」のメッセージが表示され、完了しても特にメッセージは表示されません。実行が無事に確認するには以下の通りです。

  1. Power Automateを開き、マイフローから作成した該当のフローを開く
  2. 画面下に実行履歴が表示されていて、状況が「成功」ならばOK
  3. この画面はPower BI上のPower Automateの作成画面からも同様に確認可能です。
  4. この機能はPower BI Desktopのみであり、オンライン版では動作しません。

実際には通知のような機能よりも、こちらを参考にPDF出力でもして、メールやTeamsに１件だけ投稿するのがベストです。

図：レコードの数だけ送信されてしまうので要注意

図：実行状況の確認

データ反映の自動化

Power BIは発行にて、オンライン版にデータを発行出来ますが、これらの作業はそのままではユーザが手動で発行の手続きを実行しなければ反映しません。そこで有償の機能になりますが、Power BI上の自動更新機能を利用して、Power BI Desktop上で行ったデータの反映をオートマチックに行うことが出来るようになっています。

手段としてはPower BI DesktopでのDirectQuery機能を利用する手段と、オンライン版上で自動更新の設定を行う方法があります。

オンライン版の自動更新設定

オンライン版Power BI上でデータソースの更新を行わせる方法です。オンライン上で設定する事になるので、データソースがローカルPCの場合には、Power Automate Desktopのように「オンプレミスデータゲートウェイ」のインストールと設定が必要になります。データソースもオンラインの場合（AWS上のMySQLであったり、SharePoint Online上のExcelであったり）には、オンプレミスデータゲートウェイは不要です。

今回は、SharePoint Online上のExcelに接続して解析するファイルを発行させています。

  1. Power BI Desktopでオンラインのデータソースを利用したファイルを作成する。
  2. 発行をクリックして、オンライン版のマイワークスペースにデータを送り出す
  3. Power BI オンライン版にサインインして、マイワークスペース -> データセット+データフローの一覧を開く
  4. 対象のプロジェクトにマウスカーソルを移動すると、「更新のスケジュール設定」というものが出てくるのでクリックする
  5. ダイアログでまず、データソースの資格情報をクリックする
  6. 認証方法は「OAuth2」、プライバシーレベルの設定は「Organizational」を指定し、Microsoft365アカウントでログイン処理を済ませる。ローカルデータの場合ここで、オンプレミスデータゲートウェイの設定が必要になります。
  7. スケジュールされている更新を開く
  8. データを最新の状態に保つのチェックを入れてオンにする
  9. 更新頻度、タイムゾーン、時刻の設定を入れて適用を押せば、指定時刻に自動でデータを取得して反映するようになります。
  10. もちろん手動更新もこれで行えるようになります。

図：自動更新の設定箇所

図：これで自動更新されるようになる

DirectQueryを使用した自動更新設定

オンライン上のデータベースサーバ等に於いて、DirectQueryを使用して接続してる場合には、Power BI Desktop上での自動更新が可能になっています。自動更新を使えるようにするには、以下の手順で設定します。

  1. Power BIでDirectQueryで接続できるデータソースに接続してファイルを作成する
  2. ビジュアルを追加してる場合、追加したビジュアルを選択していない状態で、視覚化の書式を開く
  3. 項目に「ページの更新」という項目があるので、スイッチをオンにする
  4. ページの更新を選ぶ。今回は自動更新を選択
  5. 更新時間は30分毎としてセットしました。

これで、データソースに追加がなされた場合であったり、指定時刻毎にデータソースにアクセスしてレポートを自動更新するようになります。

※次項でDirectQueryでの接続方法について説明しています。

図：自動更新設定が使えるようになった

その他の設定

DirectQuery

Power BIでのデータ接続方法に於いて、ファイル内にデータを持たずに、直接データソースと接続して解析する方法になります。しかし、DirectQueryが利用できるデータソースは結構限られている為、この設定を使っての更新は多くの人が使えるというものではないと思います。主に

が日本国内ではDirectQueryが使えるデータソースとなると思います。この手法での接続手順は以下の通りになります。今回は、Google BigQueryにテーブルを用意して、そちらにDirectQueryで接続させています。予め、BigQuery側でsoniqueというデータセットを用意して、tomatoというテーブルと値を格納してあります。

  1. Power BI Desktopにて、データを取得をクリックし、Google BigQueryを選択し接続します。
  2. サインインを要求されるので、アクセスできるGoogleアカウントでサインインします
  3. ナビゲータが起動して、用意されてるプロジェクト一覧が出ます。BigQueryを用意したプロジェクトを開き、データセットを選択、データベースを選択します。
  4. 読み込みをクリックする
  5. 接続の設定が出てくるので、DirectQueryを選択してOKをクリック
  6. テーブル接続されて、いつものようにデータソースとして利用出来るようになります。

これで、ページの自動更新なども出来るようになります。但しDirectQueryはファイル内にデータを持っていないので、都度データを取得することになる結果処理が重たくなる傾向です。

図：BigQueryのテーブル選択画面

図：DirectQueryで接続する画面

図：BigQueryのデータソースの様子

自然言語検索

Power BIではチャートを作成する場合は基本的にはユーザが、チャートのタイプに合わせて自らデータソースから対象のフィールドを拾ってきて構成するのが定石です。しかし、グラフを作成するのに慣れていない人にとっては、どんな列をピックアップすべきかわからないことも多いです。そんなケースに対応する機能が、Q&A機能で検索ボックスにルールに従ってワードを入れていくだけで、自動的に適切なビジュアルパーツを選んで描画してくれます。

人口動態統計のBIファイルを用いて作ってみます。

  1. Power BI Desktopで対象のファイルを開く
  2. リボンの挿入をクリックし、Q&Aをクリックします。
  3. 検索ボックス付きのビジュアルパーツが入る。英語のみと書いてあるものの、フィールド名などは日本語でもOKです。
  4. 試しに、show 出生総数 と入力すると出生総数合計値が計算されて、値を表示するだけの単一のパーツが生成されました。
  5. また、show 都道府県 出生総数 と入力すると、2つのフィールドを使って、自動的に都道府県別出生総数棒グラフが生成されます。
  6. さらに、show 都道府県 出生総数 by 2018 (年度) といったように、byで絞り込みを書けてあげると、2018年度の出生総数の合計が表示されます。
  7. チャートタイプを指定する場合は、show 都道府県 出生総数 as treemap といったような検索を行うと、都道府県別出生総数をツリーマップで表示してくれます。
  8. 追加で年度でフィルタして表示したい場合には、show 出生総数 都道府県 as treemap filter 2018 (年度)　とすることで、年度が2018のデータでフィルタして表示してくれます。

図：自動的に最適なグラフが選択されて表示される

図：チャートタイプを指定して表示

関連リンク

カテゴリー
Microsoft365、業務テクニック
タグ

コメントを残す

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

日本語が含まれない投稿は無視されますのでご注意ください。（スパム対策）

Microsoft365

前の記事

Power BIでデータ解析を視覚化する　作成編
2021年6月1日
Electron

次の記事

ElectronでGoogleカレンダーの日本の祝日を取得する
2021年7月2日