消費税の計算を入れるときは、10%で計算（軽減税率は8%）しますよね。
Power Queryでは、例えば商品に消費税分を上乗せする場合は、1.1倍するため次の式になります。
= [商品] * 1.1 // 商品に消費税分を上乗せ消費税が10%のまま変わらなければ問題ありませんが、3%→5%→8%→10%と増えてきた歴史があり、今後変更がある度に式を修正する必要があります。
この問題はパラメータを使用することで回避できます。
param_consumption_taxというパラメーターに0.1を設定して、次のように数式にパラメーターを使用できます。
= [商品] * (1 + param_consumption_tax) // 商品に消費税分を上乗せこれなら、消費税が15%になってもparam_consumption_taxを0.15に修正するだけで済みます。パラメータを1か所修正するだけで、消費税計算してる数式は修正しなくて済みますね。
本記事では、Power Queryのパラメーター管理の機能について解説します。
サンプルデータ
サンプルデータはこちらからダウンロードしてください。
パラメーターの管理
パラメーターは次の3種類あります。
- 任意の値を設定する
パラメーターに値を入力し、式等で使用する。 - 値の一覧から選択可能にする
パラメーターの選択肢を用意し、必要に応じて値を切り替え可能。 - クエリの結果から選択可能にする
パラメーターの選択肢をクエリから取得し、必要に応じて値を切り替え可能。
サンプルデータをもとに3種類の機能をそれぞれ試していきます。
サンプルデータを開いて、「データ」タブー「クエリと接続」をクリックします。「クエリと接続」の中の「販売実績」を右クリックし、「編集」をクリックします。
任意の値を設定する
消費税をパラメーター設定する「param_consumption_tax」を作成してみます。
「ホーム」タブー「パラメーターの管理」をクリックします。
「新規作成」をクリックします。
次を入力し、OKをクリックします。
名前：「param_consumption_tax」を入力
説明：パラメーターの意味が分かる説明
種類：「10進数」を選択
提案された値：「任意の値」を選択
現在の値：「0.1」を入力 ※10%という意味
パラメーターが作成されました。
作成したパラメーター「param_consumption_tax」を使って、金額（税込）列を作成します。
「販売実績」をクリックし、「列の追加」タブー「カスタム列」をクリックします。
「新しい列名」に「金額（税込）」と入力、「カスタム列の式」に後述の変換式を入力し、OKをクリックします。
金額列×110%とすれば税込価格になるため、以下の数式を入力します。
= [金額] * (1 + param_consumption_tax)税込の金額列が作成できました。
値の一覧から選択可能にする
任意の値を設定するでは、パラメーターの値は手入力しましたが、一覧から選択することが可能です。
商品カテゴリをパラメーターから動的にフィルターする例を用いて使い方を解説します。
「ホーム」タブー「パラメーターの管理」の▼ボタンをクリックし、「新しいパラメーター」をクリックします。
パラメーターの新規作成画面が開きます。
次を入力し、OKをクリックします。
名前：「param_fillter_itemcategory」を入力
説明：パラメーターの意味が分かる説明
種類：「テキスト」を選択
提案された値：「値の一覧」を選択、一覧に「家電」「食品」「文房具」「衣料品」「家具」を入力
規定値：「家電」を選択
現在の値：「家電」を選択
パラメーターが作成されました。
作成されたパラメーターを使って、商品カテゴリをフィルターします。
「販売実績」をクリックし、商品カテゴリ列の▼ボタンー「テキストフィルタ」ー「指定の値に等しい」をクリックします。
ABCの右にある▼ボタンー「パラメーター」をクリックします。
作成したパラメーター「param_fillter_itemcategory」を選択し、OKをクリックします。
商品カテゴリが家電でフィルターされました。
param_fillter_itemcategoryを違う値の食品に変更します。
商品カテゴリが食品でフィルターされました。パラメーターを変更することで、数式を変更することなく結果を出力することが出来ますね。
クエリの結果から選択可能にする
値の一覧から選択可能にすると似ていますが、違う点はクエリから一覧を取得できることです。
商品カテゴリの一覧をクエリから取得する例を用いて使い方を解説します。
「ホーム」タブー「新しいソース」ー「その他のソース」ー「空のクエリ」をクリックします。
数式にリスト形式で商品カテゴリを入力し、クエリの名前に「商品カテゴリリスト」と入力します。
= {"家電","食品","文房具","衣料品","家具"}
「ホーム」タブー「閉じて読み込む」をクリックします。
「商品カテゴリリスト」を右クリックし、「読み込み先」をクリックします。
Excelシートに表示する必要がないため、「接続の作成のみ」を選択し、OKをクリックします。
OKをクリックします。
これで商品カテゴリ一覧のクエリが完成です。作成したクエリを元にパラメーターを作成します。
Power Queryエディタに戻り、「ホーム」タブー「パラメーターの管理」の▼ボタンー「新しいパラメーター」をクリックします。
次を入力し、OKをクリックします。
名前：「param_fillter_itemcategory2」を入力
説明：パラメーターの意味が分かる説明
種類：「テキスト」を選択
提案された値：「クエリ」を選択
クエリ：「商品カテゴリリスト」を選択
現在の値：「家電」を選択
パラメーターが作成されました。
ここで違和感はありませんでしたか？クエリから商品カテゴリのリストを取得しているはずなのに、リストから選択できないのでは？と。
現在の値も手入力しか出来ないですし、どういった意味があるのやら？
理由は調べても分かりませんでしたが、パラメーターの編集画面からであれば一覧から選択できるようです。
「ホーム」タブー「パラメーターの編集」をクリックします。
この画面からは一覧から選択可能です。
活用例
パラメーターを使ってファイルパスを指定する
Power Queryで作成したExcelファイルを他の人に配った場合、ファイルを参照してるパスを修正することがあります。
ファイルパスをパラメーターにすることによって、修正の手間を簡単に出来ます。
