【簡単】シートのセルの値からPowerQueryの設定値を操作する方法。PowerQueryエディタを開かずにパラメータを変更する。

2020年8月5日

当ブログはアフィリエイト広告を利用しており、商品リンクを通じて収益を得る場合があります。

URLをコピーしました！

PowerQueryエディタを開くことなく、条件値（パラメータ）を変更する方法についてご紹介します。パラメータの作成は複数方法がありますが、初めての人にも分かりやすく使いやすいものをご紹介します。とても便利ですので、ぜひ活用してみてください。

PowerQuery内の設定値をワークシートから変更する

Power Queryは変換ステップをコードとして自動的に記録してくれています。

この時、コードの中には設定値も直接書き込まれます。

たとえば、列をフィルターして 100より大きい値を選択した場合、100 という条件が書き込まれます。またCSV ファイルをインポートした場合、ファイルのパス（フォルダ名）はコード内に書き込まれます。

ステップ（コード）内に値が直接書き込まれることを「ハードコード」といいます。

100という値またはこのファイルパスを変更する場合は、どうしたらいいでしょうか。もちろん、毎回クエリを編集すればできますね。

または、パラメータを設定することでワークシートから値を操作することもできます。

PowerQueryエディタに慣れていないユーザーにはエディタを操作するのはハードルが高いですね。ワークシートから値だけを変更できるようにしておくことで、ユーザーにとって分かりやすいツールを作ることができます。ワークシートから操作する方が時間の節約になるというメリットもあります。

ここでは、最も使いやすい「セルの値をパラメータとして使用する方法」を紹介します。この方法を使うとセルの値を変更することで、クエリの結果を変更できるようになります。

なごみ

簡単で便利な方法ですので、ぜひ活用してください。

サンプルファイルをダウンロード

良ろしければ一緒に操作してみてください。サンプルデータは私が作成した5列1000行のCSVファイルです。

こちらから元データのCSVファイルをダウンロードできます。パソコン内のフォルダに保存してフォルダをメモしておいてください。

パラメータファイル作成用サンプルデータ

1 ファイル 35.17 KB

なごみ

まずはベースとなるクエリを作成していきましょう。

抽出元データのクエリを作成

最初の手順では、まず通常どおりクエリを作成します。

ここでは、販売日が2020/01/05以降2020/01/31までの大阪府の販売データを抽出します。

前述のように、すべての手順はハードコード（ステップに直接書込み）されます。どこに書き込まれたかは後でご説明します。

元データとなるテーブル内の任意のセルを選択し、リボンから [データ] – > [データの取得] – > [ファイルから]– > [テキストまたはCSVから]をクリックします。

フォルダの選択画面が開きます。CSVを保存したフォルダからファイルを選択して、右下の[インポート]クリックします。

確認画面が開いたら[読み込み]をクリックします。

ワークシートにデータが読み込まれました。

なごみ

次に[販売日]フィールドの型の変換（確認）を行いましょう。

日付列

[データの取得]から[PowerQueryエディターの起動]をクリックします。

販売日ヘッダーの横にあるアイコンが[日付]アイコンになっていることを確認しましょう。（なっていない場合は、メニューから[日付]を選択します。）

[販売日]ヘッダーのフィルターから[指定の値の間]をクリックします。

開始日と終了日を設定します。ここでは2020/01/05と2020/01/31を入力しましょう。

ＯＫをクリックします。

[都道府県] 列

次に[都道府県] 列で、フィルターアイコンをクリックしすべて選択をクリックして全選択を解除して、[大阪府]のみが選択されていることを確認します。

[ホーム ] – > [閉じて読み込む] をクリックします。

テーブルは次のようになります。

元データから、2020/01/05-2020/01/31かつ大阪府の販売データテーブルを作成しました。しかし、2020年2月に京都府に条件変更したい場合はどうでしょうか? エディターを開けば変更は可能ですが、ここでパラメータを使います。

なごみ

それでは販売日と都道府県を動的に変更するためのパラメータを作成してみましょう。

動的とは
状況に応じて変化したり、状況に合わせて選択できたりする柔軟性を持っていることを意味します。

パラメータの作成

簡単な説明として、パラメータは通常のクエリであり、値自体をドリルダウンして接続としてロードします。

一つの値を持つ 2 つのテーブルを作成します。[日付]テーブルと[販売者]テーブルとします。

新しいワークシートに以下のように入力します。

B3をクリックしてCTRL+ｔキーを押します。テーブルの作成が表示されたら、ＯＫをクリックします。

リボンの[テーブルデザイン]からテーブル名に開始日と入力してテーブルの名前をつけます。

同様に終了日、都道府県もテーブルに変換しましょう。

開始日テーブルを選択して、リボンのデータから[テーブルまたは範囲から]をクリックします。

PowerQueryエディターが開きテーブルが取り込まれました。左側の＞をクリックしてクエリ名を確認しましょう。

開始日というクエリが作成されています。

型が日付時刻型になっていますので、日付型に変更しておきましょう。

データ型は非常に重要です。細心の注意を払ってください。抽出元のクエリの [販売日] 列は日付型です。抽出条件であるこの列も日付型にしておく必要があります。

確認メッセージが表示されたら[現在のものを置換]をクリックしましょう。

リボンのホームから[閉じて読み込む]->[閉じて次に読み込む]をクリックします。

接続の作成のみを選んでＯＫをクリックします。

この例の場合、既にワークシートにテーブルが存在するため、2重にワークシートに取り込まれてしまわないように接続の作成のみを選択しています。もしテーブルに取り込んでしまった場合は、あとからできた方のテーブルがあるワークシートを削除すれば、自動的に接続専用になります。

右側のクエリと接続ペインに開始日接続専用。と表示されました。

同様に終了日もクエリ作成しておきましょう。

終了日の型の変換も忘れずにしましょう。

テキストパラメータの作成

次に都道府県のパラメータを作成します。

[都道府県]テーブルのセルを選択し、[データ] – > [テーブル/範囲から] をクリックしてクエリを作成します。（既に取り込み済みの場合はクエリと接続ペインから右クリックで編集でエディター開くことができます。）

都道府県クエリの値（京都府）を右クリックし、メニューから[ドリルダウン] を選択します。

数式バーには = 変更された型{0}[都道府県]　と表示されました。

これは都道府県クエリから1番目のデータを取り出すことを意味しています。（ここでは０が１番目、1が２番目・・を示しています。）

[ファイル] -> [閉じて読み込む] をクリックして読み込みます。

[データのインポート] ウィンドウで [接続の作成のみ] をクリックし、[OK]をクリックします。

[クエリと接続] メニューには、 [都道府県]というクエリ名が表示されました。クエリ名のアイコンがABCに変わったことからテキストパラメータに変わったことが分かります。

日付パラメーターの作成

さて、[開始日]パラメータについて、同じ手順をもう一度実行してみましょう。上で作成したテキストパラメータと比較して、追加のステップを 1 つ行う必要があります。元のクエリでは、[販売日]列のフィルターが処理で日付の種類が設定されているため、開始日、終了日パラメーターの型も日付型にする必要があります。

PowerQueryエディターを開き[開始日]クエリを選択します。

値部分（2020/02/01）を右クリックして[ドリルダウン] をクリックします。

ちなみに、リボンがテキストツールではなく、日付ツールビューになります。

クエリ名 (開始日) をメモしておきます。終了日も同様にドリルダウンしましょう。

ホームから[閉じて読み込む]をクリックし、[接続のみ作成] を選択して[OK]をクリックします。

これで、テキストタイプとして都道府県と日付タイプとして開始日と終了日という 2 つのパラメータが作成されました。

クエリにパラメータを挿入する

パラメータを作成したら、それらを使いましょう。これを行うには、クエリのMコードに変更を加えます。

Ｍコードとは数式バーに表示されているコードです。詳細エディターまたは数式バーから変更できます。簡単に行うために、この例では数式バーを使用します。

重要　 M コード（PowerQueryエディター内の数式、コード）は大文字と小文字を区別します。

PowerQueryエディターを開き、抽出元のクエリ (この例ではデータ2020年クエリ) を開きます。

数式バーが表示されていない場合は、[表示] -> [数式バー] をクリックします。

値大阪府が設定されているステップを見つけます。

見つけ方
クエリ名をクリック→適用したステップ→フィルターされた行をクリック→数式バーで大阪府が書き込まれた式が表示されたらＯＫ

パラメーター “大阪府”を都道府県に置き換えます。

パラメーターの場合はダブルコーテーションは不要ですので削除しましょう。

= Table.SelectRows(フィルターされた行, each ([都道府県] = "大阪府"))

= Table.SelectRows(フィルターされた行, each ([都道府県] = 都道府県))

次に、開始日と終了日パラメーターを適用します。2020/1/5と2020/1/31を日付としてハードコーディングした手順を探します。

＃date(2020, 1, 5)を開始日に、＃date(2020, 1, 31)を終了日に、置き換えます。

変更前

= Table.SelectRows(変更された型, each [販売日] >= #date(2020, 1, 5) and [販売日] <= #date(2020, 1, 31))

変更後

= Table.SelectRows(変更された型, each [販売日] >= 開始日 and [販売日] <= 終了日)

これで、パラメータが適用されました。[ホーム] – [閉じて読み込み] をクリックして、変更を Excel に読み込みます。

パラメーターの使用

Excel に戻ったら、[データ] – > [すべて更新] をクリックします。

京都府の指定した期間の販売データが抽出できました。

クエリが更新され、選択したパラメータの値のみが表示されていますね。ワークシートのパラメータテーブルの値を変更して同じ手順で他の条件も試してみましょう。

なごみ

上手くいきましたか。慣れるまで最初は少し戸惑うかもしれませんね。また、よくエラーになるチェックポイントなども、ご紹介したいと思います。

その他のパラメータ活用例

これでPower Query のハードコード値をパラメーターとして設定できるようになりました。

便利な使い方の例は以下の通りです。アイデア次第で使いやすいツールが作成できるでしょう。

外部データファイルをインポートするファイルパス
財務レポートの期間終了日
特定の領域（事業部門の名称など）に対してのみレポートを作成する
他のユーザーが変更する必要がある設定（ツールの作成者とユーザが異なる場合は特に効果あり）

この例では、セルに作成したExcel テーブルをソースとして使用してきましたが、名前付き範囲、CSVをデータソースをパラメータとして利用することもできます。

いかがでしょうか。パラメータの作成は奥が深く、もちろん方法は他にもたくさんあります。今回は初心者の方にも分かりやすいものをご紹介しました。

一度設定してしまえば、便利に活用できます。ぜひ試してみてください。また、別のサンプルについてもご紹介していきますね。

では、また。

なごみ

手順が少なくてシンプルな方法としてこちらもあります。よろしければご覧ください。私はこちらの方法をよく利用します。PowerQueryに慣れてきた方はこちらがいいかもしれません。