前回、関数クエリを使って横展開のcsvファイルを取り込む方法をお届けしました。
今回は、これについてもう少し簡単な方法について書いてみたいと思います。
なお、今回の方法についてはツイッターで教えてもらった方法です。
Yt-olt(@olt_yt)さん、ありがとうございます。
では早速見ていきましょう！
この記事でやりたいこと
前回は結合してしまうと縦に変換できないから、フォルダパスを変数にした関数クエリを作って縦に展開して結合しようという話でした。
今回はフォルダ取得で結合したデータテーブルを使って、横展開のテーブルデータを縦に展開します。
変換のイメージはこんな感じです。
- 結合データを各月でグループ化し、
- 各月グループテーブルに対して行列入替を行い、
- 各月テーブルを展開して整形する。
こんな感じです。
事前準備
ところで、このフォルダ取得を使ってテーブル結合するときに気を付けないといけないことがあります。
今回取得する各月データの列数（＝データ数）が問題で、各月データをそのままフォルダ取得するとこんな問題が生じます。
例えば各月のデータ数がこのようにばらけていた場合で6月データをサンプルファイルとして指定した場合、6月のデータ数を超えるデータ数は取り込まれない（今回の場合7月）という問題が起きます。
じゃあデータが変わる都度列数最大のファイルを指定すればいいじゃないかというとそうでもありません。
定期的に使うことを考えると、列数の変化にかかわらず漏れなく取り込めるようにしたいわけですよね。
この問題の解決策は２つあります。
まずひとつ目。
フォーマットファイルを用意し、想定される最大のデータ数の列を作っておき、これをサンプルファイルに指定します。
こうしておくことで、各月データがサンプルファイルのデータ数（列数）を超えることは無いので、もれなく取り込まれます。
もう一つは、csvファイルを取り込む際に列数を指定する方法です。
この図のように、サンプルファイルの変換クエリの「ソース」ステップに列数を指定する「Column= xx」という引数部分がありますので、ここをデータ数の最大値を入力します。
ただしこの方法も欠点がありまして。
テキストファイルやCSVファイルは列数を指定できるのですが、エクセルファイルの場合列数を指定する項目が見当たらないので、フォーマットファイルを用意した方が運用上安全と思います。
ただし、Excelを取得した場合でも列数を指定できれば話は別ですけどね。
というわけで、今回はフォーマットファイルを用意した場合の方法を紹介します。
操作方法
フォルダから取得
まずはデータタブの「データの取得」より「ファイルから」「フォルダ」を選択します。
そして、前回と違うのはここ。
結合ボタンの「データの結合と変換」を選びます。
次にファイルの結合画面では、サンプルファイルにフォーマットファイルを選択します。
取得が完了するとこのような画面が開きます。
結合データのグループ化
ファイル名を示す「Source.Name」列を選択し、変換タブ「グループ化」を選択します。
列は「Source.Name」となっていることを確認し、列名はそのままでよく操作に「すべての行」を選択しOKします。
これで、ファイル名ごとにグループ化されました。
グループ化したテーブルの行列入替：カスタム列の追加
列の追加タブ「カスタム列」を選択します。
そして、カスタム列の式に以下の式を入力しOKします。（列名は変えたければ変える。）
= Table.Transpose([カウント])
これが行列入替の関数でして、「カウント列（のテーブルごと）を、行列入替する」という意味です。
カウント列を行列入替した「カスタム」列が追加されました。
不要列の削除
ここで必要な列だけにしておきましょう。（左２列がいらない）
カスタム列の展開
ではこのカスタム列を展開しましょう。
するとこんな感じになります。
ここにあるのはフォーマットファイルを読み込んでますので、空欄が続いてますが、下の方に各月のデータが同じように展開されています。
あとはこれを整形して完成です。
展開したテーブルの整形
ここで整形方法の概要だけまとめておきます。
ちなみにこれはほんの１例、整形していく方法は千差万別色々あると思います。
- １行目の削除（行の削除ー上位ー１行指定）
- ヘッダーの昇格（１行目をヘッダーとして使用する）
- データ型を日付／数値に変更
- エラー行を削除
- フィルタでnull列を外す
まぁこんなところでしょうか。
以上の手順でこのようにデータ整形までできました。
あとは閉じて読み込んで終了です。
最後に
いかがだったでしょうか。
前回の記事よりはだいぶ簡単になったのではないでしょうか。
とは言えまだまだステップは多いですよね。
実はここからまだ簡単にすることができます。
次回、もう一段簡単な方法を紹介したいと思いますので、もう少しお付き合いくださいませ。
今回も最後までお読みいただきありがとうございました。
