本ページには広告が含まれています。

Excel パワークエリ「フォルダーから」ヘルパーなしで結合する

Power Query「フォルダー」からヘルパーなしで結合
2021.07.04

Power Query 第5回目です。今回はフォルダーから複数ファイルの結合の処理際にする作成される「ヘルパークエリ」について勉強してみます。
※「フォルダーから」のデータ取得で「予期せぬエラー」が発生したため中断していました。
実はこの時に、原因を探るために「フォルダーから」についての情報をいろいろ調べていたんですが「ヘルパークエリ」のことがすごく気になっていたんです。
以下、独学で勉強しながらですので、温かい目で参考としてみていただければ幸いです。

くるみこ
くるみこ

というわけで「ヘルパークエリ」というものについて勉強してみたいと思います。どんな結果になるかわかりませんが、「ヘルパークエリ」が増殖しないで済む方法が見つかったらうれしいんですけどね(^^;

その後復習のために、いくつもの「フォルダーから」取り込んでみたんですが、その都度「ヘルパークエリ」ができるので「クエリと接続」が大変なことになっちゃいました。　是非よろしくお願いしますm(__)m

【この記事でわかることは】
・「フォルダーから」複数ファイルの結合で作成される「ヘルパークエリ」について
・「ヘルパークエリ」なしで同じことができるようにしてみる

前回記事のおさらいは、下のカードをクリックすれば開きます(^^ゞ

Excel Power Query フォルダーから複数データ結合
Excel パワークエリでCSVファイルのデータを「フォルダーから」インポート・結合する方法について勉強します。データを取り込む際の注意点やCSVファイルとExcelブックの取り込む際の違いなども解説しています。サンプルCSVデータも登録。
www.kurumico.com

・前回の内容は「フォルダーから」複数のCSVファイルを取得・結合方法でした。

記事作成にあたっては次の Microsoft office のサポートの情報などを参照しています。
・Power Query for Excel のヘルプ
・Excel の Power Query について

なお、この記事の画像は Microsoft 365 で取得した画像です。Excel2016 のリボンやメニューなどバージョンによって違うようなので、適宜読み替をお願いします(^^)/

スポンサーリンク

「ヘルパー クエリ」の中身を確認してみましょう

・前回「フォルダから」CSVファイルを結合した際に作られた「ヘルパークエリ」です。

「クエリと接続」に表示されたヘルパークエリ

「ヘルパー クエリ」グループに4つのサポートクエリが作成されています。

・「サンプル ファイル」

・「パラメーター１（サンプルファイル）」

・「ファイルの変換」

・「サンプル ファイルの変換」

「その他のクエリ」に

・「CSV」は、各ファイルが結合され出来上がったクエリです。

・「ヘルパー クエリ」内のサポートクエリは Power Query が、取得するファイルの中身を解析して作られたものです。サンプルファイルのデータをもとに文字コード、列数、ヘッダー等の各項目をもとに、各ファイルの中身を展開して結合クエリを作成しているようです。

・以下、「ヘルパー クエリ」内のそれぞれのサポートクエリについて確認し、最後に結合後に出来上がった「CSV」クエリと比較検証してみたいと思います。

「ヘルパー クエリ」グループ内のサポートクエリ

「サンプル ファイル」

・各クエリは、ダブルクリック又は右クリック「編集」で Power Query エディター が開き、編集できるようになります。
・下の画像は、適用したステップの「ソース」を表示しています。

適用したステップの「ソース」を表示している画像

・「ナビゲーション」ステップの画像がこちらです。

「ナビゲーション」ステップの画像

・[クエリ]→[詳細エディター]で Power Query M言語のコードを確認できます。

「詳細エディター」で Power Query M言語のコードを確認
let
    ソース = Folder.Files("D:\CSV"),
    ナビゲーション1 = ソース{0}[Content]
in
    ナビゲーション1

・Power Query M 数式言語のリファレンス – PowerQuery M … を参照しながら勉強します。
・let 式 には、計算され、名前を割り当てられた後、in ステートメント に続く後の式で使用される、一連の値がカプセル化されているとのことです。

・ソース = Folder.Files(“D:\CSV”),
　「ソース」変数に「Folder.Files」関数で（フォルダー パス）にあるファイルごとの行を含むテーブルを代入しています。

・ナビゲーション1 = ソース{0}[Content]
　「ソース」の[Content]列{0}最初の行のリンクを「ナビゲーション1」にカプセル化しています。

※多分こんな感じでいいのかなぁ(^^;　よくわからないなぁ

「パラメーター１（サンプルファイル）」

・Power Query エディター の編集画像です。

Power Query エディター の編集画像

・「パラメーターの管理」をクリックすると次のダイアログが表示されます。

「パラメーターの管理」ダイアログ

・[詳細エディター]はこのようになっています。

「詳細エディター」のコード
#"サンプル ファイル" meta [IsParameterQuery=true, BinaryIdentifier=#"サンプル ファイル", Type="Binary",
　　 IsParameterQueryRequired=true]

・パラメーターは、再利用できる値を簡単に格納・管理する手段ということです。
・パラメーターの使用で、クエリの値に応じてクエリの出力を動的に変更できる柔軟性が得られるようになります。
・構文値メタ [レコード] を使用して、メタデータ レコードが #”サンプル ファイル”に関連付けられています。
・データソース関数（ここでは「Folder.Files」）の引数値が変更されても対応できるようになるということのようです。

「ファイルの変換」

・編集で表示される Power Query エディター の画像がこちらです。

編集で表示される Power Query エディター の画像

・詳細エディターを表示しようと思ったところ、こんなメッセージが表示されました。

詳細エディターを表示しようと思ったところ表示されたメッセージ

・表示させた詳細エディターの画像がこちらです。

表示させた詳細エディターの画像
let
    ソース = (パラメーター1) => let
        ソース = Csv.Document(パラメーター1,[Delimiter=",", Columns=6, Encoding=932, 
　　　　　　　　　QuoteStyle=QuoteStyle.None]),
        昇格されたヘッダー数 = Table.PromoteHeaders(ソース, [PromoteAllScalars=true])
    in
        昇格されたヘッダー数
in
    ソース

・「ファイルの変換」クエリは 関数クエリですね。
・”パラメーター1″ クエリを使用して、”サンプル ファイル” クエリへの入力として各ファイル (またはバイナリ) を指定します。
・このクエリでは、ファイルの内容を含む[コンテンツ]列も作成され、構造化された[レコード]列が自動的に展開され、結果に列データが追加されます。”ファイルの変換”クエリと”サンプル ファイル”クエリがリンクされ、”サンプル ファイル”クエリへの変更が”ファイルの変換”クエリに反映されます。

「サンプル ファイルの変換」

PowerQueryエディター「サンプル ファイルの変換」

・下の画像は１行目がヘッダーに適用された画像です。

１行目をヘッダーとして使用した画像
「サンプルファイルの変換」詳細エディター
let
    ソース = (パラメーター1) => let
        ソース = Csv.Document(パラメーター1,[Delimiter=",", Columns=6, Encoding=932, 
　　　　　　　　　QuoteStyle=QuoteStyle.None]),
        昇格されたヘッダー数 = Table.PromoteHeaders(ソース, [PromoteAllScalars=true])
    in
        昇格されたヘッダー数
in
    ソース

・これは、一番目のファイルをサンプルとして変換しているクエリですね。

「CSV」クエリ

「CSV」クエリの画像
「CSV」クエリの詳細エディター画像
let
    ソース = Folder.Files("D:\CSV"),
    #"フィルター選択された非表示の File1" = Table.SelectRows(ソース, each [Attributes]?[Hidden]? <> true),
    カスタム関数の呼び出し1 = Table.AddColumn(#"フィルター選択された非表示の File1",
　　　　　　　　　　　　　　　 "ファイルの変換", each ファイルの変換([Content])),
    #"名前が変更された列 1" = Table.RenameColumns(カスタム関数の呼び出し1, {"Name", "Source.Name"}),
    削除された他の列1 = Table.SelectColumns(#"名前が変更された列 1", {"Source.Name", "ファイルの変換"}),
    展開されたテーブル列1 = Table.ExpandTableColumn(削除された他の列1, "ファイルの変換",
　　　　　　　　　　　　　　 Table.ColumnNames(ファイルの変換(#"サンプル ファイル"))),
    変更された型 = Table.TransformColumnTypes(展開されたテーブル列1,{{"Source.Name", type text},
　　　　　　　　　 {"販売日", type date}, {"エリア", type text}, {"品名", type text},
　　　　　　　　　 {"数量", Int64.Type}, {"金額", Int64.Type}, {"店舗コード", type text}})
in
    変更された型

・これがフォルダ内のファイルデータを結合して出来上がったクエリです。

・これだけ残して、ヘルパークエリを削除してしまうとエラーで動きません！
・ヘルパークエリを参照しているところがあるからです。

・ヘルパークエリを参照せずに作ってくれればいいんですけどね。
・結果として、参照するフォルダーが増えれば、その数分のヘルパークエリができてしまうことになるので困りものです。

「フォルダーから」データを結合する動作

・ヘルパークエリを使って「フォルダーから」データを結合する動作をサポートしていることはわかりました。でも基本的な動作を押さえておけば、ヘルパークエリに頼らなくてもできそうな気がするのでチャレンジしてみたいと思います。

【フォルダから複数ファイルを結合する基本動作】
・基本は①～③の３つです。そのほかは必要に応じて追加します。

① ソース ＝ Folder.Files(“FolderPathアドレス”)
　　これで「フォルダー」内のファイルデータ「バイナリ」を取得します。

② 列を追加して、取得した「バイナリ」データをテーブルに変換して格納します。
　　基本は「区切記号」「列数」「文字コード」を指定して変換します。
　　※「列数」は無指定でも自動取得してくれるようです。

③ 出来上がったテーブルを展開します。

・不要な列の削除を行います。
・ヘッダーの設定（位置や名前など）
・列のデータ型変更

・大体こんな感じだと自分なりに理解しました。
・ではヘルパークエリで出来上がったクエリを参考にして実際にやってみたいと思います。

スポンサーリンク

ヘルパークエリ無しでデータ取得してみます

画面操作だけでやってみます

①「ソース」の作成

・[データ]→[データの取得]→[フォルダーから]→フォルダーを指定します→[変換]
・これで目的の①番目「ソース」の部分が出来上がりました。

「ソース」が作成できた画像

[閉じて次に読み込む…]→[データのインポート]→[接続の作成のみ]にしておきます。

② カスタム列を追加します

・[列の追加]→[カスタム列]→ダイアログに式を貼り付けます。

「カスタム列」を追加

・次のように、ソースの部分「パラメータ１」を「Binaly」データ列の[Content]に変更ます。

Csv.Document(パラメーター1,[Delimiter=”,”, Columns=6, Encoding=932, QuoteStyle=QuoteStyle.None])

　　　　　　　　　↓

Csv.Document([Content],[Delimiter=”,”, Columns=6, Encoding=932, QuoteStyle=QuoteStyle.None])

・結果、出来上がったカスタム列の中を見てみると、ヘッダーがないですね(^^;

列にヘッダーが無いことが確認できる
出来上がったカスタム列

・詳細エディターで確認してみます。

let
    ソース = Folder.Files("D:\CSV"),
    追加されたカスタム = Table.AddColumn(ソース, "カスタム", each Csv.Document([Content],
　　　　　　　　　　　　　[Delimiter=",", Columns=6, Encoding=932, QuoteStyle=QuoteStyle.None]))
in
    追加されたカスタム

・「Table.PromoteHeaders」を追加して次のように書き換えます。

let
    ソース = Folder.Files("D:\CSV"),
    追加されたカスタム = Table.AddColumn(ソース, "CSV変換", 
　　　　　　　　　　　　　each Table.PromoteHeaders(Csv.Document([Content],
　　　　　　　　　　　　　[Delimiter=",", Columns=6, Encoding=932, QuoteStyle=QuoteStyle.None])))
in
    追加されたカスタム

・無事ヘッダーが設定されました。列名も「カスタム」から「CSV変換」に変更しています。

ヘッダーが設定され、列名も「CSV変換」に変更された

③ 出来上がったテーブルを展開します

・では、いよいよ列を展開してみます。下の画像の↑部分のアイコンのクリックします。
・右画像のダイアログが表示されますのでプレフィックスを使用しない設定で「OK」を押します。

テーブルを展開するアイコン
「元の列名をプレフィックスとして使用しない」に設定する

・不要な列はたくさん残っていますが、テーブルが展開されたのが確認できました。

テーブルが展開された画像

・ここまで３ステップのみで「ヘルパークエリ」無しのクエリが作成できました。
・下のコードは、不要列削除と列見出し名変更の２ステップを追加したものです。

let
    ソース = Folder.Files("D:\CSV"),
    追加されたカスタム = Table.AddColumn(ソース, "CSV変換", 
　　　　　　　　　　　　　each Table.PromoteHeaders(Csv.Document([Content],
　　　　　　　　　　　　　[Delimiter=",", Columns=6, Encoding=932, QuoteStyle=QuoteStyle.None]))),
    #"展開された CSV変換" = Table.ExpandTableColumn(追加されたカスタム, "CSV変換", {"販売日", "エリア", "品名", "数量", "金額", "店舗コード"}, {"販売日", "エリア", "品名", "数量", "金額", "店舗コード"}),
    削除された列 = Table.RemoveColumns(#"展開された CSV変換",{"Content", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),
    #"名前が変更された列 " = Table.RenameColumns(削除された列,{{"Name", "FileName"}})
in
    #"名前が変更された列 "

Excelブックの場合の変換コード

・Excelブックの場合は、シートがある点が違います。

【CSVの場合】
Csv.Document([Content],[Delimiter=”,”, Columns=6, Encoding=932, QuoteStyle=QuoteStyle.None])
　　　↓
【Excelの場合】
Excel.Workbook([Content],true){[Item=”Sheet1″,Kind=”Sheet”]}[Data]

・この部分を変更すれば大丈夫なようです。
・実際にやってみた詳細エディターのMコードがこちらです。

let
    ソース = Folder.Files("D:\ExcelBook"),
    追加されたカスタム = Table.AddColumn(ソース, "カスタム", 
　　　　　　　　　　　　　each Excel.Workbook([Content],true){[Item="Sheet1",Kind="Sheet"]}[Data]),
    #"展開された カスタム" = Table.ExpandTableColumn(追加されたカスタム, "カスタム", 
　　　　　　　　　　　　　{"販売日", "エリア", "品名", "数量", "金額", "店舗コード"}, 
　　　　　　　　　　　　　{"販売日", "エリア", "品名", "数量", "金額", "店舗コード"})
in
    #"展開された カスタム"

まとめ（おわりに）

・以上で、「ヘルパークエリ」についての勉強は終了です。
・「ヘルパークエリ」を使わず「フォルダから」データ取得・結合する方法についても試してみました。なんとかうまくいったと思いますが、いかがでしたでしょうか？
・今回は、記事内で使ったCSVファイルのサンプルファイルは前回記事のものを使っています！
・是非サンプルを使って実際にファイルの結合を試してみてください。

・今までの記事のサンプルも登録していますのでよろしければお使いください(^^)

まとめと感想など

くるみこ
くるみこ

少し難しくて時間がかかりましたが、なんとかうまくいきましたね！

これで「フォルダから」の取得を沢山設定したとしても、「ヘルパー クエリ」が増殖してしまうことはなくなりそうですね。
次回は、クエリのマージについて勉強しましょう(^^)/

難しかったです(^^;
でも、今回勉強したものを使いまわせばよさそうですね。
しっかり復習しておかないと、

この先ついていけなくなりそうです(^^ゞ

【今回わかったことは】
・「ヘルパークエリ」の基本動作について
・「ヘルパークエリ」なしでも同じことができる

★★★ ブログランキング参加中！ クリックしてね(^^)/ ★★★

にほんブログ村 IT技術ブログ VBAへ
にほんブログ村

人気ブログランキングアイコン
Visual Basicランキング

【今後の記事について】

今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてる」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m

【検討中の今後の記事内容は・・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思います・・・・・
・今後の記事にご期待ください(^^)/

スポンサーリンク
エックスサーバーバナー広告
スポンサーリンク

過去記事のサンプルファイルをダウンロードできます

今回の記事で使用したCSVサンプルファイルをリンク先に登録しています！

過去の記事で使用したサンプルファイルもダウンロードできるようにページを設置していますので、こちら（このリンク先）からご利用ください

【Excel VBA】サンプルファイル_ダウンロードのページ
【Excel VBA】の記事内で使ったサンプルファイルを（Zipファイルで）ダウンロードできるようにしました。記事内では細かい部分の解説が不十分ですが、実際にファイルをダウンロードして実行することで使えるのかどうか見ていただけたら幸いです。
www.kurumico.com