以前の記事で、Formsの回答結果をPower Automateで自動的にSharePointリストに格納する方法を解説しました。
今回は、Power AutomateでSharePointリストに格納したデータをPower BIで取り込み、データ変換する方法を解説します。
- Forms複数回答データをPower BIで分析するためのテーブル設計
- Forms回答データをPowerQueryエディタでデータ変換する方法
- 特定の回答をした人の、他の質問への回答内容をビジュアル化する方法
YouTube動画で見たいかたは、こちらからどうぞ！
Formsのアンケート結果を全自動で集計するイメージ
Formsのアンケート結果を全自動で集計するイメージ図は以下のようになります。
- Formsにアンケートの投稿をトリガーにして、Power Automateを実行
- Power Automateは、Formsで投稿された情報を取得し、SharePointリストに登録
- Power BIでSharePointリストのデータを取得し、レポートを作成、Power BIサービスに発行し、自動更新設定
以前の記事で、Power Automateで、Formsに投稿された情報を取得し、SharePointリストに登録する方法を解説しました。
今回は、Power BIでSharePointリストのデータを読み込み、以下のような解析可能な形にデータ変換していきます。
Formsの複数回答がある場合のテーブル設計
今回のようにFormsで複数回答データがある場合、テーブル設計を少し工夫する必要があります。
PowerQueryエディタで以下のように、元データを、質問一覧と、質問-回答対応表の２つのテーブルに変換していきます。
質問一覧と、質問-回答対応表は、Q-ID列で1対多のリレーションシップをしています。
Power BIでFormsの複数回答データを変換する方法
SharePointリストからのデータ取得
１．Power BIデスクトップアプリを起動し、ホーム＞データを取得＞詳細をクリックします。
２．SharePointで検索し、SharePoint Onlineリストを選択し、接続をクリックします。
３．接続先のSharePointリスト情報に、以下のデータを入力し、OKをクリックします。
- サイトURL
- SharePointサイトのURL
- 実装
- 2.0を選択
- 詳細オプション＞表示モード
- 規定-SharePointリストの”規定のビュー”に設定されている列を取得しますを選択
実装「2.0」、表示モード「SharePointリストの”規定のビュー”~」を選択すると、取得対象のデータを、現在SharePointリストで表示している列のデータに絞ることができます。
４．取得するSharePointリストを選択し、データの変換をクリックします。
５．Power Queryエディタが開くので、データの変換をしていきます。
2つのテーブルの共通部分をデータ変換
１．複数選択可の回答データは[“回答1”,”回答2″…]のようにデータが格納されているので、「[]（大括弧）」と「”」は削除します。
ホームタブ＞値の置換をクリックします。
２．置換する値に「”」、置換後の値を空にして、OKをクリックします。
３．3つの質問列を選択し、変換タブ＞列のピボット解除＞選択した列のみをピボット解除をクリックします。
「列のピボット解除」をすると、列の見出し部分を「属性」、該当するデータ部分を「値」にしたテーブルに変換できます。
４．「属性」列に質問内容、「値」列に回答データが入ったテーブル構成になりました。
「属性」→「Question」、「値」→「Answer」に列名を変更しておきます。
質問一覧テーブルを作成
１．質問一覧テーブルを作成するため、クエリを右クリック＞複製を選択します。
クエリを複製すると、複製した時点のクエリ（処理のステップ）が複製されます。
複製元のクエリを変更しても、複製されたクエリには反映されません。
２．複製されたクエリ名を「QuestionList」に変更しておきます。
ID列、Answer列は不要なので、Deleteキーで削除します。
３．ホームタブ＞行の削除＞重複の削除をクリックし、質問内容の重複を削除します。
４．列の追加タブ＞インテックス列＞1からを選択し、インデックス列を追加します。
インデックス列を追加すると、一番上のレコードから順に重複のない番号を振れます。
５．インデックス列を「Q-ID」に列名を変更します。
質問-回答対応表テーブルを作成
１．複製元のテーブルに戻り、ホームタブ＞クエリのマージをクリックします。
２．マージするテーブルは「QuestionList」を選び、結合キーは「Question」列を選択します。
結合の種類は「左外部」結合を選択し、OKをクリックします。
３．QuestionListテーブルの展開アイコンをクリックします。
４．「Q-ID」列を選択し、「元の列名をプレフィックスとして使用します」のチェックを外し、OKをクリックします。
５．ID列、Q-ID列は、集計しないデータのため、データ型を「テキスト型」に変換します。
６．「閉じて適用」をクリックし、PowerQueryエディタでの操作を適用します。
これでPowerQueryエディタでの、データの変換は完了です！
リレーションシップ（モデル）
１．モデルタブを開き、作成した2つのテーブルがQ-ID列で一対多のリレーションシップされていればOKです。
※自動でリレーションシップされていない場合、手動（ドラッグ＆ドロップ）で行います。
各テーブルの列名と、データの中身が自動でチェックされ、列名とデータが一致する列で、自動的にリレーションシップをしてくれます。
※自動リレーションシップは設定で解除もできます
レポートの作成個々の質問の回答結果を見たい場合、以下の方法があります。
- スライサーに「Question」データを表示する
- 個々のビジュアルごとにフィルターで「Question」内容を絞る
しかし、このテーブル設計には、以下のような問題があります。
ある質問に回答した（例えばよく使うサービスで、Power Appsと回答した）人が、他の質問でどのような回答をしているかが、ビジュアルで見れない。
現在のテーブル設計では、例えばAnswerのスライサーで「Power Apps」を選択すると、「よく使うサービス」以外の質問の回答は表示されません。
この問題を解決するには、以下のようなモデルに変更する方法があります。
質問-回答対応表を参照し、もう1つ全く同じテーブルを作り、ID列で多対多のリレーションシップをしていきます。
１．データ変換からPowerQueryエディタをサイド開き、質問-回答対応表のクエリを右クリック＞参照を選択します。
これで、閉じて適用をクリックします。
クエリの参照は、参照元のテーブルのクエリを実行した結果できたテーブルを参照します。
参照元の処理のステップが変更されれば、参照先にも反映されます。
２．質問-回答対応表の参照したテーブルと、参照元のテーブルのID列で、多対多のリレーションシップをします。
※クロスフィルターは双方向を選択します
クロスフィルターを双方向に設定すると、テーブル間で双方向にフィルターをかけることができるようになります。
３．スライサーのAnswerデータを、参照したテーブルのAnswerデータに置き換えます。
ビジュアルごとのフィルターで、表示するQuestionを絞ります。
４．そうすると、よく使うサービスで例えばPower BIと回答した人が、他に何のサービスをよく使うのか、また他の質問で何の回答をしているのかが、ビジュアルで見れるようになります。
まとめ
今回の記事では、Formsの複数回答データを、Power BIで分析できるようにデータ変換する方法を解説しました。
複数回答可の質問列が複数ある場合、全ての列をそのまま、列の分割で行方向に展開してしまうと、指数関数的にレコード数が増えてしまうので注意しましょう。
レコード数が多くなるほど、Power BIでの読み込み・更新に時間がかかってしまいます
今回のように、複数回答データの質問が複数列ある場合、質問一覧テーブルと、質問-回答対応表テーブルの2つに分けて、リレーションシップをするとよいでしょう。
テーブル設計の基本的な考え方や、リレーションシップについては、また別の記事で解説していきます。
