Power Query 第18回目です。前回はテーブルからパラメータークエリを作成して使う方法について勉強しました。今回から、カスタム関数について勉強していきたいと思います。
VBAでは、Functionプロシージャを使ってユーザー定義関数を作成して引数を受け取ることができました。それと同じように、Power Query でもカスタム関数を作成して利用することができるようです。
今回から少し難しくなりそうです。複数回に渡って カスタム関数 について勉強していきたいと思います。前回勉強したパラメータークエリよりもっと汎用性を満たせることが可能になるでしょう。
しっかりついていけるように気合を入れていきます。
今回もよろしくお願いしますm(__)m
【この記事でわかることは】
・カスタム関数とはどんなものかがわかります
・第1回目は「単純なカスタム関数」の作成方法です
前回記事のおさらいは、下のカードをクリックすれば開きます(^^ゞ
・前回の内容は テーブルからパラメータークエリを作成して活用する方法 でした。
記事作成にあたっては次の Microsoft office のサポートの情報などを参照しています。
・Power Query for Excel のヘルプ
・Excel の Power Query について
・カスタム関数を作成して呼び出す
なお、この記事の画像は Microsoft 365 で取得した画像です。Excel2016 のリボンやメニューなどバージョンによって違うようなので、適宜読み替えをお願いします(^^)/
カスタム関数とは
カスタム関数とは、VBAで Functionプロシージャを使ってユーザー定義関数を作成するのと同じように、一連の入力値を受け取って、1 つの出力値を返します。
カスタム関数を作成しておけば、何度も再利用することができます。別のクエリまたは値に適用するロジックで、必要な場所と必要なときにカスタム関数を呼び出して利用することができます。
独自の Power Query カスタム関数の作成には M 式言語を使用します。作成する方法は複数あります。
カスタム関数の作成（1）
単純なカスタム関数の作成方法
単純なカスタム関数の例として、自ブックのパスを返す関数を作成してみましょう。
前回使用したExcelファイルを（少し整理して）使っていきます。
空のクエリを作成します
空のクエリの作成方法は次の二つのどちらでもOKです。
Excelから作成する場合
・[データ] → [その他のソース] → [データの取得] → [その他のデータソースから] → [空のクエリ]
Power Query エディターから作成する場合
・Power Query 左側 [クエリ] ナビゲーションウィンドウで空白の場所を右クリックします。
・[新しいクエリ] を選択し[その他のソース] → [空のクエリ] と選択します。
[クエリ設定] プロパティで、クエリの名前を “CurrentPath” に変更します。
詳細エディターでコードを書き換えます
・書き換え前の テンプレートのスターター コードがこちらです。
let
ソース = ""
in
ソース・これを次のコードに置き換えます。
let
CurrentPath = () => Excel.CurrentWorkbook(){[Name="mypath"]}[Content]{0}[MyPath]
in
CurrentPath・関数名を CurrentPath としたいので、ソース を CurrentPath に変更します。
・= () の () には 関数の引数が入りますが、ここでは何も入れません。
・関数の型を指定する場合は = () の後に as 指定型 のように記述します。
・関数の本体は、移動記号 => の後に記述します。ここではテーブルの値を参照するコードを入れました。Excel.CurrentWorkbook(){[Name=”mypath”]}[Content]{0}[MyPath]
・テーブルのセルには「=LEFT(CELL(“filename”),FIND(“[“,CELL(“filename”))-1)」の式が入っています。詳細は「自ブックのパスを使う方法」に記事を参照願います。
・最後に [完了] を選択した後の Power Query エディター が次の画像です。
・関数のデータ型を指定していないので「function() as any」となっています。
・例えば、テキスト型に指定する場合は、CurrentPath = () as text => のように記述します。
・[閉じて読み込む] でクエリを保存します。
・クエリ CurrentPath をカスタム関数として作成できました。
・関数アイコン 
がクエリ名 CurrentPath の左側に表示されています。
CurrentPath カスタム関数の呼び出し
・関数を呼び出す場合は、[呼び出し] ボタンをクリックします。
・関数の結果がデータ プレビューに表示され、[クエリ] ウィンドウに既定の名前の [呼び出された関数] クエリとして追加されました。名前は変更できます。
・単純なカスタム関数なので設定値（この場合は自ブックのパス）を呼び出すだけの動作です。
・結果のクエリを保存する必要がない場合は、[×]で閉じる際に表示される下図のダイアログで [破棄] を選択します。ワークシートに結果を表示したい場合は [保存] を選択します。
・保存を選択した場合は、下図のようになります。
・これで確認できました。作成されたシートとクエリは不要ならばを削除しておきましょう。
まとめ（おわりに）
以上で、カスタム関数の作成についての第１回目「単純なカスタム関数の作成」の勉強は終了です。
まとめと感想など
カスタム関数の作成方法の第１回目は「単純なカスタム関数の作成」でした。「単純な文字列」の表示ではつまらないので、以前勉強した「自ブックのパス」を表示するカスタム関数を作成してみました。いかがでしたか？
この続きも楽しみにしていてね(^^)/
そういえば、フォルダーから取得で作成される「ヘルパークエリ」の中にあった「ファイルの変換」というクエリはカスタム関数だったんですね！
ちょっと難しくなったけど、ついていけました。つぎは大丈夫かなぁ(^^;
★★★ ブログランキング参加中！ クリックしてね(^^)/ ★★★
【今後の記事について】
今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてる」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m
【検討中の今後の記事内容は・・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思います・・・・・
・今後の記事にご期待ください(^^)/
過去記事のサンプルファイルをダウンロードできます
今回記事のサンプルファイルを登録しています！
過去の記事で使用したサンプルファイルをダウンロードできるようにページを設置していますので、こちら（このリンク先）からご利用ください
【今回わかったことは】
・単純なカスタム関数の作成方法と呼び出し方法がわかりました
・カスタム関数のデータ型の指定方法がわかりました