輸出するときなど、外貨（ドルとかユーロ）で販売することがありますが、外貨と邦貨（日本円）が混在すると金額が集計できません。
外貨を邦貨に換算して集計し、売上金額を日本円で集計していると思います。
Power Queryのカスタム列を追加して、USDは１ドル×150円、EURは1ユーロ×180円といった感じで通貨ごとに条件式を入れることは可能です。
= if [通貨] = "USD" then [金額] * 150
else if [通貨] = "EUR" then [金額] * 180
else [金額]
しかし、このやり方だと通貨が増えれば増えるほど数式が複雑になります。
売上明細から為替レート換算するカスタム関数を作成すれば、通貨と金額さえあれば簡単に換算できます。
次のサンプルのように、為替レートはマスタ化しておけば通貨が増えても数式を修正する必要はありません。
換算した結果です。
本記事では、為替レート換算するカスタム関数を作成する方法を解説します。
サンプルデータ
為替レートと売上明細をサンプルデータとして使用します。
サンプルデータはこちらからダウンロードしてください。
データの加工
サンプルデータでPower Queryエディターを開くと、すでに為替レートと売上明細があります。
この2つのデータをもとに為替レート換算を行うカスタム関数を作成します。
カスタム関数を作成する
空のクエリからカスタム関数を作成します。
「ホーム」タブー「新しいソース」ー「その他のソース」ー「空のクエリ」をクリックします。
クエリの名前は分かりやすいように「為替レート換算」と入力します。数式を入力しやすいように、「ホーム」タブー「詳細エディター」をクリックします。
詳細エディターが開きました。後述の数式を入力し、OKをクリックします。
数式の次の通りになります。順番に解説します。
= (currency as text, amount as number) =>
let
ソース = 為替レート,
通貨取得 = Table.SelectRows(ソース, each ([通貨] = (currency))),
レート取得 = 通貨取得{0}[レート],
レート換算 = レート取得 * (amount)
in
レート換算1行目ですが、関数に渡すパラメーターを記述しています。currencyというテキスト型の通貨と、amountという数値型の金額の2つのパラメーターがカスタム関数に渡せるようになります。
(currency as text, amount as number) =>次にletの中身を見ていきます。ソースは為替レートにしています。1ドル150円といった為替レートを持っており、このマスタをもとに換算します。
let
ソース = 為替レート,この時点ではJPY、USD、EURの3行を読み込んでいます。
為替レートを読み込んだ後、特定通貨の為替レートを取得します。パラメーター「currency」で指定した通貨の行を取得しています。
通貨取得 = Table.SelectRows(ソース, each ([通貨] = (currency))),パラメーター「currency」にUSDを入力していた場合、USDの行だけ読み込んでいる状態です。
取得した通貨のレートを取得します。USDであれば150が取得されます。
レート取得 = 通貨取得{0}[レート],最後に、取得した換算レートとパラメーター「amount」で入力した金額と掛け算すると、換算が完了です。
レート換算 = レート取得 * (amount)OKをクリックした後は、次の画面が表示されます。
カスタム関数を実行する
作成したカスタム関数を実行してみましょう。
currencyに「USD」、amountに「10」と入力し、「呼び出し」をクリックします。
カスタム関数が実行され、1500（10ドル×150円）と返ってきました。
カスタム関数を呼び出して列を追加する
作成したカスタム関数を売上明細で使います。
「列を追加」タブー「カスタム関数の呼び出し」をクリックします。
次を入力し、OKをクリックします。
新しい列名：「金額（日本円）」を入力
関数クエリ：「為替レート換算」を選択
currency：列名を選択し、「通貨」を選択
amount：列名を選択し、「金額」を選択
邦貨（日本円）に為替レート換算された金額列が追加されました。
データの出力
「ホーム」タブー「閉じて読み込む」をクリックします。
売上明細が出力されました。
