PowerQuery クエリを関数化する方法

クエリを関数化する方法をまとめました。

（クエリを関数化する方法だけ聞いても、メリットとデメリットがわからないと実践する気になれない、という方もいるでしょうか。最初、メリット・デメリットの説明も書いていたのですが、書いているうちに、自分でつまらないなと感じてしまい、公開をやめてしまいました。）

概要

概要を説明します。

• クエリの関数化と言っていますが、実際にはクエリを「関数のリスト」にします。この手順は「クエリを関数のリストにする」で説明しますが、そんなに難しい手順ではありません。
• クエリを関数のリストにできたとしましょう。ではその関数のリストはどのように使えばいいのでしょうか。関数のリストを通常のクエリのように使うには、「リスト内の関数を合成」する必要があります。当記事ではその関数にComposeAllと名付けていますが、このComposeAll関数の作り方と、その使い方を「関数を合成する関数」で説明します。
• クエリをデバッグするには、ステップ毎に評価するのが標準的な方法ですが、クエリを関数化してしまうとステップ毎の評価ができなくなってしまいます。そこで関数化したクエリに対してステップ毎の評価を擬似的に再現する方法を「関数リストをステップ毎に評価する」で説明します。

関数を合成する関数

次のクエリを定義しておきます。クエリの名前はComposeAllにしておきます。

// リスト内の関数を左から右の順に合成する。
let
    ComposeAll = (funcList as list) as function =>
        if List.Count(funcList)<=1
        then funcList{0}
        else (v) => @ComposeAll(List.RemoveFirstN(funcList,1))(funcList{0}(v))
in
    ComposeAll

コメントにも書いてますが、このクエリは「リスト内の関数を全て合成した関数を作る関数」です。
動きを簡単に言うと、リストから要素を１つずつ取り出して、取り出した要素を順次合成する、という作業をリストが空になるまで繰り返します。
※当然ですが、関数以外の要素があった場合はエラーになります。

日本語で説明してもややこしいので、実際に動きを見てみましょう。

次のクエリで試します。

let
    f = (n as number) => n+1,
    g = (n as number) => n*2,
    gf = ComposeAll({f,g})
in
    gf(1)

このクエリ内で、fは「入力された数値＋１を返す関数」、gは「入力された数値×２を返す関数」です。
たとえばf(3)は３＋１つまり４が返りますし、g(3)は３×２つまり６が返ります。
そしてgfは「入力された数値をfしてからgした結果を返す関数」です。いわゆるfとgの合成関数です。
なのでgf(3)は(３＋１)×２＝４×２＝８なので８が返ります。
なぜそうなるか知りたい方はComposeAllの定義を見てください。
なお、fg = ComposeAll({g,f})というようにfとgを逆順にすると合成の順番が逆になります。したがってfg(3)は３×２＋１＝６＋１＝７すなわち７が返ります。

「ComposeAllの引数はリストであること」「ComposeAllの合成の順番」によく注意してください。

クエリを関数のリストにする

関数ComposeAllの使い方はわかったと思うので（わかったということにしておく）、ComposeAllを使ってクエリを関数化します。
ComposeAllの引数は関数のリストなので、クエリを関数のリストにする必要があります。

ここで、クエリを関数のリストに書き換えるにあたって、GUIの通常の手順で作ったクエリが満たしている次の性質が重要です。

• 基本的に、let...in式が１回、かつ１回だけ使われており、inの直後の式が読み込まれる
• let...in式内の各識別子（通称 ステップ）の定義は直前のものだけを参照している

この性質を踏まえると、次の手順でクエリを関数のリストにすればComposeAllに入力できるようになります。

1. let,inをそれぞれ[,]に置き換え、inの後の識別子を消す
2. リストに変換するために、全体を関数Record.FieldValuesでラップする
3. ステップの定義は全て関数式に置き換える
4. 一番最初のステップ（ソース）は最初の入力値のためステップごと削除する（必要ならソースだけのクエリを作ったり、パラメータクエリを使ったりするとよい）
5. 必要なら先頭に恒等関数（入力を一切変換せずにそのまま出力として返す関数）を追加する

関数クエリを評価する

最後に、関数化したクエリを評価するクエリを作ります。
具体的には、次のようなクエリを作成します。
ComposeAll(関数のリスト)(ソース)

以上でクエリの関数化ができました。

実践例

言葉だけで説明してもわからないかもしれないので、簡単な例で実践してみましょう。

次のテーブルを使います。

そのテーブルを、PowerQueryで次のテーブルに変換しました。

詳細エディターにはクエリは次のように書かれています。

let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    区切り記号による列の分割 = Table.SplitColumn(ソース, "Name", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Name.1", "Name.2"}),
    追加されたカスタム = Table.AddColumn(区切り記号による列の分割, "BirthYear", each #date( Date.Year(DateTime.LocalNow()) - [Old], Date.Month(DateTime.LocalNow()), 1))
in
    追加されたカスタム

変換の内容

• Name列を半角スペースでName.1列とName.2列に分離
• 今日の日付からOld列の年数だけ遡ったBirthYear列を追加

ComposeAllを作成

まず新規のクエリでComposeAllを作成します。
クエリペイン（一番左側のとこ）で右クリックするか、リボンの右上の「新しいソース」から「空のクエリ」を選択し、できたクエリで詳細エディターを開いて、さっきのComposeAllのコードを貼り付けます。クエリの名前はComposeAllにしておきます。

画像のように、「パラメータの入力」という表示になっていたら正解です。
数式バーに貼り付けのではなく、必ず詳細エディターを開いて貼り付けてください。数式バーに貼り付けると、以下の画像のように、貼り付けたものがすべて文字列として扱われてしまいます。

関数リストを作る

次に、さっきの手順（letとinを[と]に置換、全体をRecord.FieldValuesでラップ、各ステップを関数化、ソースステップの削除、先頭に恒等関数を追加）に沿って関数リストを作ります。
作成例としては次の通りです。

Record.FieldValues([
    // ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    恒等関数 = (テーブル as table) as table => テーブル,
    区切り記号による列の分割 = (テーブル as table) as table => Table.SplitColumn(テーブル, "Name", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Name.1", "Name.2"}),
    追加されたカスタム = (テーブル as table) => Table.AddColumn(テーブル, "BirthYear", each #date( Date.Year(DateTime.LocalNow()) - [Old], Date.Month(DateTime.LocalNow()), 1))
])

もとのクエリと比較しやすいように、ソースステップはコメントアウトにしました。
また、各ステップの関数化では、パラメータ名を全て「テーブル」に統一しました。

これはGUI上でプレビューすると次のように見えます。
（クエリが急にいっぱい増えていますが、あとの節で使うものなので今は無視してください）

データソースを取得するだけのクエリを作る

変換する前の、データソースからテーブルを取得するだけのクエリを新規クエリとして作成しておきます。
この手順は省いてもいいのですが、基本的にはあったほうがわかりやすいと思います。
ソースであることがわかりやすいように、クエリ名は「テーブル1のソース」としました。

なお省いた場合は、ソースを取得するクエリを関数の引数に直接書けばいいです。
ただし何らかの理由でソースが取得できなかったり、ソースが頻繁に変わったりする場合は、ソースを取得するだけのクエリを作っておくほうがわかりやすいでしょう。

関数を評価する

最後に、新規クエリで関数を評価します。
空のクエリを新規で作成し、詳細エディターに次の式を入れます。

ComposeAll(テーブル1の関数リスト)(テーブル1のソース)

ご覧の通り、関数化する前とまったく同じ結果が得られました。

関数リストをステップ毎に評価する

ステップ毎の評価ができない

以上の手順で、クエリの関数化が実現できました。

ところがここで一つちょっと面倒な問題が発生しています。
通常のGUI操作で作ったクエリだとステップ毎の評価ができたのですが、クエリを関数のリストにした結果、ステップ毎の評価ができなくなってしまっています。

これがクエリを関数化するデメリットの１つです。
クエリのステップ毎の評価はPowerQueryでかなり重要な機能、というか普通なら使うのが当たり前のような機能です。それが使えないというのはかなり辛い。

この代替案として、「同様の機能を再現する」という方法を思い付いたので、それを説明します。

ComposeAllにステップ巻き戻し用のパラメータを追加する

ComposeAll関数を次の関数に書き換えます。

(funcList as list, optional BackStep as number) =>
    let
        ComposeAll = (funcList as list) as function =>
            if List.Count(funcList)<=1
            then funcList{0}
            else (v) => @ComposeAll(List.RemoveFirstN(funcList,1))(funcList{0}(v))
    in
        ComposeAll(List.RemoveLastN(funcList, Replacer.ReplaceValue(BackStep, null, 0)))

もとのComposeAll関数との大きな違いはBackStepという引数を追加したことです。
このBackStep引数は、0以上の整数を入力すると、入力した個数だけ要素をリストの後ろから除きます。引数にテーブルを入れた状態で言うと、ステップを巻き戻すことに対応します。
また、BackStep引数は省略すると0（ステップを一つも巻き戻さない）を指定したことと同じになります。なのでもとのComposeAll関数とまったく同じ使い方ができるわけです。

言葉で説明するより見たほうが早いかもしれません。

たとえば先ほどの例で実行すると下図のようになります。

BackStep 0 (Default)

BackStep 1

BackStep 2

このように、BackStepの数値をずらしていくことにより、ステップ毎の評価が可能になります。

なお、BackStepに指定した数値が関数リストの要素の範囲にない場合はエラーとなります。

ステップ毎に適用した結果をテーブル化する

ComposeAll関数に巻き戻し引数を追加してデバッグしやすくなりましたが、もっと使いやすくしましょう。
具体的には、「各ステップ毎に評価した結果をテーブルとして返します」。

まず次の関数を新たに追加しましょう。中で使用しているComposeAllは先ほどのBackStep引数を追加したものを使用してください。

(funcList as list, optional v as any ) =>

let
    テーブル化 = Table.FromValue(funcList, [DefaultColumnName = "関数リスト"]),
    追加されたインデックス = Table.AddIndexColumn(テーブル化, "インデックス", 1,1, Int64.Type),
    評価列追加 = Table.AddColumn(追加されたインデックス, "ここまで評価", each ComposeAll(funcList , List.Count(funcList) - [インデックス] )(v) )
in
    評価列追加

この関数は、関数リスト内の関数を逐次合成し、その逐次合成した関数でテーブルを逐次評価します。
この関数には「関数リストの逐次テスト」という名前を付けておきます。

やはり言葉だとわかりづらいので実際に見てもらいましょう。
この関数に、先ほどの関数リストとテーブルを入れると、次の結果になります。

これだけ見ると「なんだこれ？」という感じですが、この３列目の「ここまで評価」という列は、各セルの値がテーブルとなっています。そのセルにカーソルを合わせる（セルをクリックする）と、セルの中身がプレビューされます。上から順に次のようになっています。

このように、ステップ毎の評価とほぼ同じ機能が実現できました。

なお、Errorのセルがあった場合は、次のようにプレビューされます。

ここのプレビュー内ではセルを選択することはできないので、セルレベルエラーのデバッグをこのクエリだけで行うのは難しそうですが、そこは工夫次第かと思います。（通常のデバッグ手順を真似るとか、デバッグ用の列を追加するとか）

関数リストの最初に恒等関数を追加した理由

クエリを関数のリストにするときに、「恒等関数を頭に追加するかどうかはどっちでもいいけど、追加したほうがわかりやすいよ」と書きました。
その理由は、恒等関数を追加したほうが今説明した「ステップ毎の評価」が少しやりやすいからです。

もし恒等関数を頭に追加しなかった場合、１ステップ目（数え方によっては０ステップ目）から、いきなり変換後の結果が返ってきてしまいます。通常の方法で作成した「ソース」というステップ、ソースデータをそのまま参照するだけのステップですが、それに相当する関数が恒等関数なわけですね。

補足

単なる関数化でなく関数のリストにする理由

ちなみにクエリを関数化することだけが目的なら、ここまで込み入ったことをする必要はありません。let式の前に(ソース)=>という式を入れて、ソースのステップを削除するだけで、関数化自体は可能です。

ではわざわざ関数のリストにした理由はなんでしょうか。
これは一言で言えば（理由になっているか微妙ですが）「リストという構造を持たせておくことで応用の幅が広がる（特にListライブラリの関数が使える）から」です。

たとえば、一番最後に説明した、ステップ毎にクエリを評価する機能を作りたいとき、関数リストでなくただの関数にしてしまうと、できません。
また、変換の順番を入れ替えたりする操作も、通常のGUIであれば当然のように使う作業ですが、詳細エディター内で順番を入れ替えるのは結構面倒です（実際にやってみたことがある方はよくご存知でしょう）。これも、ただの関数でなく関数リストにしておけば、リスト内の関数の順番を入れ替えるだけで簡単に遂行できます。
ちなみに詳細エディター内で行の順番の入れ替えは、キーボードショートカット Ctrl + Alt + ↑↓ で可能です。なのでもしかしたらGUIでマウスでグリグリして順番を入れ替えるより楽かもしれません。

こういった理由があるため、手間さえ惜しまなければ、ただの関数にするより関数のリストにしたほうがいいのです。

レコード化してからリスト化している理由

関数リストの作成の手順で、「let...inを[...]に置き換えて全体をRecord.FieldValuesでラップする」というところは、一度レコード化してからRecord.FieldValues関数でリストに変換するという手順を踏んでいます。関数リストにするだけなら「let...inを{...}に置き換える」にしても構いません。実際、5/13以前は当記事でもそのように説明していました。

レコード化してからリストに変換する方式に変えた理由は、「直接リスト化するのと直接レコード化するのとでは、どちらも大して作業量が変わらず、しかもレコードなら要素に名前が付けられる」からです。

GUI操作でクエリを生成した場合、各ステップには名前がつきますが、直接リストにするとステップ名を消さなければなりません（コメントにするという方法もなくはないですが、美学に反する…）。せっかく意味のあるステップ名がついているのだから、できれば残したいところです。しかも名前を付けておけば再利用もしやすいですし。
そう考えて、「一度レコード化してから関数でリスト化する」を基本の作業手順とすることにしました。

とすると今度は逆に「リストにしなくてもレコードのまま使えばいいのでは？」という疑問が浮かぶかもしれませんが、レコードの場合は各フィールドに必ず名前を付けなければならず、ComposeAll関数の使い勝手が悪くなってしまいます。
変換の内容を一言で説明できる関数なら名前も付けやすいですが、説明するのに苦労する関数や、逆に簡単すぎてわざわざ名前を付ける必要を感じない関数なんかもあるでしょう。ComposeAll関数をなるべく汎用性の高いものにしたいという考えからすれば、入力する引数の方はレコードよりリストのほうが望ましいと思います。

おわりに

今回はPowerQueryでクエリを関数化する方法について説明しました。
関数化するのが少し手間なのは間違いないので、全てのクエリを関数化する必要はありませんが、
クエリの編集で同じような作業が続いているな、面倒だな、と思い始めたら、関数化を検討してみるのもいいのではないでしょうか。

また、ComposeAll関数や、「関数リストの逐次テスト」関数は、クエリを関数化するときに何度も再利用することになるため、テンプレートとか外部ファイルとかにまとめておくのがよさそうです。
.odcファイルという、マイクロソフトオフィスのデータ接続用ファイルを使うと相性が良さそうなのですが、PowerBIでの使い方がよくわからず…。
とりあえず私は、よく使うクエリを１つのExcelファイルに保存するようにしていますが、もっといい方法をご存知の方がいれば教えてください。