PowerQuery ループ処理を実現！List.Generate関数の使い方解説

PowerQueryでList.Generate関数を利用した繰り返し処理（ループ処理）の方法を紹介します。

まずPower Queryにはfor文やwhile文のようなループ構文はありません。

しかし、List.Generate を使うことで「繰り返し処理」や「前回の結果を次に使う」ような処理ができるようになります。

基本の使い方やfor文やwhile文の代替方法などを例を使って解説していきます。

List.Generate関数とは？

List.Generate関数の機能は「条件付きで繰り返し処理を行い、リストを作る」ことです。

まず基本構文と使い方の例を説明します。

基本構文

基本の構文は下記の通りです。

初期値と条件を設定することで条件をクリアするまで処理が繰り返されます。

List.Generate(
    () => 初期値,
    each 条件式,
    each 繰り返す処理,
    each 出力値
)

項目	説明
初期値	最初の状態
条件式	繰り返しを続ける条件
繰り返す処理	処理内容
出力値	実際にリストに追加する値

基本的な使い方の例

それでは例を使って基本的な使い方を解説します。

変数が1つの場合

まずは一つの変数のみを使って繰り返し処理を行う場合です。

下記の例は、初期値の1から始めて5になるまで1ずつ足していく処理になります。出力値は5になるまでのaの値のリストとなります。

List.Generate(
    () => a = 1,
    each a <= 5,
    each a + 1
    each a
)

→ 結果：{1, 2, 3, 4, 5}

変数が複数の場合

次に複数の変数を使って繰り返し処理を行う場合です。

下記の式は変数 a と b の2つを利用して繰り返し処理を行う例です。出力値は100以下のフィボナッチ数列のリストとなります。

List.Generate(
    () => [a = 0, b = 1],
    each [a] <= 100,
    each [a = [b], b = [a] + [b]],
    each [a]
)

→ 結果：{0, 1, 1, 2, 3, 5, 8, 13, 21, 34, 55, 89}

列データを利用する場合

List.Generate関数の初期値や条件式にテーブルにある列データを利用する場合は、下記のように記載するのがオススメです。

List.Generate関数で使用する前に列データを変数に一度代入することでエラーが発生しにくくなります。

let
    a0 = [Column1],               // 変数a0に列Aの値を代入
    b0 = [Column2],               // 変数b0に列Bの値を代入
    list = List.Generate(
        () => [a = a0, b = b0],         // 初期値
        each [a] <= 100,                // 繰り返し条件
        each [a = [b], b = [a] + [b]],  // 次の状態
        each [a]                        // 出力値
    )
in 
    list                                 //リストデータを出力

let ～ inの式についてはこちらで説明していますので参考にしてみてください。

テーブルのデータにループ処理を適用する

List.Generate関数をテーブルや各行データに対してループ処理を利用する方法を紹介します。

ここでは例として以下のStart列とEnd列があるテーブルにおいて、List.Generate関数を適用して、NumberList列にStartからEndまでのリスト（コンマ区切り）を出力する手順を解説します。

STEP

テーブルを準備する

まずは元データとなるテーブルを用意します。

ここではStart列とEnd列があるテーブルとします。

STEP

新しいステップを追加する

一番下のステップを右クリックして「後にステップの挿入」を選択する

STEP

ループ処理の式を数式バーに入力

追加されたステップに式を入力する。

今回は列データをList.Generate関数内で利用しているためこちらの式を使います。

使用する関数とそれぞれの部分の意味は以下の通りです。

Table.AddColumn新しい列を追加する
each各行に対して処理を行う
let ～ in複数の処理をひとつにまとめる
List.Generate条件付きでリストを作る（ループ処理）

Table.AddColumn(
    Source,               //元テーブル
    "NumberList",　　　　　//新しく追加する列の名前
    each 
        let 
            Start = [Column1],     //変数Startに列データを代入
            End   = [Column2],     //変数Endに列データを代入
            list =List.Generate(
            () => [x = Start],     //初期値
            each [x] <= End,       //繰り返し条件
            each [x = [x] + 1],    //繰り返す処理
            each [x]               //出力値
            )
        in
            list
)

ドノ

列データを使わない場合はeachのあとにList.Generate関数だけを記載してください（let … inなどは不要）

STEP

式内の各引数を設定する

式内のそれぞれの項目を状況に合うように設定しなおします。

元テーブル：変更された型
列データ：Start
列データ：End

元テーブル名は現ステップの一つ上にあるステップの名前を入力すればOK

STEP

リストデータの展開

最後にリストデータをテーブルで利用しやすくするために展開します。

列の右上にあるボタンをクリックして「値を抽出する」を選択します。

展開時の区切り文字を選びます。ここではコンマとしました。

展開した結果はこちらです。

ドノ

これでテーブルへの適用は完了です。条件や繰り返す処理などを変更して利用してみてください。次は出力としてリストから値を取り出す方法を解説します。

リスト型データの利用方法

ループ処理をした出力値がリストデータのままだと、その後の利用がしづらいと思います。

ここではリスト型データから値を利用しやすく取り出す方法を紹介します。

列に展開

リスト型データを列に展開するには下記の手順で行います。

リスト型データをコンマ区切りで抽出
列の分割で「区切り記号による分割」で列に展開

下図の例だと、コンマ区切りで展開したNumberList列を選択したあと「列の分割」→「区切り記号による分割」を選択します。

区切り文字でコンマを選択すれば下図のようにリストデータを列に展開できます。

行に展開

リスト型データを行に展開するには列の右上にあるボタンをクリックして「新しい行に展開する」を選択します。

そうすると下図のように展開されます。リスト型データ以外の列データはコピーされて複製されます。

リストの最後の値

リストの最後の値を取り出すときはList.Last関数を用います。

List.Generate関数をList.Last関数で囲うように記載すればOKです。

List.Last(
    List.Generate(
        () => 初期値,
        each 条件式,
        each 繰り返す処理,
        each 出力値
    )
)

リストの最大値・最小値・平均値・項目数

リストの最大値・最小値・平均値などの値を取り出すときは、それぞれ以下の関数を用います

最大値：List.Max
最小値：List.Min
平均値：List.Average
リストの項目数：List.Count

最大値の場合は下記のように記載します。

List.Max(
    List.Generate(
        () => 初期値,
        each 条件式,
        each 繰り返す処理,
        each 出力値
    )
)

よくあるエラーと対処方法

よくあるエラーとしては下記のようなものがあります。

エラー内容	原因	対処法
無限ループ	終了条件が常にtrue	条件式を見直す
Expression.Error: レコードのフィールド’x’が見つかりませんでした。	状態のrecord名のミス	`[x]` のスペルを確認
型エラー	出力値の型が混在	`Number.From` や `Text.From` で統一