(Power BI) Mから始めよう #9 List.Generate ページ数がわからなくてもデータ取得

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/34ce60ec-8462-5c99-3b6c-5c7738fcea1a.png)
　今回は、M言語の中でも使いでのあるList.Generateについて書いていきます。

### 参照
- Microsoft Docs, List.Generate, Doug Klopfenstein, 2021/05/14
    - https://docs.microsoft.com/ja-jp/powerquery-m/list-generate
- Youtube, List.Generate() Function and Looping in PowerQuery, Exceed Learning, 2020/10/22
    - https://www.youtube.com/watch?v=mXbjoSg9ldo
- Qiita, Power Query の List.Generate 関数ってなんだよー, Takeshi Kagata, 2019/3/9
    - https://qiita.com/PowerBIxyz/items/ed6ebb547a9157561ad1

# 構文
　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を加算していくリストを作成します。

```:sample1
let
    sample1 = List.Generate(
    ()=>1,          // 開始の値
    each _ <= 10,   // 条件式
    each _ + 1      // 処理内容
)
in
    sample1
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/03c345ab-0942-04bd-362a-dd8ec01461b1.png)

　**()=>** は、引数のない関数呼び出しです。2つ目と3つ目の引数では、**_** を使ってその値にアクセスすることができます。**each _ <= 10** は、**(_)=> _ <= 10** と書くこともできます。
　以下は、4つ目の引数を指定した例です。繰り返しで使用される値は、1から10まで1ずつ加算されていきます。

```:sample2
List.Generate(
    ()=> 1,       // 開始の値
    each _ <= 10, // 条件式
    each _ + 1,   // 処理内容
    each Text.Format("Value is #{0}", {_})    // リストに入れる値
)
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/61c67067-d22f-0929-4253-370bced03bc1.png)
　最後の引数で、リストに入れる値をText.Formatで加工して出力していますが、引数を省略した場合は、sample1で見たように**each _**と同じ動きになります。

# 2. 2つの値を使う
　次に、レコードを使ってxとyという2つの値の処理を行う例を示します。

```:sample3
List.Generate(
    () => [x = 1, y = 10],
    each [x] < 10,
    each [x = [x] + 1, y = [y] + 10],
    each Text.Format("x = #[x].  y = #[y].", _)
)
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/1c387df0-d314-45bb-77e3-24e3157bedef.png)
　レコードを使っているため、[]の使い方が注意です。また、sample2とsample3でText.Formatの使い方の違いに小技を使っています。([補足：Text.Formatについて](https://qiita.com/drafts#%E8%A3%9C%E8%B6%B3textformat-%E3%81%AB%E3%81%A4%E3%81%84%E3%81%A6)参照）

# 3. エクセルのいくつあるかわからないシートのデータを追加する

 ※こちらも参照ください。「**(Power BI) Mから始めよう #11 Excel 複数のブックの複数のシートのデータを取り出す**」


　以下のようなデータの入ったシートが５つあるエクセルブックで、すべてのシートのデータを取得してみます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/46f2e9ae-0236-d66f-bf21-e168ab4cb03a.png)

　まず、普通にマウス操作でエクセルブックをソースとして開くクエリを作ります。

```
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
    変更された型
```

　これをシート番号を引数にしてデータを取得する関数に変換します。変換方法はクエリを右クリックして「関数の作成」で作る方法もありますが、ここは詳細エディターで書き換えることにします。また、このままだとシート名で開くことになるので、シート名の番号が途中が削除されていたり、変更されていた場合にうまく動かないので、シート番号で呼び出すように変更します。

```:FXSheet
(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
    変更された型
```

　これで、以下のような関数が出来上がります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/fc8dda2b-324b-e49d-6dd7-69b22effad01.png)
　シート番号は０から始まりますので、パラメータに0を入力して呼び出すと、以下のようにデータが取得されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/eb387504-3355-7500-e064-3ba904f13b5c.png)

　次に、List.Generate関数を使ったクエリを作成します。シートがそれ以上ない場合は、tryによってエラーを検知し、Resultの値はnullになるように設定します。

```:sample4
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
```

　このクエリを実行させると、以下のようになります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/07ef7c28-73a7-5b1f-4540-b4e80c7f13aa.png)

 これをテーブルに変換、テーブルカラムの展開と操作すると、結合した表が取得できます。

```:sample4
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"
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/864075/47b53c05-cca4-0745-6517-c96f394ab5fd.png)

# 4. ページ分割されているWebでページ数がわからないデータを取得する
　まず、Webページからデータをテーブルで取得する関数を作成し、**FXWebpage**とします。
　※参照「[(Power BI) Mから始めよう #8 〜クエリ・パラメータの各種使い方](https://qiita.com/spumoni/items/4badf29d0b3e170fa15b#b%E3%82%AB%E3%82%B9%E3%82%BF%E3%83%A0%E9%96%A2%E6%95%B0%E3%81%A8url%E3%83%91%E3%83%A9%E3%83%A1%E3%83%BC%E3%82%BF%E3%81%A7web%E3%82%B9%E3%82%AF%E3%83%AC%E3%82%A4%E3%83%94%E3%83%B3%E3%82%B0)」

　それを呼び出すクエリを以下に示します。

```:sample5
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
    - https://docs.microsoft.com/ja-jp/powerquery-m/text-format)

```:Text.Format構文
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歳になった。
```

# 関連記事

- [Mから始めよう #5 〜Power BIでWebコンテンツの利用と個人用ゲートウェイ〜](https://qiita.com/spumoni/items/f66bb43f0c3f13308d79)
- [[Power BI] Power Query の Web.Contents 関数でREST APIを使う](https://qiita.com/spumoni/items/419ffd1f07947a07cad0)
- [(Power BI) Mから始めよう #8 〜クエリ・パラメータの各種使い方](https://qiita.com/spumoni/items/4badf29d0b3e170fa15b#b%E3%82%AB%E3%82%B9%E3%82%BF%E3%83%A0%E9%96%A2%E6%95%B0%E3%81%A8url%E3%83%91%E3%83%A9%E3%83%A1%E3%83%BC%E3%82%BF%E3%81%A7web%E3%82%B9%E3%82%AF%E3%83%AC%E3%82%A4%E3%83%94%E3%83%B3%E3%82%B0)
- [ページ分割されているWebでページ数がわからないデータを取得する](https://qiita.com/spumoni/items/2832b63fa627b20a5de2#4-%E3%83%9A%E3%83%BC%E3%82%B8%E5%88%86%E5%89%B2%E3%81%95%E3%82%8C%E3%81%A6%E3%81%84%E3%82%8Bweb%E3%81%A7%E3%83%9A%E3%83%BC%E3%82%B8%E6%95%B0%E3%81%8C%E3%82%8F%E3%81%8B%E3%82%89%E3%81%AA%E3%81%84%E3%83%87%E3%83%BC%E3%82%BF%E3%82%92%E5%8F%96%E5%BE%97%E3%81%99%E3%82%8B)
- [(Power BI) Mから始めよう #10 エラー処理とHTTPステータスコード](https://qiita.com/spumoni/items/cd5843e0104fed9b4874)
- [[Power BI] Power QueryでCSSセレクタを使ってWebページからデータを取得する](https://qiita.com/spumoni/items/cad005c1deaa522531d8)