Power BI で可能なデータ整形機能のひとつ
はい、今回は [列の分割] についてです。これは使えるようになると地味に強い武器になります。
まずはケーススタディ
昨今いろんな場面で、アンケート、取りますよね。イベントや製品のフィードバック、マーケティングの一種、などなど。そんなアンケートの回答を集計して、分析する。当たり前のことですが、これが地味に面倒ですw
今回のネタはそんなアンケート結果をまとめるときに使えます。
例えばこんなアンケート
とある勉強会的なイベントがあったとします。セミナーでも結構です。最初にアンケートに入力してもらいます。
可能であれば、イベント中に集計を終えて、最後のまとめセッションで使いたい。また後に参加者の属性をまとめ、報告にも使いたいし、データとして保存もしたい。こんな前提です。
以下はアンケート例です。（今回は Microsoft Forms を使用しています）
よくある質問項目ですが、何が困るって、そう、この 複数選択可能 ってやつです。
通常、回答者ひとりにつき、1レコード（＝1行）になり、質問項目ひとつにつき、1列を使います。つまり、この画像のように5問ある場合、質問項目の列は5列になり、複数選択可能な質問はひとつのセルに何らかの記号で連結されて、入力されます。
そう、これがやっかいなのです。
こういうことです。
このように一つのセルに複数の回答が何らかの文字で連結されると、理論上すべての組み合わせのデータができる可能性があり、それはすべて異なる値になってしまいます。これを集計するのはまともにやるととっても面倒で、とてもイベント中にできることではありません。
でも、Power BI の [列の分割] を使うと、これがすんごく簡単に分けられて、集計できます。
具体的内容はここから
今回もExcelファイルを用意しました。これをダウンロードしてください。
アンケート.xlsx
Power BI Desktop で読み込み
Power BI Desktop を開いて、Excel アイコン ⇒ ダウンロードした Excel ファイルを選択
[テーブル1] にチェックを入れて、[データの変換] をクリック
そうすると Power Query エディターが開きます。赤枠の列が複数選択の質問項目です。これをどうにかしたいですね。
列の分割をする前に Key を決める
データから Key となる項目を探してください。Key とは一意の値で、値を指定したらレコードを 1 行に特定することができるものです。今回の場合だと ID か メールアドレス ですね。ただメールアドレスは回答が 1 人 1 回という前提があれば、ということになります。どちらでもよいですが、今回は ID を Key にします。
Key を決めたら Key と質問項目のみのクエリ(テーブル)を作る
何を言ってるのかわからないと思うので、まずは騙されたと思って、以下の手順をやってみてください。
[テーブル1] を右クリック - [参照] をクリック
[テーブル1 (2)] ができました
※参照とはその名の通り「参照」なので [テーブル1] の最後の状態を参照していることになります。従って、もし [テーブル1] に何か変更を加えると、 [テーブル1 (2)] もその変更の影響を受けます。影響を受けたくない場合は 「参照」 ではなく 「複製」 を選んでください。「複製」 は文字通りコピーで、クエリをイチから別モノとして実行します。
[テーブル1 (2)] の [ID] を選択した後、Ctrl を押しながら [あなたの立場は？] を選択します。それから [あなたの立場は？] を右クリックし、[他の列の削除] をクリック
[ID] と [あなたの位置は？] のみになりました。
2行目の複数の質問が入っているセルを選択し、画面下部に表示された値を全選択して、メモ帳などに張り付けます。
値と値の間の 区切り記号 に使えるモノを確認します。
今回の場合は 半角セミコロン (;) です。
ここまで確認ができたら、いよいよ列を分割します。
[あなたの位置は？] を選択して [列の分割] - [区切り記号による分割] をクリック
[区切り記号] は セミコロン が選択されています。これは値を見て、たぶんこれかな？って勝手に推測してくれます。
選択可能なモノは全部で 6 種類 ＋ --カスタム-- です。--カスタム-- は任意に入力することができます。
全角の記号や2文字以上の文字列を指定する場合は、--カスタム-- を選んでください。
[分割] は
- 一番左の区切り記号 ⇒ 値の中で指定した区切り記号の左から1個目のところでのみ分割される
- 一番右の区切り記号 ⇒ 値の中で指定した区切り記号の右から1個目のところでのみ分割される
- 区切り記号の出現ごと ⇒ 値の中で指定した区切り記号出現するたびに分割される
の3種類です
そしてここで大事なのは [詳細設定オプション] です。これは必ず開いてください。
デフォルトだとこの状態です。
- 分割の方向：列
- 分割後の列数：3
- 引用符文字："
分割の方向 が一番大事です。列 ということはここで OK を押すと、列が増えます。区切り記号で文字列を分割して、その値の数の分だけ列に分けてくれます。そして [分割後の列数] がなぜ 3 となっているか？というと、値をすべてみて、最大３つの値に分けられると判断しているからです。
ということで、とりあえずこのまま [OK] を押してみましょう。
はい、この通りです。
元の列名に .数字 という文字列を付けて、列を増やしています。[あなたの立場は？.3] ができていることを不思議に思うかもしれませんが、元の値を見ると、最後の値にも セミコロン(;) が付いているので、空文字が値として入っています。そして3つ目の値がない1行目と3行目は null になっています。
はい、今回はこれは目的の形ではないので、いったん元に戻します。
画面右端の [適したステップ] の [Changed Type]（日本語だと [型の変更] となっています）の左の × をクリックしてください。
次に [区切り記号による列の分割] のステップの右側の歯車マークをクリックすると [区切り記号による列の分割] 画面が開きますので、[分割の方向] を [行] にしてください。
そのまま OK をクリック
そうすると区切り記号によって分割した値を行に展開してくれます。そして [ID] は元の値を引き継いでいます。結果として ID が同じものが増えます。さて、列方向に分割した時と同様、空の値がありますので、[あなたの立場は？] の列名の右にある ▽ をクリックして [空白] のチェックを外します。こうすることで [空白] の行をフィルタリングで消すことができました。
画面右端の名前を「あなたの立場は？」に変えておきましょう
同様の手順で [あなたの役割は？] と [今日期待することは？] はもクエリを作成してください
[閉じて適用] をクリックしてください。
はい、これで Power Query での作業は完了です！
次は [リレーション] です。
モデルペインを開く
画面左端一番下の [モデルペイン] を開きます。この画面はテーブル間のリレーションを定義したり各カラムのプロパティを設定することができます。
ここでやることは2つ。
- 紐付けに使った [ID] を非表示にする
- クロスフィルターの方向を [双方向] にする
Ctrl キーを押しながら、周囲の３つのテーブルの [ID] 列を選択し、画面右側の [プロパティ] - 非表示を [オン] にします
[アンケート] と [あなたの役割は？] テーブルの間の線を右クリックして [プロパティ] をクリック
[リレーションシップの編集] でクロスフィルターの方向を [双方向] にしてください。
これを他の2本の線にも設定してください。
はい、これでできました。
あとは好きなようにビジュアルを作ってください！
たとえばこんな感じです
左上の「あなたの役割は？」を作るには、ドーナツグラフを選択して、
値に [ID]、凡例に [あなたの役割は？] を入れます。
あとはこのグラフをコピーして、凡例を [あなたの立場は？] に変えたら、
左下の「あなたの立場は？」グラフのできあがり。
またグラフをコピーして、凡例を [今日期待することは？] に変えたら、
右下の「今日期待することは？」グラフのできあがり。
という感じです。
まとめ
いかがでしたでしょうか？
たぶん読みながらやると難しいなーとかややこしいなーと思われたかもしれません。
が、慣れると大したことはありません。実際このくらいの内容なら10分くらいで作れます。
そして私が紹介した方法が唯一の方法でもありません。皆さんなりにいろいろと工夫をしてみてください！
何かリクエストがあれば、以下までー🤗
Twitter: https://twitter.com/yugoes1021
Facebook: https://www.facebook.com/yugoes1021
LinkedIn: https://www.linkedin.com/in/yugoes1021/