今回は、M言語の中でも使いでのあるList.Generateについて書いていきます。
参照
- Microsoft Docs, List.Generate, Doug Klopfenstein, 2021/05/14
- Youtube, List.Generate() Function and Looping in PowerQuery, Exceed Learning, 2020/10/22
- Qiita, Power Query の List.Generate 関数ってなんだよー, Takeshi Kagata, 2019/3/9
構文
List.Generateは、以下の構文になります。
List.Generate(
initial as function,
condition as function,
next as function,
optional selector as nullable function
) as list
引数はすべてfunctionです。
1. 簡単なサンプル
以下の例は、開始の値が1で、10以下の場合は1を加算していくリストを作成します。
let
sample1 = List.Generate(
()=>1, // 開始の値
each _ <= 10, // 条件式
each _ + 1 // 処理内容
)
in
sample1
()=> は、引数のない関数呼び出しです。2つ目と3つ目の引数では、_ を使ってその値にアクセスすることができます。each _ <= 10** は、**(_)=> _ <= 10 と書くこともできます。
以下は、4つ目の引数を指定した例です。繰り返しで使用される値は、1から10まで1ずつ加算されていきます。
List.Generate(
()=> 1, // 開始の値
each _ <= 10, // 条件式
each _ + 1, // 処理内容
each Text.Format("Value is #{0}", {_}) // リストに入れる値
)
最後の引数で、リストに入れる値をText.Formatで加工して出力していますが、引数を省略した場合は、sample1で見たように**each _**と同じ動きになります。
2. 2つの値を使う
次に、レコードを使ってxとyという2つの値の処理を行う例を示します。
List.Generate(
() => [x = 1, y = 10],
each [x] < 10,
each [x = [x] + 1, y = [y] + 10],
each Text.Format("x = #[x]. y = #[y].", _)
)
レコードを使っているため、[]の使い方が注意です。また、sample2とsample3でText.Formatの使い方の違いに小技を使っています。(補足：Text.Formatについて参照）
3. エクセルのいくつあるかわからないシートのデータを追加する
※こちらも参照ください。「(Power BI) Mから始めよう #11 Excel 複数のブックの複数のシートのデータを取り出す」
以下のようなデータの入ったシートが５つあるエクセルブックで、すべてのシートのデータを取得してみます。
まず、普通にマウス操作でエクセルブックをソースとして開くクエリを作ります。
let
ソース = Excel.Workbook(File.Contents("D:\work\samplebook.xlsx"), null, true),
Sheet1_Sheet = ソース{[Item="Sheet1",Kind="Sheet"]}[Data],
昇格されたヘッダー数 = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"col1", type text}, {"col2", Int64.Type}})
in
変更された型
これをシート番号を引数にしてデータを取得する関数に変換します。変換方法はクエリを右クリックして「関数の作成」で作る方法もありますが、ここは詳細エディターで書き換えることにします。また、このままだとシート名で開くことになるので、シート名の番号が途中が削除されていたり、変更されていた場合にうまく動かないので、シート番号で呼び出すように変更します。
(SheetNumber as number) as table =>
let
ソース = Excel.Workbook(File.Contents("D:\work\samplebook.xlsx"), null, true),
Sheet1_Sheet = ソース{SheetNumber}[Data], // シートを名前でなく番号で呼び出すように変更
昇格されたヘッダー数 = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"col1", type text}, {"col2", Int64.Type}})
in
変更された型
これで、以下のような関数が出来上がります。
シート番号は０から始まりますので、パラメータに0を入力して呼び出すと、以下のようにデータが取得されます。
次に、List.Generate関数を使ったクエリを作成します。シートがそれ以上ない場合は、tryによってエラーを検知し、Resultの値はnullになるように設定します。
let
Source =
List.Generate(
()=> [Sheet = 0, Result = try FXSheet(Sheet) otherwise null], // エラーになったらResultはnull
each [Result] <> null,
each [Sheet = [Sheet] + 1, Result = try FXSheet([Sheet] + 1) otherwise null],
each [Result]
)
in
Source
このクエリを実行させると、以下のようになります。
これをテーブルに変換、テーブルカラムの展開と操作すると、結合した表が取得できます。
let
Source =
List.Generate(
()=> [Sheet = 0, Result = try FXSheet(Sheet) otherwise null], // エラーになったらResultはnull
each [Result] <> null,
each [Sheet = [Sheet] + 1, Result = try FXSheet([Sheet] + 1) otherwise null],
each [Result]
),
テーブルに変換済み = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"展開された Column1" = Table.ExpandTableColumn(テーブルに変換済み, "Column1", {"col1", "col2"}, {"col1", "col2"})
in
#"展開された Column1"
4. ページ分割されているWebでページ数がわからないデータを取得する
まず、Webページからデータをテーブルで取得する関数を作成し、FXWebpageとします。
※参照「(Power BI) Mから始めよう #8 〜クエリ・パラメータの各種使い方」
それを呼び出すクエリを以下に示します。
let
Source = List.Generate(
() =>
[
Page = 1,
Result =
try Function.InvokeAfter(
()=> FXWebpage(Page), // Webからデータ取得
#duration(0,0,0,1) // 遅延1秒
)
otherwise null // エラーの場合はResultにnull
],
each [Result] <> null and [Page] <= 10, // エラーとなるか10ページまでで終了
each
[
Page = [Page] + 1,
Result =
try Function.InvokeAfter(
()=> FXWebpage([Page] + 1), // Webからデータ取得
#duration(0,0,0,1) // 遅延1秒
)
otherwise null // エラーの場合はResultにnull
],
each [Result]
),
テーブルに変換済み = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"展開された Column1" = Table.ExpandTableColumn(テーブルに変換済み, "Column1", {"店名", "住所", "URL"}, {"店名", "住所", "URL"})
in
#"展開された Column1"
複数のWebページにアクセスる場合は、Function.InvokeAfterで遅延時間を入れるようにしましょう。また、最後のページを超えてもエラーとならないサイトもありますので、終了ページ数を指定しておく方が安全です。
補足：Text.Format について
- Microsoft Docs, Text.Format, Doug Klopfenstein, 2021/5/14
Text.Format(
Text.Format(
formatString as text, // 書式設定文字列
arguments as any, // リストまたはレコード
optional culture as nullable text // （オプション）カルチャ "ja-JP"や"en-US"など
) as text
Text.Format(
"#{0} turned #{1} on #{2}.",
{"Taro", 17, #date(2021,11,20)},
"en-US"
)
// Taro turned 17 on 11/20/2021.
Text.Format(
"#[name]は#[birthday]で#[age]歳になった。",
[
name ="Taro",
age =17,
birthday = #date(2021,11,20)
],
"ja-JP"
)
// Taroは2021/11/20で17歳になった。