パラメーター クエリを作成する (Power Query)

パラメーター クエリは、SQL または Microsoft Query での使用に精通している可能性があります。 ただし、Power Queryパラメーターには主な違いがあります。

  • パラメーターは、任意のクエリ ステップで使用できます。 パラメーターは、データ フィルターとして機能するだけでなく、ファイル パスやサーバー名などの指定にも使用できます。
  • パラメーターは入力を求められません。 代わりに、Power Queryを使用して値をすばやく変更できます。 Excel のセルに値を格納して取得することもできます。
  • パラメーターは単純なパラメーター クエリに保存されますが、使用されるデータ クエリとは別です。 作成したら、必要に応じてクエリにパラメーターを追加できます。

メモ他の方法でパラメーター クエリを作成する場合は、「Microsoft Query でパラメーター クエリを作成する」を参照してください。

パラメーターを作成する

パラメーターを使用すると、クエリ内の値を自動的に変更し、値を変更するたびにクエリを編集しないようにすることができます。 パラメーター値を変更するだけです。 パラメーターを作成すると、Excel から直接簡単に変更できる特殊なパラメーター クエリに保存されます。

  1. [データ>Get Data>その他のソース>Launch Power Query エディターを選択します。

  2. Power Query エディターで、[ホーム>管理パラメーター] > [新しいパラメーター] を選択します。

  3. [ パラメーターの管理 ] ダイアログ ボックスで、[ 新規] を選択します。

  4. 必要に応じて、次の値を設定します。

    名前 これはパラメーターの関数を反映する必要がありますが、できるだけ短くしてください。
    説明 これには、ユーザーがパラメーターを正しく使用するのに役立つ詳細を含めることができます。
    必須 次のいずれかの操作を行います。

    任意の値 パラメーター クエリでは、任意のデータ型の任意の値を入力できます。

    値の一覧 値を特定のリストに制限するには、小さいグリッドに値を入力します。 下の [既定値] と [ 現在の値 ] も選択する必要があります。

    クエリ リスト クエリを選択します。これは、コンマで区切られ、中かっこで囲まれた List 構造化列に似ています。

    たとえば、Issue 状態フィールドには、{"New"、"Ongoing"、"Closed"} の 3 つの値を指定できます。 リスト クエリを事前に作成するには、詳細エディターを開いて ([ホーム>詳細エディター] を選択)、コード テンプレートを削除し、クエリ リスト形式で値の一覧を入力し、[完了] を選択します。

    パラメーターの作成が完了すると、リスト クエリがパラメーター値に表示されます。
    型 パラメーターのデータ型を指定します。
    提案された値 必要に応じて、値の一覧を追加するか、クエリを指定して入力候補を提供します。
    既定値 これは、[推奨値] が [値の一覧] に設定され、既定のリスト アイテムを指定する場合にのみ表示されます。 この場合は、既定値を選択する必要があります。
    現在の値 パラメーターを使用する場所によっては、これが空白の場合、クエリで結果が返されない場合があります。 [必須] が選択されている場合、現在の値を空にすることはできません。

  5. パラメーターを作成するには、[ OK] を選択します。

パラメーターを使用してデータ ソースを変更する

データ ソースの場所に対する変更を管理し、更新エラーを防ぐ方法を次に示します。 たとえば、同様のスキーマとデータ ソースを想定して、データ ソースを簡単に変更し、データ更新エラーを防ぐのに役立つパラメーターを作成します。 サーバー、データベース、フォルダー、ファイル名、または場所が変更されることがあります。 場合によっては、データベース マネージャーがサーバーをスワップアウトしたり、CSV ファイルを毎月削除したり、別のフォルダーに移動したり、開発/テスト/運用環境を簡単に切り替える必要があります。

手順 1: パラメーター クエリを作成する

次の例では、フォルダー C:\DataFilesCSV1 のインポート操作 ([データの選択]>、[データの取得>From Files>From Folder)] を使用してインポートする CSV ファイルがいくつかあります。 ただし、C:\DataFilesCSV2 というファイルを削除する場所として、別のフォルダーが使用される場合があります。 クエリのパラメーターは、別のフォルダーの代替値として使用できます。

  1. [ ホーム>管理パラメーター>新しいパラメーター] を選択します。

  2. [ パラメーターの管理 ] ダイアログ ボックスに次の情報を入力します。

    名前 CSVFileDrop
    説明 別のファイルのドロップ場所
    必須 ○
    型 テキスト
    提案された値 任意の値
    現在の値 C:\DataFilesCSV1

  3. [OK] を選択します。

手順 2: パラメーターをデータ クエリに追加する

  1. フォルダー名をパラメーターとして設定するには、[ クエリ設定] の [ クエリステップ] で [ ソース] を選択し、[ 設定の編集] を選択します。
  2. [ファイル パス] オプションが [パラメーター] に設定されていることを確認し、ドロップダウン リストから作成したパラメーターを選択します。
  3. [OK] を選択します。

手順 3: パラメーター値を更新する

フォルダーの場所が変更されたので、単にパラメーター クエリを更新できます。

  1. [ Data>Connections & Queries>Queries ] タブを選択し、パラメーター クエリを右クリックし、[編集] を選択 します。
  2. [ 現在の値 ] ボックスに新しい場所 ( C:\DataFilesCSV2 など) を入力します。
  3. [ ホーム>閉じる & 読み込み] を選択します。
  4. 結果を確認するには、新しいデータをデータ ソースに追加し、更新されたパラメーター (Select Data>Refresh All) を使用してデータ クエリを更新します。

パラメーターを使用してデータをフィルター処理する

クエリを編集したり、同じクエリのコピーを少し異なったりすることなく、クエリのフィルターを簡単に変更して異なる結果を取得したい場合があります。 この例では、日付を変更して、データ フィルターを簡単に変更します。

  1. クエリを開くには、Power Query エディターから読み込まれたクエリを探し、データ内のセルを選択し、[クエリ>Edit] を選択します。 詳細については、「 Excel でクエリを作成、読み込み、または編集する」を参照してください。

  2. 任意の列ヘッダーでフィルター矢印を選択してデータをフィルター処理し、[日付/時刻フィルター>After などのフィルター コマンドを選択します。 [ 行のフィルター] ダイアログ ボックスが表示されます。

    [フィルター] ダイアログ ボックスにパラメーターを入力する

  3. [ 値 ] ボックスの左側にあるボタンを選択し、次のいずれかの操作を行います。

    • 既存のパラメーターを使用するには、[ パラメーター] を選択し、右側に表示される一覧から目的のパラメーターを選択します。
    • 新しいパラメーターを使用するには、[ 新しいパラメーター] を選択し、パラメーターを作成します。

  4. [現在の値] ボックスに新しい日付を入力し、[ホーム] >[閉じる] & [読み込み] を選択します。

  5. 結果を確認するには、新しいデータをデータ ソースに追加し、更新されたパラメーター (Select Data>Refresh All) を使用してデータ クエリを更新します。 たとえば、フィルター値を別の日付に変更して、新しい結果を表示します。

  6. [ 現在の値 ] ボックスに新しい日付を入力します。

  7. [ ホーム>閉じる & 読み込み] を選択します。

  8. 結果を確認するには、新しいデータをデータ ソースに追加し、更新されたパラメーター (Select Data>Refresh All) を使用してデータ クエリを更新します。

セル値を使用してデータをフィルター処理する

この例では、クエリ パラメーターの値がブック内のセルから読み取られます。 パラメーター クエリを変更する必要はありません。セル値を更新するだけです。 たとえば、最初の文字で列をフィルター処理しますが、値を A から Z の任意の文字に簡単に変更できます。

  1. フィルター処理するクエリが読み込まれるブックのワークシートで、ヘッダーと値の 2 つのセルを含む Excel テーブルを作成します。

    MyFilter
    G

  2. Excel テーブル内のセルを選択し、[データ>データの取得>テーブル/範囲から] を選択します。Power Query エディターが表示されます。

  3. 右側の [クエリ設定] ウィンドウの [名前] ボックスで、クエリ名を FilterCellValue など、よりわかりやすいものに変更します。

  4. テーブル自体ではなくテーブルに値を渡すには、[データ プレビュー] で値を右クリックし、[ ドリルダウン] を選択します。
    数式が に変更されていることに注意してください。 = #"Changed Type"{0}[MyFilter]
    手順 10 で Excel Table をフィルターとして使用する場合、Power Queryは Table 値をフィルター条件として参照します。 Excel テーブルへの直接参照によってエラーが発生します。

  5. [ ホーム>Close & Load>Close & Load to] を選択します。 手順 12 で使用する "FilterCellValue" という名前のクエリ パラメーターが作成されました。

  6. [ データのインポート ] ダイアログ ボックスで、[ 接続の作成のみ] を選択し、[ OK] を選択します。

  7. フィルター処理するクエリを FilterCellValue テーブル内の値で開きます。このテーブルは、以前にPower Query エディターから読み込まれ、データ内のセルを選択し、[クエリ>Edit] を選択します。 詳細については、「 Excel でクエリを作成、読み込み、または編集する」を参照してください。

  8. 任意の列ヘッダーでフィルター矢印を選択してデータをフィルター処理し、 テキスト フィルター>Begins With などのフィルター コマンドを選択します。 [ 行のフィルター] ダイアログ ボックスが表示されます。

  9. [ 値 ] ボックスに任意の値 ("G" など) を入力し、[ OK] を選択します。 この場合、値は、次の手順で入力する FilterCellValue テーブル内の値の一時的なプレースホルダーです。

  10. 数式バーの右側にある矢印を選択すると、数式全体が表示されます。 数式のフィルター条件の例を次に示します。

    = Table.SelectRows(#"Changed Type", each Text.StartsWith([Name], "G"))

  11. フィルターの値を選択します。 数式で [G] を選択します。

  12. M Intellisense を使用して、作成した FilterCellValue テーブルの最初の数文字を入力し、表示される一覧から選択します。

  13. [ ホーム>Close>Close & Load] を選択します。

結果

クエリでは、作成した Excel テーブルの値を使用してクエリ結果をフィルター処理できるようになりました。 新しい値を使用するには、手順 1 で元の Excel テーブルのセルの内容を編集し、"G" を "V" に変更して、クエリを更新します。

パラメーター クエリの使用を制御する

パラメーター クエリを許可するかどうかを制御できます。

  1. Power Query エディターで、[ファイル]、[オプション]、[設定]>>[Query のオプション>Power Query エディター] を選択します。
  2. 左側のウィンドウの [グローバル] で、[Power Query エディター] を選択します。
  3. 右側のウィンドウの [ パラメーター] で、[ データ ソースと変換ダイアログで常にパラメーター化を許可する] を選択またはオフにします。

関連項目

Power Query for Excel のヘルプ

クエリ パラメーターの使用 (docs.com)