Power Query には、文字、数値、ブール値というプリミティブ値と、リスト、レコード、テーブルという構造化された値があります。
リストは、順序つけられた値のシーケンスであり、 "{" で始まり "}" で終了します。リストが空の場合は、 "{}" となります。リストのサイズに制限はありません。
{1,2}
リストは、クエリとクエリの受け渡しで使われ、最終的なデータモデルとして使用されることはほとんどありません。それでも、リストの使い方をマスターすると、できることの幅が広がると思い、まとめてみました。
連続する番号を含むリスト
例えば、１から１００までの整数をもつリストは以下のようになります。
{1..100}
{1..47, 49..100}
ただし、連続して生成できる値と個数には制限があります。数値は-2147483646から2147483647まで。個数は2,147,483,647個までです。３２ビット整数値で表現できる値を超えると下記のようにエラーとなります。
リスト自体に個数制限はないので、以下のようなリストは作成可能です。
{-2147483646..0, 1..2147483647, 1..2147483647}
List.Numbers
List.Numbers(
start as number, // 初期値
count as number, // 作成する値の数
optional increment as nullable number // 増分値
) as list
List.Numbersを使うと、上記３２ビット整数値を超えるリストを作成可能です。
let
Source = List.Numbers( 1, 2147483648, 1 )
in
Source
List.Generate
List.Generatgeは、パラメータを全て関数で記述します。
List.Generate(
initial as function, // 初期関数
condition as function, // 条件関数
next as function, // 生成関数
optional selector as nullable function // セレクター関数
) as list
初期の値を１０にし、０以上を条件とし、１づつ引いていく場合、以下のような式になります。
List.Generate(
() => 10, // 初期値
each _ > 0, // 条件式
each _ - 1, // 生成式
each _ // セレクター
) as list
この場合、セレクターは省略しても同じ結果を得ることができます。
List.Repeat
与えられたリストを指定された回数繰り返すリストを作成します。
List.Repeat(
list as list,
count as number
) as list
List.Repeat(
{1,2},
3
)
List.Transform
リストの項目に関数を適用します。
List.Transform(
list as list,
transform as function
) as list
List.Transform(
{1,2,3},
each _ * 2
)
List.TransformMany
List.TransformManyは、与えられたリストをcollectionTransformで処理し、resultTransformで元のリストの値とcollectionTransformで処理された値を使って処理し、その結果をリストで返します。
List.TransformMany(
list as list,
collectionTransform as function,
resultTransform as function
) as list
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 as list,
seed as any,
accumulator as function
) as any
List.Accumulate(
{1,2,3,4,5},
0,
(state, current) => state + current
) // 15
上の式は、以下のような計算になります。
リストの結合
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の間で共通する要素は１つになります。
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(
list1 as list,
list2 as list,
optional equationCriteria as any
) as list
リスト{"a","b","c"}のうち、{"a","b","C"}に含まれない値をリストで返します。大文字、小文字は区別されます。
List.Difference(
{"a","b","c"},
{"a","b","C"}
) // {"c"}
以下のように、equationCriteriaを使用して、大文字、小文字の区別をしないよう指定すると、空のリストが返されます。
List.Difference(
{"a","b","c"},
{"a","b","C"},
Comparer.OrdinalIgnoreCase
) // {}
また、リスト1に同じ値が複数あり、リスト2には1つだけある場合は、リスト1から1つだけ削除されます。
List.Difference(
{"a","a","b","c"},
{"a","b"}
) // {"a","c"}
equationCriteria については、「List.Containsの引数 equationCriteria を使う」を参照
List.RemoveMatchingItems
List.RemoveMatchingItems(
list1 as list,
list2 as list,
optional equationCriteria as any
) as list
リスト1からリスト2にある値をすべて削除します。List.Differenceと違い、リスト1に重複する値があっても、すべて削除されます。
List.Difference(
{"a","a","b","c"},
{"a","b"}
) // {"c"}
Text.Split と Text.ToList
Text.Splitは、文字列中にあるセパレータで分割し、リストを作成します。
Text.Split(
text as text,
separator as text
) as list
Text.Split(
"Name|Address|PhoneNumber",
"|"
)
Text.ToListは、文字列を1文字づつ分割してリストを作成します。
Text.ToList("Hello World")
Binary.ToList
バイナリ値をリストに変換します。
Binary.ToList(Binary.FromList({1..10}))
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(
{
{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(
lists as list,
optional equationCriteria as any
) as list
List.Intersect(
{
{1..5},
{2..6},
{3..7}
}
) // {3, 4, 5}
List.MatchesAll, List.MatchesAny
List.MatchesAllは、リスト内のすべての値で条件が満たされる場合 true を返します。
List.MatchesAnyは、リスト内のいずれかの値が条件をみたす場合 true を返します。
List.MatchesAny(
list as list,
condition as function
) as logical
List.MatchesAny(
{9, 10, 11},
each _ > 10
) // true
List.Select
リストから、条件に一致する値のリストを返します。
List.Select(
list as list,
selection as function
) as list
例では、"ap" を含む値をリストにして返します。大文字、小文字の区別はしません。
List.Select(
{"Apple", "Orange", "Grape"},
each
Text.Contains(
_,
"ap",
Comparer.OrdinalIgnoreCase
)
)
