【PowerQuery】フォルダからのデータ取得で列名が変動するデータを取込む方法
１．やりたいこと
下図の通り、「月別データ」フォルダに保管されている月別のファイルをPowerQueryで取込みたいのですが、このデータは月によって列の部門が変動します。決まったファイルをまとめて取込む場合は問題ないのですが、毎月実績が出た後にその月のデータをフォルダに保管して常に累計のデータを出すような場合、GUI操作で進めていくと前月までになかった部門が新たにでた時にデータが取り込まれなくなってしまうのです。これを解消する方法を考えてみました。
２．列名が変動するとなぜ取込めなくなるか
通常、フォルダのファイルを展開する場合、下図のように展開する列を選択します。この後OKをクリックしたら展開されるのですが、この操作はあくまでこの時点で対象となっているファイルに含まれる列となります。このままだと例えば新たにフォルダに追加したファイルの列に「G部門」や「H部門」があった場合、これらの列は展開する対象から漏れてしまうことになるのです。
（上記展開した際に生成されるM関数）
３．どうすればよいか
単純に前述のTable.ExpandTableColumn関数の列名の引数を可変となるようにしてあげればよいのではないかと思いました。今回紹介する方法が最適解かどうかはわかりませんが、次項で説明していきます。
４．実践
取込み用のExcelファイルを作成し、メニューバー → データ → データの取得 → ファイルから → フォルダから をクリックします。
フォルダーのダイアログが表示されたら右端の参照から取込み対象のフォルダを選択します。今回はPQテストフォルダの中にある「月別データ」フォルダとします。
フォルダに保管されているファイル一覧が表示されるので、データの変換をクリックします。
PowerQueryエディターが立ち上がります。通常、ここでContent列の見出し右端の矢印ボタンをクリックしてFileの結合を行いますがその場合ヘルパークエリ等が出てきてごちゃごちゃするので、今回はステップの追加から直接コードを書くことにします。
数式バー左のFx（ステップの追加）をクリックします。下図のように適用したステップにカスタム1が追加され、数式バーに前ステップ名が入った状態になります。
数式を次の通り変更します。
【変更前】
= ソース
【変更後】
= Table.AddColumn(ソース, "FileChanges", each Table.PromoteHeaders(Excel.Workbook([Content], null, true) {[Item="Sheet1",Kind="Sheet"]}[Data], [PromoteAllScalars=true]))
FileChanges列が追加されました。ためしにFileChanges列の1行目のTableのところにカーソルを置くと画面下にファイルのプレビューが表示されます。
※ステップ名 カスタム1はFileChanges_Addに変更しています。
ホームタブ → 詳細エディター をクリックします。
詳細エディターが開いたら、まず下図の通り let の下に1行を追記します。
「FolderPath = 」の後は元々の「ソース=Folder.Files( 」の後のフォルダパスと同じものを入れます。その後、元々の「ソース=Folder.Files( 」の後の中身を「FolderPath 」に書き換えます。（関数の記述を簡略化しているだけです）
続けて、3行目のFileChanges_Add の末尾に「 , 」を入力してから次の数式を入力します。
AllFilesColNames = List.Accumulate(
{1..List.Count(FileChanges_Add[Content])-1}, {Table.ColumnNames(FileChanges_Add{[#"Folder Path"=FolderPath&"\",Name=FileChanges_Add[Name]{0}]}[FileChanges])},
(state,current)=> state & {Table.ColumnNames(FileChanges_Add{[#"Folder Path"=FolderPath&"\",Name=FileChanges_Add[Name]{current}]}[FileChanges])}
)
{1..List.Count(FileChanges_Add[Content])-1}, {Table.ColumnNames(FileChanges_Add{[#"Folder Path"=FolderPath&"\",Name=FileChanges_Add[Name]{0}]}[FileChanges])},
(state,current)=> state & {Table.ColumnNames(FileChanges_Add{[#"Folder Path"=FolderPath&"\",Name=FileChanges_Add[Name]{current}]}[FileChanges])}
)
対象フォルダに保管されたすべてのファイルの列名をList化するためにList.Accumulate関数を使いました。以下、簡単に説明します。
第1引数 List： {1..List.Count(FileChanges_Add[Content])-1}
1 で始まり、FileChanges_AddのContent列の数から1を引いた数まで、今回の例だと1,2,3 となります。1番目を表す 0 は次の引数(seed)を出力結果に含める為、ここでは除いています。
第2引数 seed： {Table.ColumnNames(FileChanges_Add{[#"Folder Path"=FolderPath&"\",Name=FileChanges_Add[Name]{0}]}[FileChanges])}
FileChanges_Addの1行目({0}で表現）のテーブルdataからTable.ColumnNamesで列名を抜いています。これが初期値となり、次の第3引数で最初の state となります。
第3引数 accumlator(function)： (state,current)=> state & {Table.ColumnNames(FileChanges_Add{[#"Folder Path"=FolderPath&"\",Name=FileChanges_Add[Name]{current}]}[FileChanges])}
state,current で関数を設定しています。seedの数式と異なるのは、[Name]の後の{ } の中を current としており、ここに第1引数のListが代入され計算されます。出力結果は state&としていますので初期値を含めたすべてのファイルの列名がList化されることになります。
下図の通り、記述し in の後を AllFilesColNames に変えて完了をクリックします。
すべてのファイルの列名が入れ子となったリストの状態となります。
ファイルタブ → テーブルへの変換 をクリックします。
下図の状態でOKをクリックします。
テーブルに変換されました。この時点では各Listが入れ子となったままです。
列見出し右の矢印ボタンから 新しい行に展開する をクリックします。
展開されました。
重複された行を削除するため、ホームタブ → 行の削除 → 重複の削除をクリックします。
重複が削除されすべてのファイルで一意の列名となりました。あとはこれを参照してテーブルを展開できれば目的達成です。
GUI操作で展開はできないので、数式バー左のFx（ステップの追加）をクリックし直接コードを書きます。
数式を次の通り変更します。
【変更前】
= 削除された重複
【変更後】
= Table.ExpandTableColumn(FileChanges_Add, "FileChanges", 削除された重複[Column1], 削除された重複[Column1])
FileChanges_Addのステップを参照して展開するのがポイントです。
きちんと展開されました。
不要な列を削除します。
これで完成です。
年月、科目の列を選択した状態でその他の列のピボット解除をすると
下図のようなデータとなりますのでこれを使った集計等ができるようになります。
この後、新たな列が含まれた別ファイルをフォルダに追加してもクエリの更新でテーブルに反映されるようになります。
以上です。
