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

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

1. [データ] > [他のソース>データの取得] > [Launch Power Query エディター] を選択します。

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

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

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

   名前	これはパラメーターの関数を反映する必要がありますが、できるだけ短くしてください。
   説明	これには、ユーザーがパラメーターを正しく使用するのに役立つ詳細を含めることができます。
   必須	次のいずれかの操作を行います: 任意の値 パラメーター クエリに任意のデータ型の任意の値を入力できます。 値の一覧    値を特定のリストに制限するには、小さいグリッドに値を入力します。 下の [既定値] と [ 現在の値 ] も選択する必要があります。 クエリ リスト クエリを選択します。これは、コンマで区切られ、中かっこで囲まれた List 構造化列に似ています。 たとえば、Issue 状態フィールドには、{"New"、"Ongoing"、"Closed"} の 3 つの値を指定できます。 リスト クエリを事前に作成するには、詳細エディターを開き ([ホーム > 詳細エディター] を選択)、コード テンプレートを削除し、クエリ リスト形式で値の一覧を入力し、[完了] を選択します。 パラメーターの作成が完了すると、リスト クエリがパラメーター値に表示されます。
   型	パラメーターのデータ型を指定します。
   提案された値	必要に応じて、値の一覧を追加するか、クエリを指定して入力候補を提供します。
   既定値	これは、[推奨値] が [値の一覧] に設定され、既定のリスト アイテムを指定する場合にのみ表示されます。 この場合は、既定値を選択する必要があります。
   現在の値	パラメーターを使用する場所によっては、これが空白の場合、クエリで結果が返されない場合があります。 [必須] が選択されている場合、現在の値を空にすることはできません。

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

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

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

次の例では、フォルダー C:\DataFilesCSV1 フォルダーのインポート操作 ([データ の取得]> [ファイルからデータ>を取得 する] >フォルダーから) を使用してインポートする 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. 結果を確認するには、新しいデータをデータ ソースに追加し、更新されたパラメーターを使用してデータ クエリを更新します ([ データ ]> [すべて更新] を選択します)。

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

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

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

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

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

   • 新しいパラメーターを使用するには、[ 新しいパラメーター] を選択し、パラメーターを作成します。

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

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

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

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

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

この例では、クエリ パラメーターの値がブック内のセルから読み取られます。 パラメーター クエリを変更する必要はありません。セル値を更新するだけです。 たとえば、最初の文字で列をフィルター処理しますが、値を 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. [ ホーム ] > [読み込み&閉じる ] > [ 読み込み&閉じる] を選択します。 手順 12 で使用する "FilterCellValue" という名前のクエリ パラメーターが作成されました。

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

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

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

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

10. 数式バーの右側にある矢印を選択すると、数式全体が表示されます。 数式のフィルター条件の例を次に示します
    
    。 = Table.SelectRows(#"Changed Type", each Text.StartsWith([Name], "G"))

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

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

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

結果

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

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

1. Power Query エディターで、[ファイル > オプション] と [設定] > [クエリ オプション] > [Power Query エディター] を選択します。

2. 左側のウィンドウの [グローバル] で、[Power Query エディター] を選択します。

3. 右側のウィンドウの [ パラメーター] で、[ データ ソースと変換ダイアログで常にパラメーター化を許可する] を選択またはオフにします。