【Power Query】ヘルパークエリを削除して同じ結果を得るクエリに修正

Power Queryで、ファイルを結合する時に自動作成される「ヘルパークエリ」の動作を理解するために、ヘルパークエリをすべて削除して同じ結果を得るクエリに修正してみました。

内容：

ヘルパークエリを削除する前提知識
事例の題材（ヘルパークエリ）
クエリの修正手順
（参考）ヘルパークエリを理解するためのポイント

ヘルパークエリを削除する前提知識

ヘルパークエリを使用せず、同じ結果を得るクエリは作成可能です。
余分と感じるケースでは、ヘルパークエリを削除することも可能です。
ただし、ヘルパークエリがあると、「ファイルの変換」の関数修正が容易になるため、クエリの大幅な修正時に便利な時があります。
ヘルパークエリの削除を判断する基準は、今後のメンテナンスで関数を使用して詳細なデータ変換の修正をしたいかになります。
（データソースの変更程度の修正であれば、ヘルパークエリは不要なケースが多いと思われます）

事例の題材（ヘルパークエリ）

説明に使用する題材のヘルパークエリは、【Power Query】クエリの依存関係を把握（ヘルパークエリでの事例）で説明したクエリで、ファイル結合時に作成された次の構成です。

上記ヘルパークエリは、次のとおり「②サンプルファイル」、「③ファイルの変換」が「⑤売上サンプルファイル」に影響している。

この事例で、「⑤売上サンプルファイル」は、ファイル結合時のフォルダ名を元に作成されているので、自身の環境の名称に置き換えて確認してください。

クエリの修正手順

今回の事例では、「⑤売上サンプルファイル」の各ステップと他のクエリの関係は次のとおりです

「カスタム関数の呼び出し1」ステップで、「③ファイルの変換」に依存
「展開されたテーブル列1」ステップで、「②サンプルファイルの変換」に依存

（詳細は、前述の【Power Query】クエリの依存関係を把握（ヘルパークエリでの事例）で説明）

以下、上記順序でヘルパークエリの機能を「⑤売上サンプルファイル」に移転し、ヘルパークエリを削除する手順です。

「③ファイルの変換」への依存をなくす

「カスタム関数の呼び出し1」ステップの詳細

まず、「カスタム関数の呼び出し1」ステップの詳細は次のとおり。
各行で「ファイルの変換([Content])」としている。

関数でファイルの変換([Content])としている — 「カスタム関数の呼び出し1」のステップ

この「ファイルの変換」はカスタム関数で、その内容を確認すると次のとおり。

Excel.Workbook(パラメーター1, null, true)

パラメーター1に前述の各[Content]のデータが代入、Excelの内容を取得している。

Excel.Workbook(パラメーター1, null, true)が数式部分 — カスタム関数「ファイルの変換」の内容

「カスタム関数の呼び出し1」ステップの修正

よって、前述の「カスタム関数の呼び出し1」ステップは、カスタム関数「ファイルの変換」を使用せず、数式を直接編集して次のように修正できる。

修正前：

= Table.AddColumn(#"フィルター選択された非表示の File1", "ファイルの変換", each ファイルの変換([Content]))

修正後：

= Table.AddColumn(#"フィルター選択された非表示の File1", "ファイルの変換", each Excel.Workbook([Content], null, true))

数式を前述のとおり修正している — 修正後の「カスタム関数の呼び出し1」のステップ

「②サンプルファイルの変換」への依存をなくす

「展開されたテーブル列1」ステップの詳細

続いて、「展開されたテーブル列1」ステップの詳細は次のとおり。

= Table.ExpandTableColumn(削除された他の列1, "ファイルの変換", Table.ColumnNames(ファイルの変換(#"サンプルファイル")))

この数式で、Table.ExpandTableColumnでTableのデータを展開している。

Table.ColumnNames(ファイルの変換(#"サンプルファイル")は、「サンプルファイル」をカスタム関数「ファイルの変換」で開き、列名を取得している。

よって、Table展開時の列名を別途指定すれば「Table.ColumnNames(ファイルの変換(#"サンプルファイル")」も不要となる。

「展開されたテーブル列1」にかわるステップの挿入

Table展開時の列名を設定する方法はいくつかあるが、今回は簡単な方法として「前のステップの後で再度データ展開」をする。

具体的には、次のとおり前のステップである「削除された他の列1」を選択し、「ファイルの変換」にあるTableについて、データを展開する。

「ファイルの変換」にあるTableを展開 — 「削除された他の列1」ステップでTableを展開

列の選択はすべての列とし、「元の列名をプレフィックスとして使用します」のチェックを外して「OK」を選択する。
（元のサンプルファイルの列名も同様であったため）

列の選択は全ての列とし、元の列名はプレフィックスとして使用しない — 展開する列の設定画面

上記の結果、「ステップの挿入」の確認メッセージが表示されるが「挿入」を選択し、次のとおり「展開されたテーブル列1」と同様の結果が得られている。

参考）数式での違いは、次のとおり。

元：「展開されたテーブル列1」ステップ

= Table.ExpandTableColumn(#"展開されたファイルの変換", "ファイルの変換", Table.ColumnNames(ファイルの変換(#"サンプルファイル")))

代替後：

= Table.ExpandTableColumn(削除された他の列1, "ファイルの変換", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name", "Data", "Item", "Kind", "Hidden"})