【Excel】Power Queryでカスタム関数を作成!ExcelのM言語で再利用可能な処理を定義する方法

ExcelのPower Queryで、繰り返し行うデータ処理を効率化したいと考えている方もいるでしょう。毎回同じ手順を再現するのではなく、一度定義した処理を呼び出すだけで使えるようにしたいはずです。この記事では、ExcelのM言語を使ってPower Queryでカスタム関数を作成する方法を解説します。これにより、複雑なデータ変換処理を再利用可能にし、作業時間を大幅に削減できます。

Power Queryカスタム関数の概要とメリット

Power Queryのカスタム関数は、M言語というPower Queryで使用される関数型言語で記述されます。この関数は、特定のデータ変換ロジックをカプセル化し、必要に応じて何度でも呼び出せるようにします。これにより、同じような処理を何度も手動で設定する手間が省けます。

カスタム関数を作成する主なメリットは、処理の標準化と効率化です。例えば、日付のフォーマット統一、特定の文字列の置換、数値の計算など、定型的な処理を関数化しておけば、誰が実行しても同じ結果が得られます。また、複雑な処理を関数としてまとめることで、クエリ全体の見通しが良くなり、メンテナンス性も向上します。

M言語でカスタム関数を作成する手順

Power Queryでカスタム関数を作成するには、M言語エディタを開き、関数の定義を記述します。ここでは、簡単な例として、テキストの先頭に指定した文字列を追加する関数を作成する手順を説明します。

1. Power Queryエディタを開く
   Excelのリボンメニューから「データ」タブを選択し、「データの取得と変換」グループにある「データの取得」から「Power Queryエディタの起動」をクリックします。
2. 新しいクエリから「空のクエリ」を選択
   Power Queryエディタの「ホーム」タブにある「新しいソース」をクリックし、「その他のソース」から「空のクエリ」を選択します。
3. 数式バーにM言語を記述
   表示された空のクエリの数式バー(表示されていない場合は「表示」タブから「数式バー」をチェック)に、以下のようにカスタム関数の定義を記述します。

   let
       PrefixText = (textToPrefix as text, prefixToAdd as text) as text =>
           prefixToAdd & textToPrefix
   in
       PrefixText
   

4. 関数を名前変更して保存
   作成したクエリ(デフォルトでは「Query1」など)の名前を、例えば「AddPrefix」のように分かりやすい名前に変更します。これで、このクエリがカスタム関数として定義されました。

作成したカスタム関数を別のクエリから呼び出す方法

定義したカスタム関数は、他のクエリから呼び出して利用できます。ここでは、先ほど作成した「AddPrefix」関数を実際に使って、テキスト列にプレフィックスを追加する手順を説明します。

1. 新しいクエリを作成
   「ホーム」タブから「新しいソース」→「その他のソース」→「空のクエリ」を選択して、新しいクエリを作成します。
2. 数式バーに関数を呼び出すM言語を記述
   数式バーに、作成したカスタム関数「AddPrefix」を呼び出すM言語を記述します。この例では、サンプルデータとして「Excel Power Query」というテキストを作成し、プレフィックスとして「CustomFunc_」を追加します。

   let
       Source = "Excel Power Query",
       Result = AddPrefix(Source, "CustomFunc_")
   in
       Result
   

3. 結果を確認
   数式バーに上記のように記述してEnterキーを押すと、数式バーの下に「CustomFunc_Excel Power Query」という結果が表示されます。
4. テーブルとして読み込む
   結果が表示されたら、「ホーム」タブの「閉じて読み込む」から「閉じて次に読み込む」を選択し、「テーブル」としてExcelシートに読み込むことができます。

カスタム関数に引数を設定する

カスタム関数は、引数を受け取るように定義することで、より柔軟な処理が可能になります。引数とは、関数に渡す値のことで、これにより関数は異なる入力に対して異なる結果を生成できます。

例えば、数値のリストを受け取り、その合計値を返す関数を作成する場合を考えます。この場合、数値のリストが引数となります。M言語では、引数ごとにデータ型を指定することが推奨されています。

引数を持つ関数の定義例

以下のM言語は、数値のリストを受け取り、その合計値を返すカスタム関数「SumList」の定義です。

let
    SumList = (numberList as list) as number =>
        List.Sum(numberList)
in
    SumList

この例では、「numberList」という名前で「list」型の引数を受け取り、「number」型の値を返します。関数本体では、「List.Sum」関数を使ってリストの合計を計算しています。

引数付き関数を呼び出す手順

作成した「SumList」関数を呼び出すには、以下のように記述します。ここでは、{1, 2, 3, 4, 5}というリストを渡して合計を計算します。

let
    MyList = {1, 2, 3, 4, 5},
    Total = SumList(MyList)
in
    Total

このクエリを実行すると、結果として「15」が表示されます。このように、引数を使うことで、関数はより汎用的な処理を実行できるようになります。

カスタム関数の管理と再利用

作成したカスタム関数は、Power Queryエディタ内の「クエリ」ペインに表示されます。これらの関数は、同じブック内の他のクエリから直接参照できます。

関数を分かりやすく管理するためには、クエリに意味のある名前を付けることが重要です。また、関数が複雑になる場合は、コメントを追加して処理内容を記録しておくと、後で見返したときに理解しやすくなります。M言語では、`//` を使ってコメントを記述できます。

関数をブック間で共有する方法

ブック間でカスタム関数を共有したい場合は、関数が定義されているクエリをコピーして、別のブックのPower Queryエディタに貼り付ける方法があります。または、関数定義をテキストファイルとして保存しておき、必要に応じて新しいブックで読み込むことも可能です。

より高度な共有方法としては、Power BI Desktopなどの環境でカスタム関数ライブラリを作成し、それを共有する方法もありますが、ExcelのPower Query単体では、クエリのコピー&ペーストが最も手軽な方法と言えます。

よくあるエラーと対処法

カスタム関数を作成・利用する際によく発生するエラーには、引数のデータ型が一致しない、関数名が間違っている、M言語の構文ミスなどがあります。

エラーが発生した場合は、エラーメッセージをよく読み、数式バーのM言語の記述を確認してください。特に、引数のデータ型(text, number, list, recordなど)が関数定義と呼び出し側で一致しているかを確認することが重要です。また、数式バーの構文ハイライト(キーワードが色分けされる機能)も、エラー箇所を特定するのに役立ちます。

カスタム関数の応用例

Power Queryのカスタム関数は、様々なデータ処理に応用できます。ここでは、いくつかの具体的な応用例を紹介します。

日付フォーマットの統一関数

複数のソースから取得したデータに含まれる日付のフォーマットがバラバラな場合、カスタム関数で統一フォーマットに変換できます。例えば、`#date(yyyy, mm, dd)` のような形式に変換する関数を作成すれば、日付の比較や計算が容易になります。

特定の条件に基づく値の置換関数

商品コードやステータスコードなど、特定の文字列を別の文字列に置き換える処理も関数化できます。例えば、`if`文や`List.Transform`関数を組み合わせることで、条件に応じた柔軟な置換処理を実装できます。

複雑な計算処理の関数化

売上データから利益率を計算したり、複数の項目を組み合わせて新しい指標を作成したりするような、複雑な計算ロジックも関数として定義できます。これにより、分析の再現性が高まります。

まとめ

この記事では、ExcelのPower Queryでカスタム関数を作成し、M言語で再利用可能な処理を定義する方法について解説しました。カスタム関数を作成することで、データ処理の効率化、標準化、そしてクエリの可読性向上を実現できます。

作成した関数は、引数を設定することでさらに汎用性を高めることができます。ぜひ、ご自身の業務で繰り返し行うデータ変換処理をカスタム関数化し、Power Queryの活用度を高めてください。今後は、より複雑なロジックを持つ関数を作成し、データ分析の自動化に挑戦してみましょう。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】