Power Query へそのゴマ 第１０章 ヘルパークエリ

本章では、Power Queryにおけるヘルパークエリの役割とその効果的な活用方法について詳しく解説します。ヘルパークエリは、複雑なデータ変換や統合プロセスを効率的かつ柔軟に行うための補助的なクエリです。特に、フォルダー内の複数ファイルの処理やデータモジュール化において重要な役割を果たします。具体例を交えながら、ヘルパークエリを活用する方法を学びます。

10.1 ヘルパークエリとは

10.1.1 ヘルパークエリの定義

Power Queryのヘルパークエリは、データ処理の補助的な役割を果たし、複雑なクエリを分割して効率的かつ柔軟に管理するために使用されます。主な用途は、処理ステップの分割、再利用性の向上、一貫性の確保、保守性の向上です。

例えば、フォルダー内の複数ファイルからデータを取得する際、Power Queryは自動的に3種類のヘルパークエリ（ファイル一覧、サンプルファイルの解析、変換ロジック適用）を作成します。これにより、サンプルファイルで定義した変換ロジックをすべてのファイルに適用することが可能です。

また、ヘルパークエリをカスタマイズすることで、列の削除、エラー処理、動的パス設定などの柔軟な対応が可能になります。さらに、カスタム関数を利用することで、複数のデータセットに共通の変換ロジックを適用でき、効率的なデータ処理が実現します。

主な目的

1. データ処理の分割: 各ステップを明確に分けることで、個々の処理を理解しやすくする。
2. 再利用性の向上: 共通のデータ変換ロジックを複数のクエリで共有。
3. 保守性の向上: 個々のヘルパークエリを調整するだけで、全体のクエリを効率的に修正可能。

ヘルパークエリの利点

• 可読性の向上: データ処理の各ステップを視覚的に確認可能。
• 柔軟性の向上: 必要に応じて、特定の処理ロジックを変更しやすい。
• エラー管理の簡略化: エラーの発生箇所を特定しやすくなり、修正が容易。

10.2 フォルダーからのデータ取得とヘルパークエリ

10.2.1 フォルダーからのデータ取得の操作

「フォルダーから」データを取得する機能を使用します。

すると、フォルダー内の複数ファイルを一括して処理できます。この際、次の3種類のヘルパークエリが自動生成されます

フォルダー内にあるファイルの情報がテーブルで取得できます。

Content列名の右側にあるアイコン「ファイルの結合」をクリックすると、全てのファイルを展開し、結合することができます。

クエリ名が表示されているペインに以下のようなヘルパークエリのフォルダが作成され、いくつかのクエリが出来上がります。

10.2.2 ヘルパークエリで作成されたクエリ群

クエリ名のペインに、「ヘルパークエリ」のフォルダが作成され、自動でいくつか作成されています。

種類	名前	概要
バイナリ	サンプル ファイル	フォルダー内から選んだサンプルとして使用するファイル
パラメータ	パラメータ１（サンプル ファイル）	「サンプル ファイルの変換」で使用するパラメータ
クエリ	サンプル ファイルの変換	サンプルファイルを使った変換ロジック
関数	ファイルの変換	「サンプルファイルの変換」を関数に変換

サンプル ファイル

「サンプル ファイル」には、以下のコードが書かれています。

let
    Source = Folder.Files("C:\Data\MonthlySales"),
    Navigation1 = Source{0}[Content]
in
    Navigation1

指定したフォルダーの一覧にある、一行目のContent列の値を示しています。つまり、１番目のファイルのデータをバイナリで持っていることになります。

パラメータ1(サンプル ファイル)

パラメータは、「サンプル ファイル」を指しています。

サンプル ファイルの変換

「サンプル ファイルの変換」は、以下のようなコードが作成されます。

let
    Source = Excel.Workbook(パラメーター1, null, true),
    Sales_Sheet = Source{[Item="Sales",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sales_Sheet, [PromoteAllScalars=true])
in
    #"Promoted Headers"

Excel.Workbook関数がパラメータ１に示されたバイナリを読み込み、シート名Salesのデータを読み込んでいます。その後、１行目をヘッダーに昇格させています。

ファイルの変換

「サンプル ファイルの変換」に埋め込まれているパラメータが引数となった関数です。

let
    ソース = (パラメーター1) => let
        Source = Excel.Workbook(パラメーター1, null, true),
        Sales_Sheet = Source{[Item="Sales",Kind="Sheet"]}[Data],
        #"Promoted Headers" = Table.PromoteHeaders(Sales_Sheet, [PromoteAllScalars=true])
    in
        #"Promoted Headers"
in
    ソース

letが二重になっており、内側は「サンプル ファイルの変換」と同じになっています。

「サンプル ファイル」、「サンプル ファイルの変換」、「ファイルの変換」の関係は、以下のようになっています。

10.2.3 主クエリに作成されたステップ

主クエリには、いくつかのステップが作成されています。

1.最初に、Folder.Fileds関数でフォルダーの情報を取得します。

let
    Source = Folder.Files("C:\Data\MonthlySales"),

2.隠しファイルを除きます

    #"Filtered Hidden Files1" = 
        Table.SelectRows(
            Source, 
            each [Attributes]?[Hidden]? <> true
        ),

3.ヘルパークエリの関数「ファイルの変換」をContent列に適用し、結果を新しい列「ファイルの変換」に入れます。

    #"Invoke Custom Function1" = 
        Table.AddColumn(
            #"Filtered Hidden Files1", 
            "ファイルの変換", 
            each ファイルの変換([Content])
        ),

4.ファイル名が登録されている「Name」列の名前を「Source.Name」に変更します。

    #"Renamed Columns1" = 
        Table.RenameColumns(
            #"Invoke Custom Function1", 
            {"Name", "Source.Name"}
        ),

5.「Source.Name」列と「ファイルの変換」列以外を削除します。

    #"Removed Other Columns1" = 
        Table.SelectColumns(#"Renamed Columns1", 
            {"Source.Name", "ファイルの変換"}
        ),

6.「ファイルの変換」列のテーブルの値を展開します。展開後のテーブルの列名は、「ファイルの変換」関数が「サンプルファイル」から取得したものを使用します。

    #"Expanded Table Column1" = 
        Table.ExpandTableColumn(
            #"Removed Other Columns1", 
            "ファイルの変換", 
            Table.ColumnNames(
                ファイルの変換(#"サンプル ファイル")
            )
        ),

7.列のデータを類推される型に変換します。

    #"Changed Type" = 
        Table.TransformColumnTypes(
            #"Expanded Table Column1",
            {
                {"Source.Name", type text}, 
                {"Date", type datetime}, 
                {"Sales", Int64.Type}
            }
        )

10.3 ヘルパークエリのカスタマイズ

10.3.1 ファイルの選択

主クエリのSourceステップに続けて行のフィルターを追加し、対象のファイルを絞ることができます。

Name列で特定の文字から始まるものや、Extension列で特定の拡張子を選択したり、Date created列で作成日を絞り込むことができます。

10.3.2 サブフォルダーのデータは含まない

Folder.Files関数は、サブフォルダーのファイルも取り込んでしまいます。

サブフォルダーのファイルを含めない場合は、Folder Pathの列名の右のアイコンをクリックして条件を追加します。

または、Folder.Files関数をFolder.Contentsに書き換える方法もあります。

Source = Folder.Contents("C:\Data\MonthlySales"),

10.3.3 異なるフォルダーのデータをまとめる

Folder.Files関数はサブフォルダーまで取り込むので、フォルダーが複数並んでいる状態であれば、上位のフォルダーを選択することで、複数のフォルダーのファイルを取得することができます。

異なるフォルダーを指定して取り込みたい場合、Source ステップに Table.Combine関数を入れ、以下のようにFolder.Files関数を並べます。

    Source = Table.Combine(
        {
            Folder.Files("C:\Data\MonthlySales"),
            Folder.Files("C:\Data\Folder")
        }
    ),

以下のような書き方もあります。

    FolderPaths = 
        {
            "C:\Data\MonthlySales", 
            "C:\Data\Folder"
        },
    Source = 
        Table.Combine(
            List.Transform(
                FolderPaths, 
                each Folder.Files(_)
            )
        )

10.3.4 変換ロジックを作成

「サンプル ファイルの変換」クエリを修正し、データの変換ロジックを追加することができます。

• データのクリーニング
  • 不要な列の削除: 不要な列を選択して右クリックし、「列の削除」を選択します
  • 不要な行の削除: 条件に基づいて行をフィルタリングし、不要な行を削除します
• データの整形
  • 列の分割: 例えば、フルネームの列を「姓」と「名」に分割する場合、列を選択して「列の分割」オプションを使用します
  • 列の結合: 複数の列を一つの列に結合する場合、結合したい列を選択して「列の結合」を使用します
  • 列のピボット解除：ピボットされているデータのピボットを解除します
• データ型の変換
  • データ型の変更: 列のデータ型を変更するには、列を選択して「データ型の変更」オプションを使用します。例えば、文字列を日付型に変換するなどです
• データのフィルタリング
  • 条件付きフィルタリング: 特定の条件に基づいてデータをフィルタリングします。例えば、「売上が1000以上」の行だけを表示する場合などです
• データの集計
  • グループ化: データを特定の列でグループ化し、集計関数（合計、平均、最大値、最小値など）を適用します
  • ピボットテーブルの作成: データをピボットテーブル形式に変換し、集計を行います

作成した内容は、「ファイルの変換」関数に自動的に反映します。

10.3.5 複数の変換処理を行う

複数のデータ形式があり、異なる変換処理の後、１つにまとめたい場合があります。 ファイル名やフォルダ名で変換処理の対象が仕分けできる場合、以下のように行います。

1. 「フォルダ」からのデータ取得を行う
2. ファイル名かフォルダ名でフィルターをかける
3. 変換処理を作成します

この作業を複数回行います。

その際、各変換処理で行った列の数、列の名前、列の型が同じになるようにしてください。

最後に、出来上がったクエリの１つを選択し、「クエリの追加」で「クエリを新規クエリとして追加」を選択します。

作成した主クエリを「追加」し、「OK」ボタンを押せば、全てのクエリが結合されます。

10.4 ヘルパークエリ活用のベストプラクティス

ヘルパークエリを使う上で、以下のことに留意します。

1. 一貫した命名規則:

   • クエリ名やカラム名に一貫性を持たせ、プロジェクト全体で理解しやすい命名を行う

2. コメントの追加:

   • 各ステップにコメントを挿入して、処理内容や意図を明確化

3. エラーハンドリングの実装:

   • データ取得時や変換時にエラーが発生しても処理を継続できるロジックを構築