PowerBI

【Forms × Power BI活用】Formsの複数回答データをPower BIで解析する方法

ミムチ
ミムチ
Formsで複数回答があると、1行に複数の回答が入ってるので、Power BIでどうやってデータ変換すればよいのか分かりませんぞ…

パワ実
パワ実
今回は、Formsの複数回答データを、Power BIで分析可能なデータに変換する方法を解説するよ!

以前の記事で、Formsの回答結果をPower Automateで自動的にSharePointリストに格納する方法を解説しました。

【Forms × Power Automate活用】Formsのアンケート結果をSharePointリストに格納して自動集計する方法 この記事では、Formsのアンケート結果を、PowerAutomateを使ってSharePointリストに自動で格納し、Powe...
【Power Automate入門】Formsの回答をSharePointリストに自動格納する方法 ~新しいデザイナーでの実装~ ミムチ Power Automateが「新しいデザイナー」になって、Formsの回答データを自動集計するフローの実装で、「For ea...

今回は、Power AutomateでSharePointリストに格納したデータをPower BIで取り込み、データ変換する方法を解説します。

この記事で分かること

  1. Forms複数回答データをPower BIで分析するためのテーブル設計
  2. Forms回答データをPowerQueryエディタでデータ変換する方法
  3. 特定の回答をした人の、他の質問への回答内容をビジュアル化する方法

YouTube動画で見たいかたは、こちらからどうぞ!

Formsのアンケート結果を全自動で集計するイメージ

Formsのアンケート結果を全自動で集計するイメージ図は以下のようになります。

  1. Formsにアンケートの投稿をトリガーにして、Power Automateを実行
  2. Power Automateは、Formsで投稿された情報を取得し、SharePointリストに登録
  3. Power BIでSharePointリストのデータを取得し、レポートを作成、Power BIサービスに発行し、自動更新設定

以前の記事で、Power Automateで、Formsに投稿された情報を取得し、SharePointリストに登録する方法を解説しました。

SharePointリストへのForms回答自動登録

今回は、Power BIでSharePointリストのデータを読み込み、以下のような解析可能な形にデータ変換していきます。

データ変換後のテーブル(左:質問-回答対応表、右:質問一覧)

Formsの複数回答がある場合のテーブル設計

今回のようにFormsで複数回答データがある場合、テーブル設計を少し工夫する必要があります。

PowerQueryエディタで以下のように、元データを、質問一覧と、質問-回答対応表の2つのテーブルに変換していきます。

Forms複数回答データのテーブル設計

質問一覧と、質問-回答対応表は、Q-ID列で1対多のリレーションシップをしています。

Power BIでFormsの複数回答データを変換する方法

SharePointリストからのデータ取得

1.Power BIデスクトップアプリを起動し、ホーム>データを取得>詳細をクリックします。

Power BIでデータを取得

2.SharePointで検索し、SharePoint Onlineリストを選択し、接続をクリックします。

SharePoint Onlineリストに接続

.接続先のSharePointリスト情報に、以下のデータを入力し、OKをクリックします。

  1. サイトURL
    • SharePointサイトのURL
  2. 実装
    • 2.0を選択
  3. 詳細オプション>表示モード
    • 規定-SharePointリストの”規定のビュー”に設定されている列を取得しますを選択
SharePoint Onlineリストに接続

実装「2.0」、表示モード「SharePointリストの”規定のビュー”~」を選択すると、取得対象のデータを、現在SharePointリストで表示している列のデータに絞ることができます。

4.取得するSharePointリストを選択し、データの変換をクリックします。

SharePointリストの選択

5.Power Queryエディタが開くので、データの変換をしていきます。

2つのテーブルの共通部分をデータ変換

1.複数選択可の回答データは[“回答1”,”回答2″…]のようにデータが格納されているので、「[](大括弧)」と「”」は削除します。

ホームタブ>値の置換をクリックします。

2.置換する値に「”」、置換後の値を空にして、OKをクリックします。

3.3つの質問列を選択し、変換タブ>列のピボット解除>選択した列のみをピボット解除をクリックします。

「列のピボット解除」をすると、列の見出し部分を「属性」、該当するデータ部分を「値」にしたテーブルに変換できます。

4.「属性」列に質問内容、「値」列に回答データが入ったテーブル構成になりました。

「属性」→「Question」、「値」→「Answer」に列名を変更しておきます。

質問一覧テーブルを作成

1.質問一覧テーブルを作成するため、クエリを右クリック>複製を選択します。

クエリを複製すると、複製した時点のクエリ(処理のステップ)が複製されます。

複製元のクエリを変更しても、複製されたクエリには反映されません。

2.複製されたクエリ名を「QuestionList」に変更しておきます。

ID列、Answer列は不要なので、Deleteキーで削除します。

3.ホームタブ>行の削除>重複の削除をクリックし、質問内容の重複を削除します。

4.列の追加タブ>インテックス列>1からを選択し、インデックス列を追加します。

インデックス列を追加すると、一番上のレコードから順に重複のない番号を振れます。

5.インデックス列を「Q-ID」に列名を変更します。

質問-回答対応表テーブルを作成

1.複製元のテーブルに戻り、ホームタブ>クエリのマージをクリックします。

2.マージするテーブルは「QuestionList」を選び、結合キーは「Question」列を選択します。
結合の種類は「左外部」結合を選択し、OKをクリックします。

3.QuestionListテーブルの展開アイコンをクリックします。

4.「Q-ID」列を選択し、「元の列名をプレフィックスとして使用します」のチェックを外し、OKをクリックします。

5.ID列、Q-ID列は、集計しないデータのため、データ型を「テキスト型」に変換します。

6.「閉じて適用」をクリックし、PowerQueryエディタでの操作を適用します

これでPowerQueryエディタでの、データの変換は完了です!

リレーションシップ(モデル)

1.モデルタブを開き、作成した2つのテーブルがQ-ID列で一対多のリレーションシップされていればOKです。

※自動でリレーションシップされていない場合、手動(ドラッグ&ドロップ)で行います。

各テーブルの列名と、データの中身が自動でチェックされ、列名とデータが一致する列で、自動的にリレーションシップをしてくれます。

※自動リレーションシップは設定で解除もできます

レポートの作成個々の質問の回答結果を見たい場合、以下の方法があります。

  1. スライサーに「Question」データを表示する
  2. 個々のビジュアルごとにフィルターで「Question」内容を絞る

しかし、このテーブル設計には、以下のような問題があります。

ある質問に回答した(例えばよく使うサービスで、Power Appsと回答した)人が、他の質問でどのような回答をしているかが、ビジュアルで見れない。

現在のテーブル設計では、例えばAnswerのスライサーで「Power Apps」を選択すると、「よく使うサービス」以外の質問の回答は表示されません。

この問題を解決するには、以下のようなモデルに変更する方法があります。

質問-回答対応表を参照し、もう1つ全く同じテーブルを作り、ID列で多対多のリレーションシップをしていきます。

1.データ変換からPowerQueryエディタをサイド開き、質問-回答対応表のクエリを右クリック>参照を選択します。

これで、閉じて適用をクリックします。

クエリの参照は、参照元のテーブルのクエリを実行した結果できたテーブルを参照します。

参照元の処理のステップが変更されれば、参照先にも反映されます。

2.質問-回答対応表の参照したテーブルと、参照元のテーブルのID列で、多対多のリレーションシップをします。

※クロスフィルターは双方向を選択します

クロスフィルターを双方向に設定すると、テーブル間で双方向にフィルターをかけることができるようになります。

3.スライサーのAnswerデータを、参照したテーブルのAnswerデータに置き換えます。

ビジュアルごとのフィルターで、表示するQuestionを絞ります。

4.そうすると、よく使うサービスで例えばPower BIと回答した人が、他に何のサービスをよく使うのか、また他の質問で何の回答をしているのかが、ビジュアルで見れるようになります。

まとめ

今回の記事では、Formsの複数回答データを、Power BIで分析できるようにデータ変換する方法を解説しました。

複数回答可の質問列が複数ある場合、全ての列をそのまま、列の分割で行方向に展開してしまうと、指数関数的にレコード数が増えてしまうので注意しましょう。

レコード数が多くなるほど、Power BIでの読み込み・更新に時間がかかってしまいます

今回のように、複数回答データの質問が複数列ある場合、質問一覧テーブルと、質問-回答対応表テーブルの2つに分けて、リレーションシップをするとよいでしょう。

ミムチ
ミムチ
なるほど。Power BIではテーブル設計というのがとても重要なのですな。

パワ実
パワ実
その通り!特に複雑なデータの場合は、最初にテーブル設計を考えてからデータの変換をした方が良いと思うよ。

テーブル設計の基本的な考え方や、リレーションシップについては、また別の記事で解説していきます。

ABOUT ME
パワ実
DX推進担当(IT部門) 2021年からPower Platform(Power BI、Power Apps、Power Automate)を勉強中。 Power Platformを使っていく中で、知りえた情報を発信している。 Youtube、Twitterでの情報発信もしています!

Power Platformのご依頼・ご相談について

Power Platformについてのご相談、お仕事のご依頼については、
こちらのお問い合わせページをご確認ください。