Power Queryのカスタム関数を徹底解説|M言語の編集も紹介

Power Query カスタム関数

この記事では、Power Queryのカスタム関数の作り方と具体的な使い方を解説していきます。「コードをどう書いたらいいかわからない」という方でも大丈夫です。実務さながらの売上サンプルデータを用いて丁寧に解説していくので、すぐに業務で活用できるようになります。

こんな方におすすめ
  1. Power Query初心者
  2. カスタム関数の使い方を知りたい
  3. イチからコードを記述することができないからPower Queryの使用を諦めていた
  4. ある程度Power Queryの扱いに慣れてきて、カスタマイズをしていきたい

サンプルデータを使って練習がしたい・・・という方向けに、本記事で使用しているデータをダウンロードできるようにご準備しております!こちらからダウンロードをしてみてください。

目次

カスタム関数とカスタム列の追加との違いは?

カスタム関数とは、Power Query内で扱うコード記述を伴う「自作の関数」のことを指します。異なるクエリや値に対して、何度でも再利用できるという特徴があります。また、似た機能名に[カスタム列]の追加があります。これは、元データの表の数値列を元に任意の数式を設定したい時に用いるものです。

[カスタム列]の追加機能

例:「数量」「単価」列を使って「売上(数量×単価)」列を作成する

手順① [ホーム]タブ > [カスタム列]をクリック
手順② [カスタム列の式]を入力 → [OK]

ちなみに、マウス操作で行う場合は、下記のようになります。

このように今回の操作はマウスでもできましたが、加算・減算・乗算などのデフォルト以外の計算をしたいときは、「カスタム列」を設定する必要があります。

簡単なカスタム関数

それでは本題の例題として、ある値を10倍にするカスタム関数を作成してみます。

【手順】

手順①
└[ホーム] > [新しいソース] > [その他のソース] > [空のクエリ] の順にクリック
手順②
└新しく作成したクエリにわかりやすい名前をつける
 *今回は、「Sample」としました。
手順③
└[ホーム] > [詳細エディタ]をクリックし、詳細エディタを開く

手順④
└詳細エディターが開けました。
└現在、空の状態なのでここから値を10倍にするためのコードを記載していきます。

【手順④後の詳細エディター】

【POINT】letとinとは?

空のクエリを作成すると、初めからlet文とin文が準備されています。letとinはMコードの重要な要素で、変換の開始(let)と終了(in)の位置を表しています。

手順⑤ 詳細エディタに下記のような入力をします。

(initialValue) =>

let
 nameOfStep = initialValue * 10
in
 nameOfStep

「initialValue」初期値
「=>」数式使用の際宣言する記号
 nameOfStep:このステップ(作業)の名前

上記のコードの詳しい解説はこちらをご覧ください。

M言語のコードは大文字と小文字(全角半角も)が区別されますので、注意して入力してください。

「完了」ボタンをクリックすると次のようなウィンドウが表示されて、「ある値に10をかける」という関数(計算フォームのようなモノ)が完成しました。

試しに関数のテストしてみましょう。initialValueに数値を入力し(ここでは10としました)、[呼び出し]をクリックします。
正しく計算されていれば、「100」という値が表示されるはずです。

【GIF】パラメーターテスト

=initialValue * 10 

先ほど作成した関数の計算式のinitialValueに10 を代入して
10 * 10 = 100 というアウトプットが作成された というイメージです。

このように関数を自作し、パラメーターで関数を呼び出すことで、Power Queryは「呼び出された関数」という新しいクエリを作成します。

【関数テスト完了後イメージ】

新しい列にカスタム関数を追加する

次は、自作したカスタム関数をクエリの中で実際に使用してみましょう。「Sales_Data」クエリを開き、カスタム列を作成します。

 手順①
└リボン>[列の追加] > [カスタム列]をクリック
「新しい列名」を「新しい列の追加:Sample」に設定

手順②
└「カスタム列の式」に下記の通り記載し[OK]ボタンをクリック
  =Sample([価格])

「価格」 列にそれぞれ 10 を掛けた結果が、「新しい列の追加:Sample」列に作成されます。

構文の詳しい解説

やっていることはなんとなくわかったけど、「記載されたコードの意味がわからない・・」という方のために、ここからは、先ほど作成したコードが何をしているのかを行ごとに分解しながら一緒にみていきます。

(initialValue) =>
1行目

関数で使用する変数を宣言しています。今回は、「initialValue」という名前の変数に設定しました。「=>」の記号は、数式使用の際宣言する必要のある記号です。

let
2行目

変換ステップの開始を示すものです。「ここから変換作業が始まるんだな」というくらいの認識でOKです。

nameOfStep = initialValue * 10
3行目

次に、nameOfStepというステップ名が書かれています。
どんな変換作業をしているステップなのか、名前を任意で付けられます。(今回のnameOfStepという名前自体には意味はありません。)
ここでのコードはnameOfStepでは、initialValue(1行目で宣言した引数)×10をした値を計算してください ということを指しています。

in

nameOfStep
最終行

inは変換の終わりを指しています。「ここで変換作業が終ったんだな」というくらいの認識でOKです。
通常in文の後には、次のステップ名を指定しますが、この例では、nameOfStep(前のステップと同じ名前)を書いています。

複数の引数とデータ型

Power Queryの関数を使用すると、複数の引数を指定したり特定のデータ型を設定したりできます。これは、クエリの最初にある引数宣言の箇所(letより前)で行います。

入力型の記載場所例

【入力型の宣言】

「テキストデータ型の宣言方法」

例:
(Mytext as text =>)

このコードでは、「myText」はテキストデータの入力のみを許可しているという意味です。

【POINT】データ型はたくさんある

例えば、引数としてテーブルだけを許可するには、次のようにします。

(MyTable as table =>)

しかし、すべてのデータ型のコードの書き方なんて覚えられないですよね。そんな時は、編集作業に関係のない別のクエリを使用し、マウス操作でデータ型を変更した後、作成されたコードを見て、「あ、こうやって書いたらいいのか!」という調べ方をすると便利かと思います。

例)データ型の調べ方

【変更前】乗算列のデータ型は「number」

【GIF】データ型変更

手順①
└リボン>[列の追加] > [カスタム列]をクリック
「カスタム列の式」に「数量」×「単価」の計算式を入力する
手順②
└「数量」「単価」列を選択する
手順③
└リボン>[列の追加] > [標準]> [乗算]をクリック

【変更後】乗算のデータ型は「text」

出力型の宣言

入力型の宣言をするのと同様に、出力する際のデータの型を示す宣言も必要です。

例えば下記の例では、「myText」をテキスト値として読み込み、その読み込んだものをテーブルデータ型として出力していることを示しています。

(myText as text) as table =>

複数の引数を宣言する場合は、括弧の中にコンマ区切りで読み込む引数を列挙します。

(myText as text , myTable as Table) as number =>


「myText」を テキストデータ型、「myTable」をテーブルデータ型として読み込み、その読み込んだもの全てを数値データ型として出力するということが記されています。

実際の使用例(変換を含むカスタム関数)

ここからは、実際の使用例を用いて、カスタム関数を学んでいきたいと思います。

「難しそう・・」と思う方もいるかもしれないですが、カスタム関数に必要なのは、わずかな構文理解と適切な変換ステップだけです。

シチュエーション:ある顧客(ABC商事)の購入金額の合計を「顧客売上計」として計算するカスタム関数を作成する。

ABC商事には10件の取引があり、1行目の売上は44円、2行目の売上は30円…10行目の売上は81円なので、「顧客合計」列にはそれぞれ515円(売上合計)と表示されるようにしたい。

【解答例】

全体の作業フローは以下のようになります。

手順①
└クエリの複製
手順②
└複製されたクエリの名前を変更する
手順③
└マウス操作でABC商事のデータでフィルターをかける
手順④
└合計値を計算するステップを作成する

—(ここまでに行ったステップはPowerQueryがコーディング(記述)してくれている)—

手順⑤
└自動で記述されたコードを編集して、汎用性の高い関数に作り直す
(1ではABC商事でフィルターをかけたが、毎回フィルター指定する顧客名が違うため他の場合でも対応できるコードに書き換えてあげる)
大きな流れ

ここからは、それぞれのステップを詳しく見ていきましょう。

手順① [売上データ]クエリの複製
 └クエリ一覧 > [売上データ]を右クリック → メニュー[複製]をクリック

手順② 複製されたクエリの名前を変更
 └クエリの名前を 「fx顧客売上計」 にする。
 * クエリの頭に「fx」とつけたのは、このクエリが関数クエリだということが後からわかりやすいようにするためです。
 └次に、2つの列項目名の変更

①Before:「顧客」→After:「フィルター列」

②Before:「価格」→After:「計算される列」
列項目名の変更内容
【GIF】列項目名の変更

手順③ 「フィルター列」を「ABC商事」でフィルタリング

手順④ 
 └「計算される列」列を選択
 └リボンの[変換]→[統計]→[合計]をクリック
これで、選択した顧客(ここではABC商事)の合計値が計算されます。

【GIF】手順③~④

【手順④の結果】


ここまでのステップ(編集内容)は、詳細エディターの中に下記のようなコードで書かれています。

ここから、M言語で書かれているコードを一部追加・修正します。M言語の基本については、こちらの記事で解説しています。

次の手順⑤は、ここまでPowerQueryによって自動で記録されたコードを編集して、汎用性の高い関数に作り直していきます。
まず、コードの最初の行で変数を宣言する必要があります。(ここでは、4つの変数を宣言します。)

1.使用するテーブル「テーブル名」
2.フィルタリングする列の名前「フィルター列」
3.フィルタリングする値「フィルター内容」
4.結果を返す列の名前「計算される列」
宣言する4つの変数
= (テーブル名 as table, フィルター列 as text, フィルター内容 as text, 計算される列 as text) =>
詳細エディター内の冒頭の宣言文

【コードの修正①】

ソース = Excel.CurrentWorkbook(){[Name=”Sales_Data”]}[Content],
Before
ソース = テーブル名,
After

【コードの修正②】

  #”名前が変更された列 ” = Table.RenameColumns(ソース,{{“顧客”, “フィルター列”}, {“売上”, “計算される列”}}),
Before
 #”名前が変更された列 ” = Table.RenameColumns(ソース,{{フィルター列, “フィルター列”}, {計算される列, “計算される列”}}),
After

【コードの修正③】

フィルターされた行 = Table.SelectRows(#”名前が変更された列 “, each ([フィルター列] = “ABC商事”)),
Before
フィルターされた行 = Table.SelectRows(#”名前が変更された列 “, each ([フィルター列] = フィルター内容)),
After

ここでお気づきの方もいるかと思いますが、今作成したこの関数には、これまで扱っていたデータクエリの列(「顧客」や「価格」)が含まれていません。つまり、どんなクエリにも使える汎用性の高い関数を作成したことになります。

完成した M コードは次のようになります。

(テーブル名 as table, フィルター列 as text, フィルター内容 as text, 計算される列 as text) =>

let
    ソース = テーブル名,
    #”名前が変更された列 ” = Table.RenameColumns(ソース,{{フィルター列, “フィルター列“}, {計算される列, “計算される列“}}),
    フィルターされた行 = Table.SelectRows(#”名前が変更された列 “, each ([フィルター列] = フィルター内容)),
    計算された合計 = List.Sum(フィルターされた行[計算される列])
in
    計算された合計
完成版テキスト

この関数が思うように動作するか下記条件で、試してみましょう。

テーブル名   :売上データ
フィルター列  :顧客
フィルター列内容:ABC商事
計算される列  :売上

【GIF】関数のテスト

【関数テストの結果】

思った通りの結果が出たので成功です。

最後に、[売上データ]クエリを選択して、作成した関数を[カスタム列の追加]ボタンから追加していきます。

数式として以下を入力し[OK]を押して、下記コードを入力します。

=fx顧客売上計(ソース,”顧客“,[顧客],”売上“)

【カスタム列の追加】画面

【最終結果】

各顧客の売上合計が「顧客売上計」列に表示されます。
例えば、1行目から10行目のABC商事の「顧客売上計」列を見ると、いずれも515と表示されています。

まとめ

いかがでしたでしょうか?この記事では、Power Queryにおける基本的なカスタム関数の作り方と変換を伴うカスタム関数の作成方法を学びました。
Mコードの操作は少し複雑で、特に初心者の方は、難しいものととらえがちです。しかし、この記事のようにサンプルデータで練習すればすぐに習得できるので、ぜひ試してみてください!

「パワークエリのようなExcel最新機能を学びたい」
「日々の業務を効率化できるExcel技を知りたい」

そんな方にはユースフルの【無料体験版】ExcelPro講座がオススメです!元伊藤忠商事経理でExcel実務のプロ、長内孝平(おさ)のExcel技を無料で8時間分も学習できるオンライン動画講座です。

気に入ったらシェア!
  • URLをコピーしました!
目次