列のピボットについて書くかどうか迷いましたが、意外と知っておいたほうが良いことが多かったので、概念と落とし穴、そして補正についてまとめました。特にエラーが発生しやすいこともあり、公式Docsには載っていない解決法も一緒に見ていきたいと思います。ファイルだけ欲しい方は一番下よりご自由にダウンロードしてください(今回はExcelファイル)。
列のピボット
「列のピボット」の考え方は分析に適切な状態(=ピボット解除された状態)からピボットした状態(=クロス集計状態)に持っていくことです(下図)。
通常、上図(左)がBIを利用する際に適切なフォーマットですが、Power Queryで右側にする必要がある場合は「列のピボット」を使用します。クロス集計はそもそもBIにおいて適切な書式ではないため、「列のピボット」でこのようなデータの持ち方にする必要がないのでは?という質問もありますが、理由はちゃんとあります。
Attributesが異なる計算指標の数値列で構成されている場合は「列のピボット」を使用したほうが良い
上図から分かりやすい例で言えば、A1、A2、A3はそれぞれ
A1 = 売上数量
A2 = 売上金額
A3 = 売上原価
のようなケースである場合、左側のAttributeにフラグとしての各項目を持たせるより、右側の状態にしたほうがPower BIでDAXメジャーを作りやすいことになります。
具体的には下図の通り。
列のピボットのロジック
「列のピボット」の動作について確認しておきます。やり方は以下の通り。
① Power Queryエディタより、「変換」タブをクリック
② クロス集計にした時、横に展開したい項目を含む列を選択(※忘れやすいのでここは重要)
③ 「列のピボット」を選択
④ 「値列」=ピボットテーブルの「Σ値」に該当する列(下図参照)
⑤ 「値の集計関数」は数値列を選んだ場合、ディフォルト値は「合計」、テキスト列を選んだ場合、ディフォルト値は「カウント」
⑥ アウトプット
Excelピボットで考えると分かりやすいですが、流れ的にまずCountryを列フィールドに配置し、その後ValueをΣ値フィールドへ。Power Queryは自動的にDateが行フィールドに配置されますが、ExcelではDateをドラッグ&ドロップしてあげる必要があります。
一方、上記②と④を変更した場合、⑥アウトプットはどうなるでしょうか?
ご覧の通り、②をDateに変更した場合、横に展開したい項目がDate列内の項目(この場合、1日のみ)となり、④がCountry(テキスト)となっていたため、⑤は既定ではカウント扱い、⑥のValueは自動的に行ヘッダー扱いというアウトプットとなりました。
エラー解説
「列のピボット解除」よりもやや複雑な「列のピボット」ですが、エラーが発生する場合があります。公式Docsにもエラーの発生要因が載っていますが、
Country と Date の組み合わせごとに複数の行が存在する
というのが原因となるようです。
普通に集計やカウントを目的とした「列のピボット」はエラーは発生しませんが、今回の事例で再現した場合、「値の集計関数」を”集計しない”として選択した際、エラーが発生してしまいます(下図)。
つまり、集計を行えば、1セルに1値として格納されるところ、集計を行わなかったため、セルには複数のValueの値が含まれるようになり、結果としてError扱いとなってしまうのです。分かりやすくするため、下図をイメージすると分かりやすいでしょう。当然ながら、このような値の展開(1つのセルに複数の値を格納すること)は通常の「列のピボット」ではエラー扱いとなります。
エラーを発生させず、かつ、上図のように集計せずに表示させたい場合、以下のように少し工夫が必要となります。
- 修正コード
これにより、Index列が追加されるものの、この列のおかげで複数行として下図のように、データは展開できるようになりました。
以下、各ステップ別の解説となります。
- ソース
初期状態ではこのようになっています
- Countryでグループ化
横へ展開したい列(Country)でグループ化をしますが、選択するGroup化される列に対する「操作」は「全ての行」。これにより、各Country内でそれぞれの値をAll列のセルに全て格納することできます
- Index列の追加
ここがミソですが、Index列を追加することで、1セルの中にユニークな数字を誕生させて、後に行う「列のピボット」を可能にしていきます
- Dataの展開
普通に「Data」列の中身を展開します。ポイントはIndex列が追加されたことです
- Allの削除
All列(テーブルがネストされた列)を削除しないで「列のピボット」を実施すると、下記のようなエラーメッセージが出現
- ピボット
最後にもう一度「列のピボット」を行います。上記Index列があるため、Date列は1行に集約されずに3行として扱われるようになったため、エラーの発生を防ぐことができました
本件、実はネット上では英語のソリューションとして紹介されていますが、日本語で紹介しているところがなかったのでまとめることにしました。
最後に、公式Docsでエラーの原因紹介はあるものの、具体的な解決法が示されていないため、DocsチームにFeedbackをしておきました。Feedbackの仕方ですが、日本語ベースのDocsだとできませんので、英語Docsに一度変更して頂き、一番下の「This page」へ飛んでいき、Github経由で英語ベースで報告を行います。