こんにちは、あおいです。
最近、NASDAQ指数が12,000~13,000のレンジで落ち着いている傾向にある気がするが、頼むからぶちあがってほしい。含み損デカすぎ。。。



今回は、マトリクス形式の複数ファイルをリスト形式に変換したうえで、１つのテーブルに統合する方法を紹介したいと思います。以下がやりたいことのイメージ図です。

以下のExcelをデータソースとします。
フォルダ内に４つのExcelファイルが格納されており、各ファイルにマトリクス形式の表が存在しています。

フォルダに格納されているExcelブックを抽出して、［データの変換］をクリックします。

しばらくすると、Power Queryエディターが起動します。

［Content］列のファイルの結合ボタンをクリックし、シート名を選択して［OK］をクリックします。

ステップが新たに追加されて、ヘルパークエリも生成されています。

面右側の適用したステップに［変更された型］が表示されていますが、この時点では不要なので両方削除します。

この時点でピボット解除はしません。仮に［その他の列のピボット解除］を実行しても、以下のように訳の分からない状態のテーブルになってしまいます。

では、どうすればいいのか？

ヘルパークエリの中の「サンプルファイルの変換」というクエリを活用します。
実際に「サンプルファイルの変換」を確認してみると、2020年度のExcelブックの中身だけが抽出されています。

一旦、2021,2022,2023年度のExcelブックのことは何も考えなくていいです。
とにかく、「サンプルファイルの変換」で展開されている2020年度のテーブルを綺麗にリスト形式に変換するようにします。


まずは［１行目をヘッダーとして使用］を選択し、適用したステップに［変更された型］が自動的に挿入されますがこの時点では不要なので削除します。

［支店］列を選択した状態で［その他の列のピボット解除］をクリックします。

「サンプルファイルの変換」でテーブルの整形を実行した後で、もう一度「支店・年度別売上額」クエリに戻って確認すると、縦長のリスト形式に変換されています。

フィルターで確認すると、きちんと2020,2021,2022,2023度のExcelブックが全て結合されていますね。

「合計」の値は不要なので、フィルターボタンをクリックして項目のチェックを外せば完了です。

やりたいことは実現できましたが、

「ヘルパークエリの中のサンプルファイルの変換で何が起きた…？？？」

と思いますよね。
 

「サンプルファイルの変換」で実行されたデータ整形の変換は、他のテーブルに対しても同じデータ整形の変換が適用されます。つまり、この「サンプルファイルの変換」のクエリを経由してデータを加工してから、最後に全てのファイルが結合される仕組みになっています。

抜群に便利ですよね。今まで自動生成されるヘルパークエリの存在がよく分からず邪魔だなぁ～と思っていましたが、まさかの超優秀な人でした。ファイルを１つずつピボット解除してから最後にまとめて結合する、なんて作業はもう不要です。


 

今回はPower Queryで複数ファイルを一括で整形して結合する方法を紹介させていただきました。ヘルパークエリは超優秀な人なので、この仕組みは必ず覚えて活用しましょう！