本ページには広告が含まれています。

Excel Power Query カスタム関数の作成（その2）

PowerQuery カスタム関数の作成（その2）
2021.08.09

Power Query 第19回目です。カスタム関数についての勉強２回目です。
Power Query でもカスタム関数を作成してサクッと利用できるようになることが目標です。

くるみこ
くるみこ

カスタム関数 について第２回目の勉強です。
今回は 複数のパラメーターを持つカスタム関数の作成とパラメーターを呼び出す方法について勉強しましょう(^^)/

パラメーターですね。今回もしっかりついていけるように頑張ります。
よろしくお願いしますm(__)m

【この記事でわかることは】
・複数のパラメーターを持つカスタム関数の作成方法がわかります
・カスタム関数のパラメーターを呼び出す方法がわかります

前回記事のおさらいは、下のカードをクリックすれば開きます(^^ゞ

Excel Power Query カスタム関数の作成（その1）
Excel Power Query カスタム関数を作成して利用する方法の解説第1回目です。単純なカスタム関数の作成方法をテーブルに表示された自ブックの CurrentPath を取得する設定で解説。カスタム関数の呼び出し方法についても解説。
www.kurumico.com

・前回の内容は カスタム関数の作成（その１）で「単純なカスタム関数」の作成について勉強・解説しています。

記事作成にあたっては次の Microsoft office のサポートの情報などを参照しています。
・Power Query for Excel のヘルプ
・Excel の Power Query について
・カスタム関数を作成して呼び出す

なお、この記事の画像は Microsoft 365 で取得した画像です。Excel2016 のリボンやメニューなどバージョンによって違うようなので、適宜読み替えをお願いします(^^)/

スポンサーリンク

カスタム関数の作成（2）

第２回目は「複数」のパラメーターを持つカスタム関数をテーブルから作成していきます。

複数のパラメーターを持つカスタム関数の作成方法

前回使用したExcelファイルをそのまま継続して使っていきます。

テーブルを用意します

パラメーターを設定するための次のようなテーブルを用意します。

パラメーターテーブルを用意

・テーブル名は「ParamSetTable」としています。
・テーブル名は後から変更できますが、変更する場合は M コードも変更する必要があります。
・列名は [Name] と [Value] とします。（この設定は Record.FromTable関数を使うため）

・上の画像では「MasterData1」「MasterData2」とパラメーターが追加されています。このように簡単にパラメータを追加していくことができるのも利点の一つです。

カスタム関数「GetParamValue」のコードを設定します

パラメータークエリのコードを確認します

・テーブルからパラメータークエリを作成する方法(リンク先参照）でクエリを作成後に「Value」の項目部分で右クリック→[ドリルダウン] を選択します。
・下の画像が、詳細エディターを表示したものです。

詳細エディター
let
    ソース = Excel.CurrentWorkbook(){[Name="ParamSetTable"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"Name", type text}, {"Value", type text}}),
    Value = 変更された型{0}[Value]
in
    Value

・[Value] 列の最初の行のデータを返す設定のコードになっています。これに手を加えていきます。

パラメータークエリのコードに手を加えます

・テーブルの [Name] 列の「名前」を指定したら [Value] 列の値を返すように設定していきます。

・先ずはテーブルのレコードを取得したいので、レコード関数 Record.FromTable を使います。

【Record.FromTale の構文】

Record.FromTable(table as table) as record

・[Name],[Value] のペアを持つ Table から Record を作成して返します。

・さらに、取得したレコードのメンバーの値を返す Record.Field 関数を使用します。

【 Record.Field の構文】

Record.Field(record as record, field as text) as any

・Record の指定したメンバーの値（field の値）を返します。

・カスタム関数の名前は「GetParamValue」という名前にします。

・調整したカスタム関数 GetParamValue のコードがこちらです。

let
    GetParamValue = (ParamName as text) => let 
        ソース = Excel.CurrentWorkbook(){[Name="ParamSetTable"]}[Content],
        #"変更された型" = Table.TransformColumnTypes(ソース,{{"Name", type text}, {"Value", type text}}),
        レコード取得 = Record.FromTable(#"変更された型"),
        メンバー取得 = Record.Field(レコード取得,ParamName)
    in
        メンバー取得
in
    GetParamValue

実行した画像

・コード設定後 [完了] 押下後の画像がこちらです。エラー無く完了しています。

パラメーターテーブル作成後の画像

・カスタム関数を使用する場合は GetParamValue(“MyPath”) のように 関数（引数）とします。

・ParamName に MyPath と入力して [呼び出し] をクリックした画像が下です。

ParamName に MyPath と入力して [呼び出し] をクリック

・[呼び出された関数]クエリが作成されました。

・[Name] “MyPath” の [Value] 自ブックのパス が返されています。

・カスタム関数 GetParamValue の完成です。

カスタム関数を実際に使用してみます

それでは実際に カスタム関数 GetParamValue を使って呼び出せるかを確認してみましょう。
下の過去記事のカードで作成したクエリの複製を使って、自ブックのパスとソースフォルダーを取得できるかどうかテストしてみましょう。

Excel Power Query 自ブックのパスを使う方法
Excel Power Query 自ブックのパスを取得してテーブルにセットすることで ソース の設定などがをやりやすくなります。CELL 関数で取得して、必要な文字列をLEFT関数とFIND関数で抜き出す方法とその活用法を勉強しています。
www.kurumico.com

上の記事で使ったサンプルのクエリ [結合クエリ（手動）] はすでにサンプルとして残してあるので、その複製クエリ [結合クエリ（カスタム関数使用）] を作成して使います。

変数をカスタム関数に置き換えます

・変更前の詳細エディターが下の画像です。let の次の３行を書き換えます。

詳細エディター変更前Mコード
let
    MyPath = Excel.CurrentWorkbook(){[Name="mypath"]}[Content]{0}[MyPath],
    SourceFolder = Excel.CurrentWorkbook(){[Name="source"]}[Content]{0}[Source],
    ソース = Folder.Files(MyPath & SourceFolder),

・この部分を次のように書き換えます。

let
    ソース = Folder.Files(GetParamValue("MyPath") & GetParamValue("Source")),

・変数 [MyPath] の部分を GetParamValue(“MyPath”) に置き換えます。
・変数 [SourceFolder] の部分を GetParamValue(“Source”) に置き換えます。

Mコード変更後の詳細エディター

カスタム関数が機能しているか確認します

・[完了] ボタン選択で、エラー無くデータ取得できたことが確認できました。

カスタム関数が機能しているか確認

・ほかのクエリでも、このカスタム関数が使えます。

・これで変数に値を代入したり、パラメータークエリを作成する必要がなくなりました。

まとめ（おわりに）

以上で、カスタム関数の作成についての第2回目「複数のパラメーターを持つカスタム関数」についての勉強は終了です。

まとめと感想など

くるみこ
くるみこ

カスタム関数の作成方法の第2回目は「複数のパラメーターを持ったカスタム関数の作成」でした。パラメータークエリの作成で出来上がったコードを「カスタム関数」に変更していく方法とカスタム関数を使う方法を勉強しました。いかがでしたか？　

レコード関数 を使っていく部分は難しくてよくわかりませんでした。
でも、今回せっかく勉強したのでしっかり覚えておくようにしたいです。
カスタム関数って便利ですね！

【今回わかったことは】
・複数のパラメーターを持つカスタム関数の作成方法がわかりました
・カスタム関数でパラメーターを呼び出す方法がわかりました

★★★ ブログランキング参加中！ クリックしてね(^^)/ ★★★

にほんブログ村 IT技術ブログ VBAへ
にほんブログ村

人気ブログランキングアイコン
Visual Basicランキング

【今後の記事について】

今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてる」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m

【検討中の今後の記事内容は・・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思います・・・・・
・今後の記事にご期待ください(^^)/

スポンサーリンク
エックスサーバーバナー広告
スポンサーリンク

過去記事のサンプルファイルをダウンロードできます

今回記事のサンプルファイルを登録しています！

過去の記事で使用したサンプルファイルをダウンロードできるようにページを設置していますので、こちら（このリンク先）からご利用ください

【Excel VBA】サンプルファイル_ダウンロードのページ
【Excel VBA】の記事内で使ったサンプルファイルを（Zipファイルで）ダウンロードできるようにしました。記事内では細かい部分の解説が不十分ですが、実際にファイルをダウンロードして実行することで使えるのかどうか見ていただけたら幸いです。
www.kurumico.com