Power Query へそのゴマAdvent Calendar 2024

Power Query へそのゴマ　第９章　ループ処理と再帰処理

Last updated at 2024-12-11Posted at 2024-12-08

Power Query では、ループ処理や再帰処理をサポートする関数がいくつか用意されており、特にリストやテーブルを操作する際に多用されます。本節では、List.Generate や再帰処理に加えて、Power Query のテーブル操作を深掘りし、Table.Translate などの高度な関数も含めて解説します。

9.1 リストのループ処理

List.Transform と List.Accumulate は、リストの各項目に対して操作を実行します。一方で List.Generate は、停止条件を設定し、指定された条件が満たされなくなるまで関数を繰り返し適用する、While ループの機能を反映しています。

9.1.1. List.Transform

List.Transform は、リストの各要素に対して処理を行います。

for x in [2, 4, 6]:
    array[x] = array[x] * 2

上記の処理をPower Queryでは以下のように書きます。

List.Transform( 
    {2, 4, 6},
    each _ * 2
)

9.1.2. List.Accumulate

List.Accumulateは、リストの各要素に対して累積処理を行います。構文は以下のようになっています。

List.Accumulate(
       list as list,
       seed as any,
       accumulator as function )

以下の例では、1から5までを足していく処理が行われます。

数値リスト {1, 2, 3, 4, 5} の合計を計算する

let
    Sum = List.Accumulate(
        {1, 2, 3, 4, 5},  // リスト
        0,                // 初期値
        (state, current) => state + current // 累積処理
    )
in
    Sum

初期値0に 1から5までの数が順番に加算され、結果は、15になります。

List.Accumulateは、累積値とリストの現在の値の2つの変数を持つ関数です。stateとcurrentは固定の変数名ではありません。(n, x) => n + xと記述しても構いません。

List.Accumulate(
      { 1, 2, 3, 4, 5 },             
      {1},
      ( state, current ) =>
        state & { List.Last( state ) * current }
    )

上記は、最初 1 だけのリストに、リストの最後の値と1から5を乗算した値を追加していく処理が行われています。

この処理は、以下のような順番で行われます。

Step	state	current	計算	結果
1	{1}	1	{1} & {1 * 1}	{1, 1}
2	{1, 1}	2	{1, 1} & {1 * 2}	{1, 1, 2}
3	{1, 1, 2}	3	{1, 1, 2} & {2 * 3}	{1, 1, 2, 6}
4	{1, 1, 2, 6}	4	{1, 1, 2, 6} & {6 * 4}	{1, 1, 2, 6, 24}
5	{1, 1, 2, 6, 24}	5	{1, 1, 2, 6, 24} & {24 * 5}	{1, 1, 2, 6, 24, 120}

結果は以下の様になります。

この機能を利用して、複数の文字を置換する処理が考えられます。

上記の名前から「_」「-」「.」を消してスペースに、「!」を削除する処理を行います。

Table.ReplaceValueは1文字づつの置換しかできないため、4種類の置換を行うには4回のステップが必要になります。List.Accumulateを使って1回で処理する方法は、以下のようになります。

let
    // 余分な文字が入った名前データ
    Source = 
        Table.FromList(
            {
                "Rich_de_Groot!",
                "Greg-Deckler!",
                "Broam.Julius!"
            },
            Splitter.SplitByNothing(),
            type table [Names = text]
        ),

        
    Convert =
        List.Accumulate(
            { {"_", " "}, {"-", " "}, {".", " "}, {"!", ""} },  // 変換元と変換先のペアのリスト
            Source,
            ( state, current ) =>
                Table.ReplaceValue(
                    state,
                    current{0},
                    current{1},
                    Replacer.ReplaceText,
                    {"Names"}
                )
        )
in
    Convert

変換元と変換先のペアをリストにして、置換処理を行っています。stateには常に名前データのテーブルがあり、返還対象が次々に処理されていきます。

Step	state	current	計算
1	Source	{"_", " "}	"_"を" "に置換 (->Table1)
2	Table1	{"-", " "}	"-"を" "に置換 (->Table2)
3	Table2	{".", " "}	"."を" "に置換 (->Table3)
4	Table3	{"!", ""}	"!"を""に置換 (->Table4)

9.1.3. List.Numbers

List.Numbersは数値のリストを作成します。

1から5までの数値のリスト {1, 2, 3, 4, 5}

List.Numbers(1, 5)

1から2づつ増える8個の値のリスト {1, 3, 5, 7, 9, 11, 13, 15}

List.Numbers(1, 8, 2)

1から0.1づつ増える5個の値のリスト {1, 1.1, 1.2, 1.3, 1.4}

List.Numbers(1, 5, 0.1)

1から1づつ減る5個の値のリスト {1, 0, -1, -2, -3}

List.Numbers(1, 5, -1)

9.1.4. List.Generate

List.Generate は、条件を満たす間、要素を生成し続けるループ処理を行う関数です。多くのプログラミング言語で見られるwhileループに似ています。

1から12までのリストを作成

let
    Source = List.Generate(
        ()=> 1,                 // 初期化式 as function
        each _ <= 12,           // 条件式 as function
        each _ + 1              // 加算式 as function
    )
in
    Source

ここでは、１から12までの値のリストを作成しています。

省略可能な第4引数は、出力する値を指定できます。

2025年1月から12月までのリストを作成

let
    Source = List.Generate(
        ()=> 1,                 // 初期化式 as function
        each _ <= 12,           // 条件式 as function
        each _ + 1,             // 加算式 as function
        each #date(2025, _, 1)  // 出力値 as function
    )
in
    Source

この例では、2025年1月から2025年12月まで、毎月1日のリストを作成しています。

以下のコードは、累積値の列を追加しています。

let
    Source = 
        Table.FromRows(
            {
                {"2024-01", 900},
                {"2024-02", 850},
                {"2024-03", 925},
                {"2024-04", 875},
                {"2024-05", 910},
                {"2024-06", 725},
                {"2024-07", 750},
                {"2024-08", 740},
                {"2024-09", 900},
                {"2024-10", 925}
            },
            type table [Period = text, Sales = number]
        ),

    // 累積値を計算
    RTCalc =
        List.Generate(
            ()=> [index = 0, RT = Source[Sales]{0}],
            each [index] < List.Count(Source[Sales]),
            each [index = [index] + 1, RT = [RT] + Source[Sales]{[index] + 1}],
            each [RT]
        ),

    // 元のテーブルと累積値を合わせる
    Combine = Table.FromColumns(
        {
            Source[Period],
            Source[Sales],
            RTCalc
        },
        type table [Period = text, Sales = number, RunningTotal = number]
    )
in
    Combine

List.Generate関数の各処理で、レコード型を使って処理を行っており、[ ] の使い方が紛らわしいのですが、複数の変数を使用して処理を行うことができます。

List.Generateを使用すると、この後説明する再帰処理をさせるよりパフォーマンスが優れています。また、コードが読みやすく、処理の追跡がしやすい特徴があります。

List.Generateの使用例として、エクセルのすべてのシートを取得する例や、Web APIで複数のページを取得するなどの例があります。

9.1.5 `List.TransformMany`

この関数は、ネストされたリスト構造を展開したり、複数のリストを組み合わせて新しいリストを生成する場合に非常に便利です。

構文

List.TransformMany(
    list as list, 
    collectionTransform as function, 
    resultTransform as function
) as list

list
操作の対象となるリスト。
collectionTransform
各リスト要素に適用される変換関数。これにより、各要素から新しいコレクション（リスト）が生成されます。
resultTransform
元の要素と新しく生成された各コレクション要素を組み合わせて、結果を作成するための関数。

この関数のキモは、resultTransformで元のリストの内容と、collectionTransformで作成された結果を組み合わせて使うところにあります。

List.TransformMany(
    {
        [Name = "Alice", Pets = {"Scruffy", "Sam"}],
        [Name = "Bob", Pets = {"Walker"}]
    },
    each [Pets],  // Petsのリストを処理対象とする
    (person, pet) => [Name = person[Name], Pet = pet] // 飼い主とペットの名前のレコードを作成
)

この例では、each [Pets]の処理では、Pets列にあるリストが処理されます。そして、(person, pet) =>では、その行全体がpersonに入り、Petsのリストの中の"Scruffy"、'"Sam"`が順番に処理されます。

let
    Source = 
        {
            [Name = "Alice", Pets = {"Scruffy", "Sam"}],
            [Name = "Bob", Pets = {"Walker"}]
        },

    // ペットの名前に"!"を付け、飼い主とペット名前が入ったレコードが並んだリストを作成
    Transform = 
        List.TransformMany(
            Source,
            each [Pets],
            (person, pet) => [Name = person[Name], Pet = pet & "!"]
        ),

    // テーブルに変換
    Table = 
        Table.FromRecords(
            Transform,
            type table [Name = text, Pet = text]
        )

in
    Table

この処理では、Petsのリストの中でペットの名前に「！」をつける処理を行い、その後に飼い主の名前とペットの名前のレコードを作成し、全体をリストにまとめています。

最後に、テーブルに変換する処理を行い、結果は以下の様になります。

9.2. テーブルのループ処理

9.2.1. Table.TransformRows

Table.TransformRowsは、テーブル内の各行に変換処理を行いリストを作成します。

let
    Source = 
        Table.FromRows(
            {
                {1, "dog"}, 
                {2, "cat"}, 
                {3, "pig"}, 
                {4, "cattle"}, 
                {5, "bird"}
            },
            type table [ID = number, Name = text]
        ),
    Transform =
        Table.TransformRows(
            Source,
            each Text.From([ID]) & Text.Reverse([Name])
        )
in
    Transform

9.2.2. Table.TransformColums

Table.TransformColumnsは、テーブルの列の値と列を変換し、データ型を変更します。

let
    Source = 
        Table.FromRows(
            {
                {1, "dog"}, 
                {2, "cat"}, 
                {3, "pig"}, 
                {4, "cattle"}, 
                {5, "bird"}
            },
            type table [ID = number, Name = text]
        ),
    Transform =
        Table.TransformColumns(
            Source,
            {
                {
                    "Name",      // 返還対象の列
                    Text.Length, // 変換処理
                    type number  // データ型を指定（オプション）
                }
            }
        )
in
    Transform

このコードで、Text.Lengthと省略形で書かれていますが、きちんと書けば each Text.Length(_) あるいは、(_)=> Text.Length(_) です。

この関数は、列の変換で他の列を参照することはできません。複数の列を使用して変換を行う場合は、次のTable.AddColumn で列の追加を行います。

9.2.3. Table.AddColumn

Table.AddColumnは、新しい列を追加し、計算結果を入れます。

let
    Source = 
        Table.FromRows(
            {
                {1, "dog"}, 
                {2, "cat"}, 
                {3, "pig"}, 
                {4, "cattle"}, 
                {5, "bird"}
            },
            type table [ID = number, Name = text]
        ),
    AddColumn =
        Table.AddColumn(
            Source,
            "New Column",  // 新しい列の名前
            each Text.From([ID]) & Text.Reverse([Name]),  // 変換処理
            type text  // データ型を指定（オプション）
        )
in
    AddColumn

9.2.4 `Table.CombineColumns`

Table.CombineColumns は、指定した複数の列の値を結合し、新しい列を作成します。この操作では、元の列は削除され、新しく作成された列がテーブルに追加されます。

構文

Table.CombineColumns(
    table as table, 
    sourceColumns as list, 
    combiner as function, 
    column as text
) as table

table: 操作対象のテーブル
sourceColumns: 結合したい列のリスト（例：{"Column1", "Column2"}）
combiner: 結合方法を定義する関数。通常は Combiner.CombineTextByDelimiter を用いて、文字列の区切り記号を指定します
newColumnName: 作成する新しい列の名前

一般的な結合

let
    Source = Table.FromRows(
        {
            {"Alice", 30, "New York"},
            {"Bob", 25, "Los Angeles"},
            {"Charlie", 35, "Chicago"}
        },
        type table [Name = text, Age = number, City = text]
    ),

    Combine = 
        Table.CombineColumns(
            Source,
            {"Name", "City"},
            Combiner.CombineTextByDelimiter(",", QuoteStyle.None),
            "Name and City"
        )
in
    Combine

`Combiner`をカスタマイズ

Combiner関数は少々特殊なようで、リストを引数として受け取らず、以下のような使われ方をします。

Combiner.CombineTextByDelimiter(
    ",", 
    QuoteStyle.None
)( { "a", "b", "c" } )

引数を使う

(x)=> や each を使って、リスト型で受け取った項目を変換します。

以下は、Combinerをカスタマイズし、数値をテキストに変換する処理を行います。

let
    Source = Table.FromRows(
        {
            {5, 3, "+"},
            {10, 2, "-"},
            {4, 6, "*"}
        },
        type table [Operand1 = number, Operand2 = number, Operator = text]
    ),

    Combine = 
        Table.CombineColumns(
            Source,
            {"Operand1", "Operator", "Operand2"},
            
            // Combinerのカスタマイズ
            each 
                Text.Combine(
                    List.Transform(
                        _, 
                        each Text.From(_)
                    ),
                    " "
                ),
                
            "Expression"
        )
in
    Combine

複雑な計算式

以下の例は、2より大きい値を「,」で区切ったテキストに変換しています。

let
    Source = Table.FromRows(
        {
            {5, 3, 8},
            {10, 2, 4},
            {4, 6, 1}
        },
        type table [Num1 = number, Num2 = number, Num3 = number]
    ),

    Combine = 
        Table.CombineColumns(
            Source,
            {"Num1", "Num2", "Num3"},
            // Combinerのカスタマイズ
            (x) => 
                let
                    // 2より大きい値のリストを作成
                    Selected = List.Select(x, each _ > 2),
                    // リストの値をテキストに変換
                    toText = 
                        List.Transform(
                            Selected,
                            each Text.From(_)
                        ),
                    // リストの中身を結合
                    Combine =
                        Text.Combine(toText, ",")
                in
                    Combine,
                
            "Numbers"
        )
in
    Combine

注意点

列の削除: 指定した列は結合後に削除され、新しい列だけが残ります。

9.3. カスタム関数を使った変換

カスタム関数を使って、年間予算のテーブルから月別の予算テーブルを作成します。

let
    // カスタム関数　予算を12か月に配分するテーブルを作成
    AnnualDistribution = (n as number) as table =>
        let 
            // 2025年1月から12月までの日付リストの作成
            MonthlyList = 
                List.Generate(
                    ()=> 1,
                    each _ <= 12,
                    each _ + 1,
                    each #date(2025,_,1)
                ), 

            // 日付リストをテーブルに変換
            CreateTable =
                Table.FromList(
                    MonthlyList,
                    Splitter.SplitByNothing(), 
                    type table [Month = date], 
                    null, 
                    ExtraValues.Error
                ),

            // 列を追加し予算を12で割った数字を入れる
            Distribution =
                Table.AddColumn(
                    CreateTable,
                    "MonthlyBudget",
                    each n / 12
                )
        in
            Distribution,

    // 支店名と年間予算のテーブル
    Source = Table.FromRows(
        {
            {"高円寺", 1000000},
            {"亀戸", 800000},
            {"品川",8000000},
            {"川崎",600000}
        },
        type table [Office = text, Budget = number]
    ),

    // 列を追加し、カスタム関数を適用
    AddMonthlyBudget =
        Table.AddColumn(
            Source,
            "Data",
            each AnnualDistribution([Budget])
        ),

    // テーブルを展開
    Expanded = 
        Table.ExpandTableColumn(
            AddMonthlyBudget, 
            "Data", 
            {"Month", "MonthlyBudget"}, 
            {"Month", "MonthlyBudget"}
        )
in
    Expanded

9.4. 再帰的な処理

M言語には様々な組み込み関数が存在しますが、階層データやネストしたオブジェクトをフラット化する必要がある場合は、再帰処理が必要になります。

しかし、再帰処理は非常に重くなるため、よりパフォーマンスが良いList.Generateの使用が推奨されています。

M言語の変数は、通常自己参照はできません。@記号は、自己参照を可能にし、再帰関数の作成をサポートします。

let
    Factorial = 
        (n) =>
            if n <= 1
                then 1
                else n * @Factorial (n - 1)
in
    Factorial(5)

上記のコードは、Factorial関数に5が与えられると、5と乗算する値をその値-1した値で自分自身を呼び出してもとめ、呼び出す値が1になるまで続けられます。
$5 \times 4 \times 3 \times 2 \times 1 = 120$
結果は120が返されます。

下の例では、氏名の間の複数のスペースを1つにまとめる処理を再帰処理で行っています。

let
    Source = Table.FromRecords(
        {
            [ID = 1, Name = "Taro Yamada"],
            [ID = 2, Name = "Shinji  Ishikawa"],
            [ID = 3, Name = "Takeo     Nakano"]
        },
        type table [ID = number, Name = text]
    ),

    // スペース2つを1つに変換を再帰させて繰り返す
    fxDeSpace = (str as text) as text =>
        let
            Replace =
                if Text.Contains(str, "  ")
                    then @fxDeSpace( Text.Replace(str, "  ", " ") )
                    else str
        in
            Replace,

    // Name列に変換処理を行う
    Result =
        Table.TransformColumns(
            Source,
            {
                {
                    "Name",
                    fxDeSpace
                }
            }
        )
in
    Result

再帰は、階乗計算や階層データのアクセスなど、再帰的な構造を持つ問題を解決するためのエレガントな方法となりえます。しかし、再帰には特にパフォーマンス面での欠点もあります。

再帰を使用すると、各再帰呼び出しがコールスタックに新しいレイヤーを生成します。コールスタックとは、プログラムがタスクを追跡するための一時的なメモリストレージです。関数が自分自身をあまりにも多く呼び出すと、スタックがオーバーフローし、クエリが失敗する可能性があります。

スタックオーバーフローを防ぐために、再帰関数の終了ロジックを必ず含めるようにしてください。また、再帰の深さに注意を払ってください。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

Power Query へそのゴマ 第９章 ループ処理と再帰処理