タイトルは勢いです。でも、分析に適した状態に整形することはとても大事なことです。
Excel ワークシートにテストの結果の記録しなさい。科目は ひとまず "国語", "算数", "理科" です。テストは何回か実施され回数は未定です。という指示があったら、多くの場合こうなる。点数を入力したり経過を見ていくときに十分な役割を果たしますので、これはこれでよいのだけど。
| 回数\科目名 | 国語 | 算数 | 理科 |
|---|---|---|---|
| 1回 | |||
| 2回 | |||
| 3回 | |||
| n回 | ... | ... | ... |
Power BI でこのデータをグラフやチャートにしようとするとき、これではちょっと困ることがある。どんなことが起きてしまうのかを試してみようというのが主な流れ。
先にまとめ
データソース上で列見出しに使用されている 科目名は、テストの種類であって "科目" というカテゴリーに含まれる値ということ。今回の例でいうと、ピボットの解除という作業が必要です。
| 回数 | 科目 | 点数 |
|---|---|---|
| 1回 | 国語 | |
| 1回 | 算数 | |
| 1回 | 理科 | |
| 2回 | 国語 | |
| 2回 | 算数 | |
| 2回 | 理科 | |
| n回 | 国語 | |
| n回 | 算数 | |
| n回 | 理科 |
**「普段から使っている Excel のデータも簡単に可視化できるんです。」と言ってしまうことがままあるのだけど、本当は「普段から使っている Excel のデータも(データの準備など必要なことを少し適用すれば、とても)簡単に可視化できるんです。」**ということです。
試してみる
データの準備
データソースのExcel テーブルをインポートし、
- そのままの "よこ"
- ピボットの解除した "たて"
ふたつのクエリを用意した。
let
ソース = 成績,
変更された型 = Table.TransformColumnTypes(
ソース,
{
{"回", type text}
, {"国語", Int64.Type}
, {"算数", Int64.Type}
, {"理科", Int64.Type}
}
)
in
変更された型
let
ソース = 成績,
ピボット解除された他の列 = Table.UnpivotOtherColumns(
ソース,
{"回"}, "科目", "点数"
),
変更された型 = Table.TransformColumnTypes(
ピボット解除された他の列,
{
{"回", type text}
, {"科目", type text}
, {"点数", Int64.Type}
}
)
in
変更された型
ロードされたテーブル
ひとまずグラフは同じ(ような)
いずれのテーブルであっても同じようなグラフを作成することはできる。グラフ自体の見た目は似せていくことは可能だが、凡例を使えるか使えないが大きな違い。
データを追加
テストの回数を重ねデータが増えた場合どうなるのか。単純に行が増えただけなのでここまでは問題ない。
テストの平均点を計算してみる
平均点を計算するメジャーを作成したが、ここから大きな違いが。
平均点(たて) = AVERAGE('たて'[点数])
に対し、
平均点(よこ) =
var num = IF(HASONEVALUE('よこ'[国語]), VALUES('よこ'[国語]))
+ IF(HASONEVALUE('よこ'[理科]), VALUES('よこ'[理科]))
+ IF(HASONEVALUE('よこ'[算数]), VALUES('よこ'[算数]))
var deno = IF(NOT(ISBLANK(VALUES('よこ'[国語]))),1)
+ IF(NOT(ISBLANK(VALUES('よこ'[理科]))),1)
+ IF(NOT(ISBLANK(VALUES('よこ'[算数]))),1)
return num / deno
式の内容はともかく、それよりも注目すべきなのは各科目の点数を表す列の"列名"をそれぞれ式に含めなければならないこと。嫌な予感しかしません。
社会のテスト結果を追加
平均点(たて) = AVERAGE('たて'[点数])
はこのままでよい。グラフの凡例とか値や軸などの設定もそのままでよい。らくちんです。対し、
平均点(よこ) =
var num = IF(HASONEVALUE('よこ'[国語]), VALUES('よこ'[国語]))
+ IF(HASONEVALUE('よこ'[理科]), VALUES('よこ'[理科]))
+ IF(HASONEVALUE('よこ'[算数]), VALUES('よこ'[算数]))
+ IF(HASONEVALUE('よこ'[社会]), VALUES('よこ'[社会]))
var deno = IF(NOT(ISBLANK(VALUES('よこ'[国語]))),1)
+ IF(NOT(ISBLANK(VALUES('よこ'[理科]))),1)
+ IF(NOT(ISBLANK(VALUES('よこ'[算数]))),1)
+ IF(NOT(ISBLANK(VALUES('よこ'[社会]))),1)
return num / deno
というメジャーの定義を更新することになる。そして、凡例が使えないので 値フィールドに[社会]列を追加しなければならない。もう面倒です。
データの更新だけにとどまらず、レポート上の重要な定義（ビジュアルの設定やメジャーの定義など）をデータに合わせて逐一更新しなければならないことになる。もう十分に手間が掛かりすぎる内容なので避けて通りましょうねということです。