こんにちは、あおいです。 
バックオフィス実務に従事する人にとって、Excel機能の中でも最も重要なのって「Power Query」な気がしますね。 



さて、Power BIでExcelのデータを可視化したいけど、以下のようにクロス集計されたマトリクス形式の表になっていて扱いづらい…なんて経験ありませんか？

このままでは非常に扱いづらいので、以下のようにリスト形式のデータテーブルに変換する必要があります。

でも、どうやってやればいいのか？ 


ご安心ください。Power Queryの「ピボット解除」を使えば、わずか数クリックのマウス操作で、リスト形式のデータテーブルに変換できます。マクロやVBAで小難しいプログラムを組む必要は一切ないです。 



そこで、今回はPower Queryの「ピボット解除」でマトリクス形式のExcel表をリスト形式に変換する方法を紹介したいと思います。 
 

［データを取得］から対象のExcelを選択して、［データの変換］をクリックします。

しばらくすると、Power Queryが起動します。

画面右側の適用したステップに「変更された型」が表示されていますが、この時点で型変換を適用する意味はないので削除します。

［1行目をヘッダーとして使用］を選択して、テーブルの1行目をヘッダーに変換します。 
また、「変更された型」が自動で適用されていますが、この時点で型変換を適用する意味はないので削除します。

ここで「ピボット解除」をしますが、操作方法は主に２つあります。 
 

１つ目は、［選択した列のみをピボット解除］です。

ピボット解除の対象となる年度列を選択して、ピボット解除を実行します。

２つ目は、［その他の列のピボット解除］です。

ピボット解除の対象となる列を選択するのではなく、対象としない列を選択してピボット解除を実行します。

こちら結果はほとんど同じように見えますが、後者の［その他の列のピボット解除］をおすすめします！ 
理由は、もし前者の場合、元データに2026年,2027年,,,と新しい年度列のデータが追加された場合、その年度列はピボット解除がされないからです。 
数式バーを確認して分かるように、ピボット解除の対象の列を2020~2025年と指定しているのが原因ですね。

［その他の列のピボット解除］では、選択した列以外の全ての列をピボット解除します。 
したがって、元データに2026年,2027年,,,と新しい年度列のデータが追加がされた場合、追加された列もピボット解除されます。 




属性列に合計という値が表示されていますが、こちらは不要なのでフィルターで外しておきます。

最後に、列名とデータ型を適切なものに変更すれば完了です。

今回はPower Queryの「ピボット解除」でマトリクス形式のExcel表をリスト形式に変換する方法を紹介させていただきました。本記事が少しでも読者の皆様のお役に立てれば幸いです。

次回の記事では、ピボット解除の応用編です。こちらの記事も是非ご覧ください。
Power Queryのピボット解除は最強です。さよなら、マクロ。| cloud.config Tech Blog