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

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

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

この度Yousefulからパワークエリ・パワーピボットをオールインワンで学習できるExcelPro講座パワーコースをリリース致しました！詳細は以下のバナーをクリックしてください！

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

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

簡単なカスタム関数

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

【手順】

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

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

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

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

(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) =>

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

let

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

nameOfStep = initialValue * 10

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

in nameOfStep

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

複数の引数とデータ型

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

【入力型の宣言】

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

例： (Mytext as text =>)

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

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

(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：「計算される列」

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

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

【GIF】手順③～④

【手順④の結果】

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

ここから、M言語で書かれているコードを一部追加・修正します。

M言語に関してもっと知りたい方は、以下の記事がおすすめです。
M言語の基礎的なことを解説している記事となっているので、合わせて読んでみてください！

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

1.使用するテーブル「テーブル名」 2.フィルタリングする列の名前「フィルター列」 3.フィルタリングする値「フィルター内容」 4.結果を返す列の名前「計算される列」

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

【コードの修正①】

ソース = Excel.CurrentWorkbook(){[Name=”Sales_Data”]}[Content],

ソース = テーブル名,

【コードの修正②】

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

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

【コードの修正③】

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

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

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

完成した 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をもっと使いこなせるようになりたい」「Power Queryがまだよく分かっていない…」という方は、以下の記事がおすすめです。
Power Queryの基本的な使い方,業務にスグ活用できる実践的な練習問題などを解説しているので、ぜひ合わせて読んでみてくださいね。

まとめ

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

「VBAはできないけど業務を自動化したい」
「周りと差をつけるExcelスキルを手っ取り早く獲得したい」

そんな方にはユースフルのExcelPro講座パワーコースがオススメです！登録者40万人のYouTubeチャンネル「ユースフル」のビジネス経験豊富な講師陣が、実務直結の学びを120本20時間の講義動画でお届けします。利便性・希少価値の高い実務スキルを身につけて、周囲をアッと言わせる超生産的な働き方を手に入れましょう！