カスタム関数の使用

異なるクエリまたは値に同じ変換セットを適用する必要がある場合は、必要な回数再利用できる Power Query カスタム関数を作成すると便利です。 カスタムPower Queryは、一連の入力値から 1 つの出力値へのマッピングであり、ネイティブの M 関数と演算子から作成されます。

「Power Query M関数について」に示すように、コードを使用して独自の Power Query カスタム関数を手動で作成することもできますが、Power Query ユーザー インターフェイスを使用すると、カスタム関数の作成と管理のプロセスを高速化、簡略化、および強化する機能が提供されます。 この記事では、ユーザー インターフェイスを使用してPower Queryこのエクスペリエンスと、それを最も多く利用する方法について説明します。

重要

この記事では、ユーザー インターフェイスでアクセスできる一般的な変換Power Queryを使用して、カスタム関数を作成するPower Query説明します。 ここでは、カスタム関数を作成する主要な概念と、この記事で参照されている特定の変換の詳細については、Power Query ドキュメントのその他の記事へのリンクを参照してください。

テーブル参照からカスタム関数を作成する

この例に従って、この記事で使用されているサンプル ファイルを次のダウンロード リンク から ダウンロードします。 わかりやすくするために、この記事ではフォルダー コネクタを使用します。 フォルダー コネクタの詳細については、「フォルダー」を 参照してください。 この例の目的は、すべてのファイルのすべてのデータを 1 つのテーブルに結合する前に、そのフォルダー内のすべてのファイルに適用できるカスタム関数を作成することです。

まず、フォルダー コネクタ エクスペリエンスを使用して、ファイルが格納されているフォルダーに移動し、[データの変換] または [編集]を****選択します。 これにより、新しいエクスペリエンスPower Queryされます。 [コンテンツ] フィールドから 選択したバイナリ 値を右クリック し、 [新しいクエリとして追加] オプションを選択 します。 この例では、リストの最初のファイルに対して選択が行われたことが確認できます。これは、4 月の 4 月 2019.csv。

サンプル ファイルになるファイルを選択します。

このオプションを使用すると、そのファイルへの直接のナビゲーション ステップを含む新しいクエリが実質的にバイナリとして作成されます。この新しいクエリの名前は、選択したファイルのファイル パスになります。 このクエリの名前をサンプル ファイル に変更します

サンプル ファイル クエリ。

ファイル パラメーター という名前の新 しいパラメーター を作成します。 次の 図に示 すように、サンプル ファイル クエリを現在 の値として使用します。

ファイル パラメーター。

注意

パラメーターの作成方法と管理方法を理解するには、パラメーターに関する記事を参照することをお勧Power Query。

カスタム関数は、任意のパラメーター型を使用して作成できます。 パラメーターとしてバイナリを持つカスタム関数の要件はありません。

パラメーターを指定せずにカスタム関数を作成できます。 これは、関数が呼び出されている環境から入力を推論できるシナリオでよく見られます。 たとえば、環境の現在の日付と時刻を受け取り、それらの値から特定のテキスト文字列を作成する関数です。

[クエリ] ウィンドウで [ファイル パラメーター ] を右クリック します。 [参照] オプションを 選択します。

ファイル パラメーターを参照します。

新しく作成したクエリの名前を File Parameter (2) から Transform Sample file に 変更します

クエリ変換サンプル ファイルの名前が変更されました。

この新しい変換サンプル ファイル クエリを右クリックし、 [関数の 作成] オプションを選択 します。

変換サンプル ファイルから関数を作成します。

この操作により、変換サンプル ファイル クエリにリンクされる新しい 関数が実質的に作成 されます。 [サンプル ファイルの変換] クエリ 加えた変更は、カスタム関数に自動的にレプリケートされます。 この新しい関数の作成時に、関数名 として Transform ファイル使用します

Transform ファイルの関数ウィンドウを作成します。

関数を作成すると、関数の名前を持つ新しいグループが自動的に作成されます。 この新しいグループには次の情報が含まれる。

  • 変換サンプル ファイル クエリで参照 されたパラメーター すべて。
  • 変換 サンプル ファイル クエリ。一般にサンプル クエリ と呼ばれる。
  • 新しく作成した関数 (この場合は、ファイルの変換)。

関数グループ。

サンプル クエリへの変換の適用

新しい関数を作成したら、変換サンプル ファイル という名前の クエリを選択します。 このクエリは Transform ファイル関数 にリンク されたので、このクエリに加えた変更は関数に反映されます。 これは、関数にリンクされたサンプル クエリの概念と呼ばれるものになります。

このクエリに対して行う必要がある最初の変換は、バイナリを解釈する変換です。 プレビュー ウィンドウからバイナリを右クリックし 、[CSV] オプションを選択してバイナリを CSV ファイルとして解釈できます。

バイナリを CSV として解釈します。

フォルダー内のすべての CSV ファイルの形式は同じです。 これらはすべて、最初の上位 4 行にまたがるヘッダーを持っています。 列ヘッダーは 5 行目にあり、次の図に示すように、データは行 6 から下方向に開始されます。

CSV データのサンプル。

変換サンプル ファイルに適用する必要がある変換ステップの次の セットは次 のとおりです。

  1. 上位 4 行を削除する —このアクションにより、ファイルのヘッダー セクションの一部と見なされる行が削除されます。

    変換サンプル ファイルから上位行を削除します。

    注意

    行を削除する方法、または行の位置でテーブルをフィルター処理する方法の詳細については、「行の位置でフィルター処理 する」を参照してください

  2. ヘッダーの昇格 —これで、最終的なテーブルのヘッダーがテーブルの最初の行に表示されます。 次の図に示すように、それらを昇格できます。

    最初の行をヘッダーとして使用します。

Power Query既定では、各列のデータ型を自動的に検出する列ヘッダーを昇格した後、新しい [変更された型] ステップが自動的に追加されます。 [ サンプル ファイルの変換] クエリは次の画像のように表示されます。

注意

ヘッダーを昇格および降格する方法の詳細については、「列ヘッダーの昇格または降格 」を参照してください

最終的な変換のサンプル クエリ。

注意事項

Transform ファイル関数は 、サンプル ファイルの変換クエリで実行 される手順に依存 します。 ただし、Transform ファイル関数のコードを手動で変更すると、次の警告が表示されます。The definition of the function 'Transform file' is updated whenever query 'Transform Sample file' is updated. However, updates will stop if you directly modify function 'Transform file'.

カスタム関数を新しい列として呼び出す

これでカスタム関数が作成され、すべての変換手順が組み込まれたので、元のクエリに戻り、フォルダーからファイルの一覧を取得できます。 リボンの [ 列の追加] タブで、[全般] グループ から [カスタム関数の 呼び出し] を選択 します。 [カスタム関数 の呼び出し] ウィンドウで、[ 新しい列名] として「 出力テーブル 」と入力します。 [関数クエリ] ドロップダウンから 、関数の 名前 [ファイルの変換 ] を選択 します。 ドロップダウン メニューから関数を選択すると、関数のパラメーターが表示され、この関数の引数として使用する列をテーブルから選択できます。 [ファイル パラメーター ] に渡す値/引数として [コンテンツ] 列 を選択します

[列の追加] メニューの [カスタム関数の呼び出し] ボタン。

[OK] を選択 すると、出力テーブルという名前の新しい が作成されます。 次の図 に示 すように、この列のセルには Table 値が含まれます。 わかりやすくするために、 [名前] と [出力テーブル] を除く、この テーブルからすべての列****を削除します

カスタム関数が呼び出されました。

注意

テーブルから列を選択または削除する方法の詳細については、「列の選択または削除 」を参照してください

関数の引数として Content 列の値を使用して、テーブルのすべての単一行に関数が適用されています。 探している図形にデータが変換されたので、次の図に示すように、[出力テーブル] 列を展開できます。展開された列にはプレフィックスを使用できません。

[出力テーブル] 列を展開します。

[名前] 列または [日付] 列の値を確認することで、フォルダー内のすべてのファイルのデータ を****確認 できます。 この場合は、Date 列の値を確認できます。各ファイルには、特定の年の 1 か月間のデータだけが含まれているので、 複数のファイルが表示される場合は、複数のファイルのデータを 1 つのテーブルに正常に結合したという意味です。

最後のテーブルに、すべてのファイルのデータが含まれているか検証します。

注意

これまでに読んだのは、ファイルの結合エクスペリエンス中に行われるプロセスと基本的には同じですが、手動で行います。

また、「ファイルの結合の概要」と「CSVファイルの結合」に関する記事を参照して、Power Query でのファイルの結合エクスペリエンスとカスタム関数が果たす役割についてさらに理解することをお勧めします。

既存のカスタム関数に新しいパラメーターを追加する

Imagineの上に新しい要件がある場合は、 新しい要件では、ファイルを組み合わせる前に、その中のデータをフィルター処理して、Country が 「Country」 と等しい行のみを取得する必要があります。

この要件を満たすには、テキスト データ型を使用して Market という名前の新しいパラメーターを作成します。 [Current Value]/(現在の値)、[The]/(政府)の値を 入力します

新しいパラメーター。

この新しいパラメーターを使用して、[ サンプル ファイルの変換] クエリを選択し、Market パラメーターの値を使用して Country フィールドを フィルター処理 します。

新しい Market パラメーターを使用して Country 列をフィルター処理します。

注意

値で列をフィルター処理する方法の詳細については、「値をフィルター処理する」 を参照してください

この新しいステップをクエリに適用すると 、Transform ファイル関数が自動的に更新されます。変換サンプル ファイルで使用される 2 つのパラメーターに基づいて、2 つのパラメーター が必要 になります。

関数が 2 つのパラメーターで更新されました。

ただし 、CSV ファイル クエリ の横に警告記号があります。 関数が更新されたので、2 つのパラメーターが必要です。 したがって、関数を呼び出すステップでは、呼び出されたカスタム関数ステップ中に Transform ファイル関数に渡された引数が 1 つだけなので、エラー 値が返 されます。

関数の更新後のエラー。

エラーを修正するには、[適用されたステップ] で [呼び出されたカスタム関数] をダブルクリックして[カスタム関数の呼び出し ] ウィンドウを開 きます。 Market パラメーター に、値 「In-1」 を手動で 入力します

呼び出されたカスタム関数引数の更新。

これで、クエリをチェックして 、Country しい行だけが CSV ファイル クエリの最終的な結果セットに表示されるのを検証できます。

更新された引数の後の最終的な出力テーブル。

再利用可能なロジックからカスタム関数を作成する

同じ変換セットを必要とする複数のクエリまたは値がある場合は、再利用可能なロジックとして機能するカスタム関数を作成できます。 後で、このカスタム関数を、選択したクエリまたは値に対して呼び出すことができます。 このカスタム関数を使用すると、時間を節約し、一連の変換を中央の場所で管理し、いつでも変更できます。

たとえば、複数のコードをテキスト文字列として持ち、それらの値をデコードする関数を作成するクエリを想像してください。

コードの一覧。

最初に、例として使用する値を持つパラメーターを作成します。 この場合は 、PTY-CM1090-LAX という値になります

サンプル パラメーターのコード値。

そのパラメーターから、必要な変換を適用する新しいクエリを作成します。 この場合、コード PTY-CM1090-LAX を複数のコンポーネントに分割します。

  • Origin = PTY
  • 宛先 = LAX
  • Airline = CM
  • FlightID = 1090

サンプル変換クエリ。

その一連の変換の M コードを次に示します。

let
    Source = code,
    SplitValues = Text.Split( Source, "-"),
    CreateRow = [Origin= SplitValues{0}, Destination= SplitValues{2}, Airline=Text.Start( SplitValues{1},2), FlightID= Text.End( SplitValues{1}, Text.Length( SplitValues{1} ) - 2) ],
    RowToTable = Table.FromRecords( {  CreateRow } ),
    #"Changed Type" = Table.TransformColumnTypes(RowToTable,{{"Origin", type text}, {"Destination", type text}, {"Airline", type text}, {"FlightID", type text}})
in
    #"Changed Type"

注意

M 数式言語の詳細Power Query M 数式言語Power Query 参照してください。

その後、クエリを右クリックして [関数の作成] を選択することで、そのクエリを 関数に変換できます。 最後に、次の図に示すように、任意のクエリまたは値にカスタム関数を呼び出します。

カスタム関数の呼び出し。

さらにいくつかの変換を行った後、目的の出力に到達し、カスタム関数からのこのような変換のロジックを利用したのが分かっています。

カスタム関数を呼び出した後の最終的な出力クエリ。