単一ファイルのみなら簡単なんですが、問い合わせがあったのは複数ファイルをフォルダ取得でまとめて取り込みたいとのこと。
ところがフォルダ取得だと思いのほか難しい。
まず、フォルダ取得する場合最初のデータより後のデータの列数が多い場合、超えた列の文は取り込まれないという問題が生じます。
それをクリアしたとしても、下の図のようなデータ配置になるので、このままピボット解除や行列入替してもうまくいかないのです。
これを行列入替した図がこちら。
そしてピボット解除がこちら。
もちろんどちらも使えません。
ではどうするのか。
今回は、この横展開データをフォルダ取得でまとめてとりこむ方法について見ていきましょう。
Contents
この記事でやりたいこと
この記事でやりたいことは上の図の通りです。
横に展開された売上データたち（左）を、ひとつのデータに結合し縦展開に変換すること（右）です。
今回特に重要なテクニックはこれです。
- フォルダ取得で取得するデータのファイルパス一覧を作る
- 通常のファイル取得で編集して変換クエリを作り、それを取得データのファイルパスを変数とした関数クエリに変換する
- ファイルパステーブルから関数クエリを使って変換した売上データを展開する
事前準備
まず、保存するフォルダを決めておきます。
今回の場合はこんな階層で保存するとよいでしょう。
売上集計.xlsxで、入手データフォルダ内の各月データを取得・結合・変換していくことになります。
ファイルパス一覧の作成
対象データの取得
Excelのデータタブより「データの取得」「ファイルから」「フォルダーから」と選択します。
次に取り込むフォルダーを指定してOKします。
ファイル内のデータが一覧表示されますので、データの変換を選択します。
これで取得は完了です。
不要列の削除（１）
このクエリでは取得データのファイルパスを作成しますので、このように[Name]と[Folder Path]以外の列を削除します。
列のマージ（結合）
結合する順番で列を選択し、列のマージボタンをクリックします。
ここでは追加される結合列を[FilePath]としています。
不要列の削除（２）
これで[Name][Folder Path]の両列が不要ですのでこれを削除し、[FilePath]列のみ残します。
これで取得データのファイルパス一覧ができました。
引き続き、横展開から縦展開に変換するクエリを作っていきます。
取得データの編集と関数クエリへの変換
データの取得
まず、クエリフィールドを開き、右クリックで「新しいクエリ」「ファイル」「テキスト/CSV」と進み、今回取り込むデータ（入手フォルダ内のテキストデータ）を選択します。
ちなみに選択するデータはどれでも構いません。
取込が完了すれば、数式バーを確認し、「Columns=8」を削除します。
削除するとこうなります。
なお、数式バーの表示が無い場合は表示タブより数式バーをチェックして表示してください。
次に適用したステップ欄の「変更された型」を削除します。
列数が変わるデータをパワークエリで扱う場合、列を直接指定するクエリは列の変動が反映されずエラーになりやすいので削除しておくのが無難です。
余談ですが、列が変動するデータはピボット解除等で列固定してから、列を直接指定するステップを加えればいいのです。
行・列入れ替え
変換タブの「入れ替え」を選択します。
するとタテとヨコが入れ替わり、日付が縦に展開されました。
1行目のヘッダーへの昇格
変換タブ「1行目をヘッダーとして使用」を選択し、1行目をタイトル列に昇格します。
これでデータの編集までできました。
ここで作ったクエリを確認しておきましょう。
次に行うのが関数化です。
取得データの行列置換クエリを関数クエリに変換する。
ここで関数化を行います。
どういうことかというと、この取得したデータをタテヨコ入れ替えるクエリのソースに、最初に作ったファイルパス一覧の各ファイルパスを代入できるようにするのです。
これはVBAの変数宣言と似ていますね。
タテヨコ変換クエリを選択した状態でホームタブより「詳細エディター」を開きます。
次に、変更箇所を確認しましょう。
変数宣言文を追加するとともに、このソースのファイルパス部分を変更します。
let直上に下記の文を入れ変数宣言し、ソース部分を変数に変えるだけですね。
( FilePath as text ) =>
これがパワークエリでの変数宣言の型です。
するとクエリフィールドの表示も変わります。
このように、fxと表示され関数に変換されたことが分かります。
次から、ファイルパス一覧から各月の売上データを展開していきます。
各月の売上データ展開データの整形
カスタム関数の呼び出し
まず、ファイルパス一覧のクエリ（ここでは「入手データ」クエリ）を選択します。
列の追加タブから「カスタム関数の呼び出し」を選択します。
新しい列名を入力し、関数クエリに先ほど作成した関数クエリを選択し、変数FilePathにはFilePath列を選択します。
このように、ファイルパスの横に関数によって呼び出されたテーブルが紐づけられました。
呼び出されたテーブルの展開
テーブル列タイトル横の展開ボタンから展開します。
展開列を確認し、不要な場合はチェックを外してOKします。
このように各月売上データのテーブルを、行列入替した状態で展開できました。
不要列の削除
ここまで出来れば、FilePath列は不要なので削除します。
データ型の変換
このままのデータ型では集計したり加工ができませんので、データ型をそれぞれ適切な形に変換します。
列タイトル右のデータ型ボタンより変換します。
ここでは、1列目を日付、2列目～3列目を整数にしています。
列タイトルの変更
最後に列のタイトルを適宜変更して完成です。
例えば下記のように列タイトルをタイトル変更します。
最後にエクセルに読込む、あるいは接続（データモデルに追加）で終了です。
終わりに
以上、 横展開されたデータをフォルダ取得でまとめて取り込み、縦に展開する方法をお届けしました。
関数化はいろんな場面で使える方法ですので、ぜひマスターしてください。
最後までお読みいただきありがとうございました。
