例えば「1,2,3,5,6,8,10,11,12,17」みたいな書き方だと分かりにくいし、文字数も多くなる。「1-3,5-6,8,10-12,17」みたいにまとめたい時、どうすればいいか。いや数式でやってもいいんだけど(やる人いるかな)、あえてクエリでやる場合どうするかを考えてみる。
単純に並んだ数字を1つの文字列にまとめるだけなら、Text.Combineを使えばいい。ただ連続している数字はまとめて表示したい場合は少し面倒になる。
いきなりやると分かりにくいだろうから画像のようなリストで考えてみる。
数値リスト = {1, 2, 3, 5, 6, 8, 10, 11, 12, 17}
こういうリストがあるとして、変換結果が「1-3」「5-6」「8」「10-12」「17」と並ぶようにすればいい。
まずは数値リストの最小値から最大値までの連番リストを作成する。
最小値 = List.Min(数値リスト),
最小値 = List.Min(数値リスト),
連番 = {最小値..最大値}
これで「1」「2」「3」……「15」「16」「17」と並んだ連番ができる。
つづいて数値リストと連番の相違リストを作る
相違 = List.Difference(連番, 数値リスト)
結果として、一致しない値「4」「7」「9」「13」「14」「15」「16」が得られる。
これを加工して「1」「5」「8」「10」「17」と「3」「6」「8」「12」「17」の2つのリストを作る。
から = {最小値}
& List.Intersect({List.Transform(相違, each _+1), 数値リスト}),
まで = List.Intersect({List.Transform(相違, each _-1), 数値リスト})
& {最大値}
これを一つのリストにまとめれば準備完了。
結合 = List.Zip({から, まで})
あとはこれを文字列に加工して、テーブルに変換すればおしまい。
リスト編集 = List.Transform(リスト結合, each [
条件 = _{0}=_{1},
結合 = Text.From(_{0}) & "-" & Text.From(_{1}),
結果 = if 条件 then Text.From(_{0}) else 結合
][結果]),
テーブル変換 = Table.FromColumns({リスト編集}, type table [変換=text])
「から」と「まで」の値が同じ時はそのまま表示、そうでなければ「?-?」に。1つにまとめるなら「Text.Comibne(リスト編集, ",")」みたいな形にすればいい。
まとめ
let
数値リスト = {1, 2, 3, 5, 6, 8, 10, 11, 12, 17},
リスト結合 = [
最小値 = List.Min(数値リスト),
最大値 = List.Max(数値リスト),
連番 = {最小値..最大値},
相違 = List.Difference(連番, 数値リスト),
から = {最小値}
& List.Intersect({List.Transform(相違, each _+1), 数値リスト}),
まで = List.Intersect({List.Transform(相違, each _-1), 数値リスト})
& {最大値},
結合 = List.Zip({から, まで})
][結合],
リスト編集 = List.Transform(リスト結合, each [
条件 = _{0}=_{1},
結合 = Text.From(_{0}) & "-" & Text.From(_{1}),
結果 = if 条件 then Text.From(_{0}) else 結合
][結果]),
テーブル変換 = Table.FromColumns({リスト編集}, type table [変換=text])
in
テーブル変換
こうなる。