ピボット解除は便利なんだけど、それはあくまで見出しが1行の場合に限られる。元の表が一覧表の場合、得てして見出しが複数行にわたる場合もあるだろう。そういう場合、どうすればまともな形に組み替えできるか。
2行とは限らず、3~4行に分かれている場合も考えられる。例えば下のような、見出しが2~5行までの4行にわたっている表で考えてみよう。
こういう場合、確かにピボットテーブルだけでは完結しない。しかしだからといって諦める必要もない。やり方次第では、ちゃんと組み換えできる。
今回の表では、5行目(見出しの最終行)は一意な値になっている。つまりここから下は普通のテーブルと変わらない。こういう場合は、見出しとそれ以外を分けておいて、後から見出しを下のテーブルにマージしてやればいい。
では下準備。まずデータを読み込む必要があるので、セル範囲の B2:I8を名前定義(名前「データ範囲」)して、そこからクエリを接続のみで作成する。
データ範囲Excel.CurrentWorkbook(){[Name="データ範囲"]}[Content]
続いて見出し部分を読み込んで組み替える。
見出しlet ソース = データ範囲, 上位行の保持 = Table.FirstN(ソース, 4), 行列の入れ替え = Table.Transpose(上位行の保持), 上位行の削除 = Table.Skip(行列の入れ替え), 下方向にフィル = Table.FillDown(上位行の削除, {"Column1", "Column2"}), 型の変更 = Table.TransformColumnTypes(下方向にフィル, { {"Column1", Int64.Type}, {"Column2", type text}, {"Column3", Int64.Type}, {"Column4", type text} }) in 型の変更
後は5行目以降を普通にピボット解除して、「見出し」クエリとマージ→展開してやればいい。
メインクエリlet ソース = データ範囲, 上位行の削除 = Table.Skip(ソース, 3), ヘッダーに昇格 = Table.PromoteHeaders( 上位行の削除, [PromoteAllScalars=true] ), 列のピボット解除 = Table.UnpivotOtherColumns( ヘッダーに昇格, {"日付"}, "店舗名", "出荷数" ), 型の変更 = Table.TransformColumnTypes( 列のピボット解除, {{"日付", type date}, {"出荷数", Int64.Type}} ), 見出し列とマージ = Table.NestedJoin( 型の変更, {"店舗名"}, 見出し, {"Column4"}, "見出し" ), 列の並べ替え = Table.ReorderColumns( 見出し列とマージ, {"日付", "見出し", "店舗名", "出荷数"} ), 見出し列の展開 = Table.ExpandTableColumn( 列の並べ替え, "見出し", {"Column1", "Column2", "Column3"}, {"地域コード", "地域名", "店舗コード"} ), 行の並べ替え = Table.Sort(見出し列の展開, { {"日付", Order.Ascending}, {"地域コード", Order.Ascending}, {"店舗コード", Order.Ascending} }) in 行の並べ替え
今回の場合だとこんな感じ。表の状況によってはもっと簡単にも複雑にもなるけど、流れはそんなに変わらないと思う。