Power Query の リスト関数 : Mから始めよう #17

　Power Query には、文字、数値、ブール値というプリミティブ値と、リスト、レコード、テーブルという構造化された値があります。

　リストは、順序つけられた値のシーケンスであり、 "{" で始まり "}" で終了します。リストが空の場合は、 "{}" となります。リストのサイズに制限はありません。

リストの例

{1,2}

　リストは、クエリとクエリの受け渡しで使われ、最終的なデータモデルとして使用されることはほとんどありません。それでも、リストの使い方をマスターすると、できることの幅が広がると思い、まとめてみました。

連続する番号を含むリスト

　例えば、１から１００までの整数をもつリストは以下のようになります。

1から100までのリスト

{1..100}

1から100までのリストで48は除外するリスト

{1..47, 49..100}

　ただし、連続して生成できる値と個数には制限があります。数値は-2147483646から2147483647まで。個数は2,147,483,647個までです。３２ビット整数値で表現できる値を超えると下記のようにエラーとなります。

　リスト自体に個数制限はないので、以下のようなリストは作成可能です。

{-2147483646..0, 1..2147483647, 1..2147483647}

List.Numbers

List.Numbers構文

List.Numbers(
    start as number,     // 初期値
    count as number,     // 作成する値の数
    optional increment as nullable number  // 増分値
) as list

　List.Numbersを使うと、上記３２ビット整数値を超えるリストを作成可能です。

List.Numbers例

let
    Source = List.Numbers( 1, 2147483648, 1 )
in
    Source

List.Generate

　List.Generatgeは、パラメータを全て関数で記述します。

List.Generate構文

    List.Generate(
        initial as function,        // 初期関数
        condition as function,      // 条件関数
        next as function,           // 生成関数
        optional selector as nullable function  // セレクター関数
    ) as list

　初期の値を１０にし、０以上を条件とし、１づつ引いていく場合、以下のような式になります。

List.Generate例

    List.Generate(
        () => 10,        // 初期値
        each _ > 0,      // 条件式
        each _ - 1,      // 生成式
        each _           // セレクター
    ) as list

　この場合、セレクターは省略しても同じ結果を得ることができます。

List.Repeat

　与えられたリストを指定された回数繰り返すリストを作成します。

List.Repeat構文

    List.Repeat(
        list as list,
        count as number
    ) as list

List.Repeat例

    List.Repeat(
        {1,2},
        3
    )

List.Transform

リストの項目に関数を適用します。

List.Transform構文

    List.Transform(
        list as list,
        transform as function
    ) as list

List.Transform例

    List.Transform(
        {1,2,3},
        each _ * 2
    )

List.TransformMany

　List.TransformManyは、与えられたリストをcollectionTransformで処理し、resultTransformで元のリストの値とcollectionTransformで処理された値を使って処理し、その結果をリストで返します。

List.TransformMany構文

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

List.TransformMany例

List.TransformMany(
     {2019..2022},                   
     (x)   => {#date(x, 1 ,1)},  
     (x,y) => Text.From(x) &" -> "& Date.ToText( y, "yyyy/MM/dd" ))

List.Accumulate

　アキュムレータ（累算器）を使用して、listの値を順に処理していきます。

List.Accumulate構文

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

List.Accumulate例

    List.Accumulate(
        {1,2,3,4,5}, 
        0, 
        (state, current) => state + current
    ) // 15

上の式は、以下のような計算になります。

リストの結合

List.Combine

　リストの結合には、List.Combineを使用します。

List.Combine

let
    list1 = {0,1,2,3,4},
    list2 = {5,6,7,8,9},
    Source = List.Combine({list1, list2})
in
    Source

簡単に、＆で結合することもできます。

&

let
    list1 = {0,1,2,3,4},
    list2 = {5,6,7,8,9},
    Source = list1 & list2
in
    Source

List.Union

　List.Unionは、２つのリストを結合します。list1とlist2の間で共通する要素は１つになります。

List.Union

let
    list1 = {1,2,3,4,5},
    list2 = {5,6,7,8,9},
    Source = List.Union({list1, list2})
in
    Source

　list1とlist2に共通する5は、１つになります。

リストの差分

List.Difference

List.Difference構文

List.Difference(
    list1 as list, 
    list2 as list, 
    optional equationCriteria as any
) as list

　リスト{"a","b","c"}のうち、{"a","b","C"}に含まれない値をリストで返します。大文字、小文字は区別されます。

List.Difference例1

List.Difference(
    {"a","b","c"},
    {"a","b","C"}
) // {"c"}

以下のように、equationCriteriaを使用して、大文字、小文字の区別をしないよう指定すると、空のリストが返されます。

List.Difference例2

List.Difference(
    {"a","b","c"},
    {"a","b","C"},
    Comparer.OrdinalIgnoreCase
) // {}

また、リスト1に同じ値が複数あり、リスト2には1つだけある場合は、リスト1から1つだけ削除されます。

List.Difference例3

List.Difference(
    {"a","a","b","c"},
    {"a","b"}
) // {"a","c"}

equationCriteria については、「List.Containsの引数 equationCriteria を使う」を参照

List.RemoveMatchingItems

List.RemoveMatchingItems構文

List.RemoveMatchingItems(
    list1 as list, 
    list2 as list, 
    optional equationCriteria as any
) as list

リスト1からリスト2にある値をすべて削除します。List.Differenceと違い、リスト1に重複する値があっても、すべて削除されます。

List.RemoveMatchingItems例

List.Difference(
    {"a","a","b","c"},
    {"a","b"}
) // {"c"}

Text.Split と　Text.ToList

Text.Splitは、文字列中にあるセパレータで分割し、リストを作成します。

Text.Split構文

Text.Split(
    text as text, 
    separator as text
) as list

Text.Split例

Text.Split(
    "Name|Address|PhoneNumber", 
    "|"
)

Text.ToListは、文字列を1文字づつ分割してリストを作成します。

Text.ToList

Text.ToList("Hello World")

Binary.ToList

バイナリ値をリストに変換します。

Binary.ToList

Binary.ToList(Binary.FromList({1..10}))

List.First

　List.Firstでは、リストの最初の値を取り出します。

List.First

let
    list1 = {0,1,2,3,4},
    Source = List.First(list1)
in
    Source  // 0

　それ以外にも、以下のように任意の順番の値を取り出すことができます。

let
    list1 = {5,6,7,8,9},
    Source = list1{3}
in
    Source  // 8

List.Zip

リストとリストの同じ位置にある項目を結合します。リスト1とリスト2の個数が違う場合、nullになります。

List.Zip

List.Zip(
    {
        {1, 2, 3},
        {11,12}
    }
)

返される値は、以下のようなリストになります。

{
    { 1, 11 },
    { 2, 12 },
    { 3, null }
}

List.Average, List.Mode, List.Median, List.Percentile

リスト内の項目の平均、モード（最頻値）、中央値、パーセンタイルを求めます。

List.Covariance

　2つのリストの共分散を返します。

List.Intersect

　リスト値の積集合を返します。

List.Intersect構文

List.Intersect(
    lists as list, 
    optional equationCriteria as any
) as list

List.Intersect例

List.Intersect(
    {
        {1..5}, 
        {2..6}, 
        {3..7}
    }
)  // {3, 4, 5}

List.MatchesAll, List.MatchesAny

List.MatchesAllは、リスト内のすべての値で条件が満たされる場合 true を返します。
List.MatchesAnyは、リスト内のいずれかの値が条件をみたす場合 true を返します。

List.MatchesAny構文

List.MatchesAny(
    list as list, 
    condition as function
) as logical

List.MatchesAny例

List.MatchesAny(
    {9, 10, 11}, 
    each _  > 10
) // true

List.Select

リストから、条件に一致する値のリストを返します。

List.Select構文

List.Select(
    list as list, 
    selection as function
) as list

例では、"ap" を含む値をリストにして返します。大文字、小文字の区別はしません。

List.Select例

List.Select(
    {"Apple", "Orange", "Grape"},
    each 
        Text.Contains(
            _, 
            "ap", 
            Comparer.OrdinalIgnoreCase
        )
)