ピボット解除応用：多階層クロス集計表のデータ化
パワークエリのテクニックで人気の高いピボット解除ですが、今回は上に多階層乗ったクロス集計表の解体＝データ化がテーマです。
ヘッダー部分が１階層（一行）であった場合はすんなりとピボット解除できるのですが、複数階層あった場合はひと工夫が必要です。
Before
※ 上から年月、予実区分、PL科目分類が３階層ならんでいます。
After
いつものようにテーブルの形に変換します。
参考資料
こちらのテクニックは『M Is for Data Monkey: The Excel Pro's Definitive Guide to Power Query』で紹介されていたものです。
この本はパワークエリの入門書として最適な本で、私がPower Queryの素晴らしさを知った本です。Power Queryの基本を学びたい方はぜひご購入ください。
M Is for Data Monkey: The Excel Pro's Definitive Guide to Power Query
- 作者:Puls, Ken,Escobar, Miguel
- 発売日: 2015/12/01
- メディア: ペーパーバック
実践
多階層クロス集計表をPower Query Editor に読み込む
まず、多階層クロス集計表の上にカーソルを置きます。
そのままデータ→データの取得と変換→テーブルまたは範囲からをクリックします。
テーブルの作成ダイアログボックスでは、先頭行をテーブルの見出しとして使用するにチェックを入れないでOKを押します。
Power Query Editor が開きます。
先頭行をテーブルの見出しとして使用するにチェックを入れなかったので列の名前はそれぞれ列１、２…です。
一時的に縦と横を入れ替える（転置）
次に変換メニューの入れ替えをクリックします。
そうすると、テーブルの中身が転置され、縦と横が入れ替わります。
列のマージで３階層を１列に結合する
まずは空白データ（null）を埋めるために、Ctlrキーを押しながら、列１、列２をクリックします。
次に変換メニューのフィルをクリックし、下を選択します。
これで、空白データ（null）が埋まりました。
今度は列をマージするために、Ctlrキーを押しながら、Column1、Column2、Column3 をクリックします。
次に変換メニューの中の列のマージをクリックします。
区切り記号の選択には、これら3つのデータに絶対に存在しない値を選んでください。
今回はカスタム→ _ （アンダースコア）を選びましたが、データに存在しない値なら何でも構いません。
これで３つの列が１つの列にマージされました。だいぶ近づいてきた感じがします。
ふたたび縦と横を入れ替えて元に戻す
次に縦横を元に戻すため、変換メニューの入れ替えをクリックします。
これで縦横が元に戻りましたが、今回は上にあった三階層が結合されて一階層になっていることに注意してください。
そして、その一階層の中身は「_（アンダースコア）」でマージされています。
ピボット解除
ピボット解除をするためには、縦に属性として並べるための列名＝ヘッダー行を用意する必要があります。
変換メニューの一行目をヘッダーとして使用するをクリックします。
これで先ほど１階層に結合した情報が列名になり、ピボット解除をする準備が整いました。
Ctrlキーを押しながら、会社名__、氏名__をクリックして選択します。
変換メニューの列のピボット解除→その他の列のピボット解除を選んでください。
※ 毎回データを読み込む時に横に並ぶ列の件数に増減がある場合は、必ず残る列（今回は、会社名__、氏名__）を選択して、その他の列のピボット解除をすると増減があっても対応可能です。
これで先ほど選択されなかった列名が属性として縦に並びました。
（私はいつもそれまでの長い準備段階を経てピボット解除を行うとき、ヒーローが必殺技でトドメを刺すような気持になります。）
１つに結合した３階層を分割して元に戻す
これで最後の仕上げです。属性列を選択します。
変換メニューで列の分割→区切り記号による分割を選択します。
区切り記号には列のマージで入力した「_（アンダースコア）」を入力します。
また、分割が区切り記号の出現ごとになっていることに注意して、OKを押します。
これで３階層が分割され、データ化されました！
最後に列名を整えて完成です。
閉じて読み込むを実行し、テーブルに読み込んで出来上がりです。
