クエリでグループ分けをする時に注意したいこと。
左の表から右の表に組み換えしたい場合、よくあるやり方は「グループ化→インデックス列追加→展開して列のピボット」という流れ。
パターンA
let
分類テーブル = [
ブック = Excel.CurrentWorkbook(),
ソース = ブック{[Name="分類テーブル"]}[Content],
型の変更 = Table.TransformColumnTypes(ソース, {
{"氏名", type text}, {"分類", type text}
})
][型の変更],
行のグループ化 = Table.Group(
分類テーブル, {"分類"},
{"グループ",
each Table.AddIndexColumn(_, "番号", 1, 1),
type table [氏名=text]
}
),
グループ列の展開 = Table.ExpandTableColumn(
行のグループ化, "グループ", {"氏名", "番号"}
),
列のピボット = Table.Pivot(
グループ列の展開,
List.Distinct(グループ列の展開[分類]),
"分類", "氏名"
),
番号列の削除 = Table.RemoveColumns(
列のピボット, {"番号"}
)
in
番号列の削除これでもちろんいいんだけど、行列が逆のパターンでやると縦の順番がズレることがある。
こうなって欲しい場合。同じく列のピボットでやってみる。「グループ列の展開」までは先程と同じ。
パターンB
氏名番号列の追加 = Table.AddColumn(
グループ列の展開, "氏名番号", each
Number.ToText([番号], "氏名\.0"), Text.Type
),
番号列の削除 = Table.RemoveColumns(
氏名番号列の追加, {"番号"}
),
列のピボット = Table.Pivot(
番号列の削除,
List.Distinct(番号列の削除[氏名番号]),
"氏名番号", "氏名"
)
このやり方だと、列のピボットを実行したところで縦の順番がずれてしまう。もちろん先に連番を振っておいてソートするとか、分類テーブルを用意しておいてそっちにマージして展開するとかすればいいけど若干めんどくさい。
というわけで、List.Generateでやってみる。
パターンA
let
分類テーブル = [
ブック = Excel.CurrentWorkbook(),
ソース = ブック{[Name="分類テーブル"]}[Content],
型の変更 = Table.TransformColumnTypes(ソース, {
{"氏名", type text}, {"分類", type text}
})
][型の変更],
行のグループ化 = Table.Group(
分類テーブル, {"分類"}, {"グループ", each _[氏名]}
),
分類リスト = List.Buffer(行のグループ化[分類]),
グループリスト = List.Buffer(行のグループ化[グループ]),
リスト生成 = List.Generate(
()=>[i=0, tbl=#table(0, {})],
each [i]<=List.Count(分類リスト),
each [
i=[i]+1,
列=Table.ToColumns([tbl]) & {グループリスト{[i]}},
見出し=Table.ColumnNames([tbl]) & {分類リスト{[i]}},
tbl= Table.FromColumns(列, 見出し)
],
each [tbl]
),
テーブル選択 = List.Reverse(リスト生成){0}
in
テーブル選択同じく逆パターン。「行のグループ化」までは同じ。
パターンB
分類件数 = List.Max(Table.AddColumn(行のグループ化, "件数",
each List.Count([グループ]))[件数]
),
リスト生成 = List.Generate(
()=>[i=0, tbl=行のグループ化],
each [i]<=分類件数,
each [
i=[i]+1,
見出し=Number.ToText(i, "氏名\.0"),
tbl= Table.AddColumn([tbl], 見出し, each
try [グループ]{i-1} otherwise null, Text.Type
)
],
each [tbl]
),
テーブル選択 = List.Reverse(リスト生成){0},
列の削除 = Table.RemoveColumns(テーブル選択,{"グループ"})いっそ、グループ化の際に全部やってしまうのもありかも？
let
分類テーブル = [
ブック = Excel.CurrentWorkbook(),
ソース = ブック{[Name="分類テーブル"]}[Content],
型の変更 = Table.TransformColumnTypes(ソース, {
{"氏名", type text}, {"分類", type text}
})
][型の変更],
行のグループ化 = Table.Group(分類テーブル, {"分類"}, {
"グループ", each [
連番 = List.Positions([氏名]),
見出し = List.Transform(連番, each Number.ToText(_+1, "氏名\.0")),
テーブル変換 = #table({"分類"} & 見出し, {{[分類]{0}} & [氏名]})
][テーブル変換]
}),
グループ列の選択 = Table.SelectColumns(行のグループ化,{"グループ"}),
見出し = [
グループ列の編集 =Table.TransformColumns(
グループ列の選択, {"グループ", Table.ColumnNames}
),
リスト化 = List.Distinct(List.Union(グループ列の編集[グループ]))
][リスト化],
型リスト = List.Transform(見出し, each {_, type text}),
グループ列の展開 = Table.ExpandTableColumn(
グループ列の選択, "グループ", 見出し
),
型の変更 = Table.TransformColumnTypes(グループ列の展開, 型リスト)
in
型の変更かえってめんどくさくなった。やっぱり List.GenerateのパターンAが良さそう。
