Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationEventAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
0
Help us understand the problem. What are the problem?

posted at

updated at

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

image.png
 今回は、M言語の中でも使いでのあるList.Generateについて書いていきます。

参照

構文

 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

 ()=>は、引数のない関数呼び出しです。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
 最後の引数で、リストに入れる値を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
 レコードを使っているため、[]の使い方が注意です。また、sample2とsample3でText.Formatの使い方の違いに小技を使っています。(補足:Text.Formatについて参照)

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

 以下のようなデータの入ったシートが5つあるエクセルブックで、すべてのシートのデータを取得してみます。
image.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
 シート番号は0から始まりますので、パラメータに0を入力して呼び出すと、以下のようにデータが取得されます。
image.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

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

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

4. ページ分割されているWebでページ数がわからないデータを取得する

 まず、Webページからデータをテーブルで取得する関数を作成し、FXWebpageとします。
 ※参照「(Power BI) Mから始めよう #8 〜クエリ・パラメータの各種使い方

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

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 について

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歳になった。
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
0
Help us understand the problem. What are the problem?