Power Query 第5回目です。今回はフォルダーから複数ファイルの結合の処理際にする作成される「ヘルパークエリ」について勉強してみます。
※「フォルダーから」のデータ取得で「予期せぬエラー」が発生したため中断していました。
実はこの時に、原因を探るために「フォルダーから」についての情報をいろいろ調べていたんですが「ヘルパークエリ」のことがすごく気になっていたんです。
以下、独学で勉強しながらですので、温かい目で参考としてみていただければ幸いです。
というわけで「ヘルパークエリ」というものについて勉強してみたいと思います。どんな結果になるかわかりませんが、「ヘルパークエリ」が増殖しないで済む方法が見つかったらうれしいんですけどね(^^;
その後復習のために、いくつもの「フォルダーから」取り込んでみたんですが、その都度「ヘルパークエリ」ができるので「クエリと接続」が大変なことになっちゃいました。 是非よろしくお願いしますm(__)m
【この記事でわかることは】
・「フォルダーから」複数ファイルの結合で作成される「ヘルパークエリ」について
・「ヘルパークエリ」なしで同じことができるようにしてみる
前回記事のおさらいは、下のカードをクリックすれば開きます(^^ゞ
・前回の内容は「フォルダーから」複数のCSVファイルを取得・結合方法でした。
記事作成にあたっては次の Microsoft office のサポートの情報などを参照しています。
・Power Query for Excel のヘルプ
・Excel の Power Query について
なお、この記事の画像は Microsoft 365 で取得した画像です。Excel2016 のリボンやメニューなどバージョンによって違うようなので、適宜読み替をお願いします(^^)/
「ヘルパー クエリ」の中身を確認してみましょう
・前回「フォルダから」CSVファイルを結合した際に作られた「ヘルパークエリ」です。
「ヘルパー クエリ」グループに4つのサポートクエリが作成されています。
・「サンプル ファイル」
・「パラメーター１（サンプルファイル）」
・「ファイルの変換」
・「サンプル ファイルの変換」
「その他のクエリ」に
・「CSV」は、各ファイルが結合され出来上がったクエリです。
・「ヘルパー クエリ」内のサポートクエリは Power Query が、取得するファイルの中身を解析して作られたものです。サンプルファイルのデータをもとに文字コード、列数、ヘッダー等の各項目をもとに、各ファイルの中身を展開して結合クエリを作成しているようです。
・以下、「ヘルパー クエリ」内のそれぞれのサポートクエリについて確認し、最後に結合後に出来上がった「CSV」クエリと比較検証してみたいと思います。
「ヘルパー クエリ」グループ内のサポートクエリ
「サンプル ファイル」
・各クエリは、ダブルクリック又は右クリック「編集」で Power Query エディター が開き、編集できるようになります。
・下の画像は、適用したステップの「ソース」を表示しています。
・「ナビゲーション」ステップの画像がこちらです。
・[クエリ]→[詳細エディター]で 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 エディター の編集画像です。
・「パラメーターの管理」をクリックすると次のダイアログが表示されます。
・[詳細エディター]はこのようになっています。
#"サンプル ファイル" meta [IsParameterQuery=true, BinaryIdentifier=#"サンプル ファイル", Type="Binary",
IsParameterQueryRequired=true]・パラメーターは、再利用できる値を簡単に格納・管理する手段ということです。
・パラメーターの使用で、クエリの値に応じてクエリの出力を動的に変更できる柔軟性が得られるようになります。
・構文値メタ [レコード] を使用して、メタデータ レコードが #”サンプル ファイル”に関連付けられています。
・データソース関数（ここでは「Folder.Files」）の引数値が変更されても対応できるようになるということのようです。
「ファイルの変換」
・編集で表示される Power Query エディター の画像がこちらです。
・詳細エディターを表示しようと思ったところ、こんなメッセージが表示されました。
・表示させた詳細エディターの画像がこちらです。
let
ソース = (パラメーター1) => let
ソース = Csv.Document(パラメーター1,[Delimiter=",", Columns=6, Encoding=932,
QuoteStyle=QuoteStyle.None]),
昇格されたヘッダー数 = Table.PromoteHeaders(ソース, [PromoteAllScalars=true])
in
昇格されたヘッダー数
in
ソース・「ファイルの変換」クエリは 関数クエリですね。
・”パラメーター1″ クエリを使用して、”サンプル ファイル” クエリへの入力として各ファイル (またはバイナリ) を指定します。
・このクエリでは、ファイルの内容を含む[コンテンツ]列も作成され、構造化された[レコード]列が自動的に展開され、結果に列データが追加されます。”ファイルの変換”クエリと”サンプル ファイル”クエリがリンクされ、”サンプル ファイル”クエリへの変更が”ファイルの変換”クエリに反映されます。
「サンプル ファイルの変換」
・下の画像は１行目がヘッダーに適用された画像です。
let
ソース = (パラメーター1) => let
ソース = Csv.Document(パラメーター1,[Delimiter=",", Columns=6, Encoding=932,
QuoteStyle=QuoteStyle.None]),
昇格されたヘッダー数 = Table.PromoteHeaders(ソース, [PromoteAllScalars=true])
in
昇格されたヘッダー数
in
ソース・これは、一番目のファイルをサンプルとして変換しているクエリですね。
「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
変更された型・これがフォルダ内のファイルデータを結合して出来上がったクエリです。
・これだけ残して、ヘルパークエリを削除してしまうとエラーで動きません！
・ヘルパークエリを参照しているところがあるからです。
・ヘルパークエリを参照せずに作ってくれればいいんですけどね。
・結果として、参照するフォルダーが増えれば、その数分のヘルパークエリができてしまうことになるので困りものです。
「フォルダーから」データを結合する動作
・ヘルパークエリを使って「フォルダーから」データを結合する動作をサポートしていることはわかりました。でも基本的な動作を押さえておけば、ヘルパークエリに頼らなくてもできそうな気がするのでチャレンジしてみたいと思います。
・大体こんな感じだと自分なりに理解しました。
・ではヘルパークエリで出来上がったクエリを参考にして実際にやってみたいと思います。
ヘルパークエリ無しでデータ取得してみます
画面操作だけでやってみます
①「ソース」の作成
・[データ]→[データの取得]→[フォルダーから]→フォルダーを指定します→[変換]
・これで目的の①番目「ソース」の部分が出来上がりました。
[閉じて次に読み込む…]→[データのインポート]→[接続の作成のみ]にしておきます。
② カスタム列を追加します
・[列の追加]→[カスタム列]→ダイアログに式を貼り付けます。
・次のように、ソースの部分「パラメータ１」を「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変換」に変更しています。
③ 出来上がったテーブルを展開します
・では、いよいよ列を展開してみます。下の画像の↑部分のアイコンのクリックします。
・右画像のダイアログが表示されますのでプレフィックスを使用しない設定で「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ファイルのサンプルファイルは前回記事のものを使っています！
・是非サンプルを使って実際にファイルの結合を試してみてください。
・今までの記事のサンプルも登録していますのでよろしければお使いください(^^)
まとめと感想など
少し難しくて時間がかかりましたが、なんとかうまくいきましたね！
これで「フォルダから」の取得を沢山設定したとしても、「ヘルパー クエリ」が増殖してしまうことはなくなりそうですね。
次回は、クエリのマージについて勉強しましょう(^^)/
難しかったです(^^;
でも、今回勉強したものを使いまわせばよさそうですね。
しっかり復習しておかないと、
この先ついていけなくなりそうです(^^ゞ
★★★ ブログランキング参加中！ クリックしてね(^^)/ ★★★
【今後の記事について】
今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてる」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m
【検討中の今後の記事内容は・・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思います・・・・・
・今後の記事にご期待ください(^^)/
過去記事のサンプルファイルをダウンロードできます
今回の記事で使用したCSVサンプルファイルをリンク先に登録しています！
過去の記事で使用したサンプルファイルもダウンロードできるようにページを設置していますので、こちら（このリンク先）からご利用ください
【今回わかったことは】
・「ヘルパークエリ」の基本動作について
・「ヘルパークエリ」なしでも同じことができる