Mから始めよう #6 〜Power Queryの関数を作成する

(この記事は、2021年7月20日に記述しました）

　Ben Gribaudoのブログで紹介されている内容を学びます。(source is here)

1. Power Queryの関数はラムダ式

　Power Queryで関数を作るには、「新しい空のクエリ」を作成し、詳細エディタで記述します。最初に引数を示し、=> の後に返す値を記述します。

Function01

(a, b, x) =>
let
    Result = x * (a + b)
in
    Result

　この関数では、a,b,xの3つの引数から、let以下の計算を行い、inで示したResultの値が返されます。関数が出来上がると、以下のように、クエリ名のところに左側に関数であることを示すアイコン fx が表示されます。

　この画面で、a, b, x に値を入力して「呼び出し」ボタンを押すと結果が表示されます。
　プロジェクト内の他のクエリからこの関数を呼び出すには、M式で以下のように書きます。

FunctionTest01

let
    Source = Function01(1, 3, 8)
in
    Source

　Function01の引数を使い、8 * (1 + 3)が計算され、結果は、32となります。

　簡単な計算であれば、let in を使わずに、以下のように記述することも出来ます。

Function02

(a, b, x) => x * (a + b)

　いわゆる、ラムダ式というやつですね。

2. 同一コード上に関数を記述する方法

　関数用のクエリを別に作成しなくても、M式の中で関数を記述することも出来ます。

FunctionTest02

let
  Input1 = 10,
  Input2 = 15,
  Input3 = 30,
  SecretFormula = (a, b, x) =>
    let
      Output = x * (a + b),
      OutputDoubled = Output * 2
    in
      Output,
   Result = SecretFormula(Input1 , Input2, Input3)
in
  Result

　上記では、Output に 30 * (10 + 15) の結果となる 750 が代入され、OutputDoubled には Output * 2 の 1500 が入ります。しかし、返される結果は Output だけなので、OutputDoubled は実際は計算されることなく 750 だけが返されます。

3. 省略可能なパラメータ

　
　引数を省略可能にするためには、optionalを指定します。

Function03

(LastName, FirstName, optional MiddleName) =>
    Text.Combine({FirstName, MiddleName, LastName}, " ")

　Power Queryエディタの表示上は、全て （省略可能） と書かれていますが、M式の中で記述するときにはoptionalと指定されているもの以外は必須となります。

つまり、上記の関数では、以下の2つの方法で呼び出すことになります。

FunctionTest03

Function03("Smith", "Joe")
Function03("Brown", "Robert", "James")

（省略可能） とは、nullを許可するかどうかという意味です。上記画面からパラメーターを何も入れないで呼び出しを行うと、以下のクエリが作成されます。

Invoked Function

let
    Source = Function03(null, null, null)
in
    Source

したがって、以下の書き方もできます。

FunctionTest04

Function03(null, "Joe")
Function03(null, null, "James")

4. 関数の戻り値の型とパラメーターの型

　これまで作成したものは、パラメータ、戻り値いずれも宣言されておらず、全ての型を使用できる (as any) ようになっています。パラメータの入力の画面の下の方に、関数の完全な形が表示されています。

　型を指定してやるには、以下のように記述します。

Function05

(Comment as text, PriceEach as number, Quantity as number) as text => 
    Comment & Number.ToText(PriceEach * Quantity)

　パラメーターの入力画面には、以下のように表示されます。

　型を指定すると、optional指定はできません。また、値にnullを入れることもできなくなります。

データ型

　データ型は以下のようなものがあります。

データ型	型の識別子
テキスト	text
True/False	logical
数値	number
日付/時刻	datetime
日付	date
時刻	time
日付/時刻/タイムゾーン	datetimezone
期間	duration
Binary	binary
任意（ヌルを含む）	any
任意（ヌルを含まない）	anynonnull
ヌル	null
リスト	list
レコード	record
テーブル	table
関数	function
型値	type
	none

　type number は、Double.Typeです。
　値にnullを含める場合は、null許容型にします。(例. nullable number)

5. 関数の引き渡し

　関数を関数に渡すということは、Power Queryの中では、通常意識せずに行われています。以下の式を見てください。

FunctionTest05

let
  Source = #table( {"Col1", "Col2"}, { {1, 2}, {3, 4} } ),
  ColumnCreator = (row) => row[Col1] + row[Col2],
  AddColumn = Table.AddColumn(Source, "RowTotal", ColumnCreator)
in
  AddColumn

　Table.AddColumnの中で、行を追加するだけでなく、1行ごとにColumCreatorが呼び出されて計算が実行されています。

6. インライン関数

　関数に名前を付けずにインラインで記述することができます。先の関数をインラインで書き直すと以下のようになります。

FunctionTest06

let
  Source = #table( {"Col1", "Col2"}, { {1, 2}, {3, 4} } ),
  AddColumn = Table.AddColumn(Source, "RowTotal", (row) => row[Col1] + row[Col2])
in
  AddColumn

　結果は、上記と同じです。一般に、インライン関数を使用すると、プログラムの実行速度の高速化につながるといわれています。ただし、そのために処理が読みにくくなったり、サイズが大きくなったりすることがあります。インライン関数に適しているのは、処理の少ない小さな関数です。

7. each と _(アンダースコアー)

　eachは、リストを扱う関数の場合、リストのアイテムそれぞれに処理を行います。テーブルを扱う関数の場合、行それぞれに処理を行います。"(_) => ..." と同じ意味です。そして、 _ は、アイテムを渡すときに、そのアイテムとして使用されます。

　下記の３つの式は、同じ結果となります。

(_) => _[Col1] + _[Col2]
each _[Col1] + _[Col2]
each [Col1] + [Col2]

　以下の書き方の方が、読みやすくなります。

FunctionTest07

let
  Source = #table( {"Col1", "Col2"}, { {1, 2}, {3, 4} } ),
  AddColumn = Table.AddColumn(Source, "RowTotal", each [Col1] + [Col2])
in
  AddColumn

eachが入れ子になる場合

　eachが入れ子になった場合、内側のeachは外側の値を参照することはできません。_ が内側のスコープに書き換えられてしまうためです。その場合は、(x)=> (xは任意)を使って、別のスコープを与えてやる必要があります。

　以下の式は、「ファイル名、シート名を残してシートの項目をまとめる２ Table.AddColumnでファイル名を入れ込む」で作成したものです。

    // Dataにファイル名、シート名を埋め込む
    Step4 = Table.AddColumn(
        Step3,
        "Worksheets",
        (x)=>
            Table.AddColumn(
                x[Data],
                "File Sheet",
                each [File = x[File], Sheet = x[Name]]
            )
    ),

8. クロージャー(Closure)と高階関数(Higher-order Function)

　高階関数(HOF)とは、関数を引数にとることができ、関数を返すことができる関数です。

　関数を引数にとるとは、以下のような式になります。

let
    twice = (f, n) => f(f(n)),
    square = (n) => n * n
in
    twice(square,2)

　先の FunctionTest06 でも、Table.AddColumn の中に関数を記述しています。

let
    twice = (f, n) => f(f(n)),
    square = (n) => n * n
in
    twice(square,2)

twice関数の引数にsquareという関数を渡しています。

　M式は、クロージャー機能も使うことができます。クロージャーとは、関数とその関数が定義された状態をセットにした特殊なオブジェクトのことです。関数の中に関数を定義出来て、親子関係とか、関数を変数に入れることが出来たり、親に定義された変数を参照できるレキシカルスコープとか、関数の状態を保存できる等あります。

let
   addTo = (x) => (y) => x + y,
   addToFive = addTo(5),
   eight = addToFive(3)
in 
   eight

上記では、 addToFive = addTo(5) という式によって、 addToFive には addToFive = (y) => 5 + y という関数が設定され、eightには 5 + 3 の結果が返ることになります。このように、関数の一部を動的に変更することができるようになります。

　以下の例では、割引結果の値を計算するCalculatorGenerator関数に 0.5 の引数を与え、HalfOffという関数を作成しています。

FunctionTest08

let
  Source = { 1, 2, 3, 4, 5 },
  CalculatorGenerator = (discountPercentage) =>
    (value) => (1 - discountPercentage) * value,
  HalfOff = CalculatorGenerator(0.5),
  Result = List.Transform(Source, HalfOff)
in
  Result

9. 再帰関数

　関数が内部から、＠をつけて自分を呼び出すことができます。

FunctionTest09

let
  SumConsecutive = (x) => if x <= 0 then 0 else x + @SumConsecutive(x - 1),
  Result = SumConsecutive(4)
in
  Result

　上記の例では、最初に４の引数が与えられたSumConsecutiveが、0になるまで引数を-1して自分を呼び出し続けます。戻り値は、4 + 3 + 2 + 1 + 0 = 10 となります。
　ただし、再帰処理は、システムリソースを使うので注意しましょう。