テーブルをランダムに並び替える方法は、前にやったんだけども、今回はリストでやってみる。
まずはリストを用意する。
リスト1 = {"A".."Z"},ここから同じ行数の乱数リストを作る。
乱数リスト = List.Random(List.Count(リスト1)),２つのリストをまとめて List.Sortをかける。
ランダムソート = List.Sort(
List.Zip({乱数リスト, リスト1}),
(x,y)=>Value.Compare(x{0}, y{0})
),乱数リストを取り除く。
リスト2 = List.Zip(ランダムソート){1},テーブルに変換するなら以下の通り。
テーブル変換 = Table.FromColumns(
{リスト2}, type table [列1=text]
)以上。
２つのテーブルを比較して変更箇所をチェックしたい時、ぱっと思い付くのは条件付き書式で変更箇所に色を入れる方法だろうか。条件付き書式は同じブック内でないと比較できないが、COMアドインの「Inquire」をアドイン（Excel2013以上）してやれば「ファイルの比較」で別ブックと比較して変更箇所に色を付けることも一応できる。
ただデータが大量にあると、「色が付く」だけでは追い付かない時がある。「削除されたもの」「追加されたもの」「変更されたもの」をどこかに抜き出すとなったら、やはりクエリの出番だろう。
こういう表（もちろんダミー情報）から変更箇所を分類して、下のようなテーブルを作ってみる。
まずは「削除されたもの」を抜き出す。
削除テーブル = [ 列のマージ = Table.NestedJoin( テーブル1, {"氏名"}, テーブル2, {"氏名"}, "備考", JoinKind.LeftAnti ), 列の編集 = Table.TransformColumns(列のマージ, {"備考", each "削除"}) ][列の編集],
次に「追加されたもの」を抜き出す。
追加テーブル = [ 列のマージ = Table.NestedJoin( テーブル2, {"氏名"}, テーブル1, {"氏名"}, "備考", JoinKind.LeftAnti ), 列の編集 = Table.TransformColumns(列のマージ, {"備考", each "追加"}) ][列の編集],
最後に「変更されたもの」を抜き出す。
変更テーブル = [ 列のマージ = Table.NestedJoin( テーブル1, {"氏名"}, テーブル2, {"氏名"}, "備考", JoinKind.LeftOuter ), 行の抽出 = Table.SelectRows( 列のマージ, each [ リスト1 = List.Range(Record.FieldValues(_), 0, 3), リスト2 = Record.FieldValues([備考]{0}), 条件 = try リスト1<>リスト2 otherwise false ][条件] ), 列の編集 = Table.TransformColumns(行の抽出, {"備考", each "住所変更"}) ][列の編集],
この３つのテーブルを縦結合すればおしまい。
テーブル結合 = 削除テーブル & 変更テーブル & 追加テーブル結合については、Table.Combineを使っても問題なし。
今回は「テーブルの組み換えＡ」の完全な逆パターン。
こういう縦並びのデータを組み換えて、
グループ別で横並びに組み替える。
ただ横並びにするだけなら「行のグループ化＋連番追加＆列のピボット」で済む話なんだけど、「品名」と「数量」のように複数の列が１セットになっている場合は、そう簡単にいかない。
というのも、グループ化の操作で「集計しない」を選択するとテーブルが返るんだけれども、このテーブルを展開（Table.ExpandTableColumn）しても縦方向にしか並ばない（要はグループ化する前に戻る）。横に展開したい時は、グループ化の際に一工夫が必要になる。
行のグループ化 = Table.Group(テーブル1, {"氏名"}, {"テーブル", each [ 列の選択 = Table.SelectColumns(_, {"品目", "数量"}), 行列の入替 = Table.Transpose(列の選択), 列のピボット解除 = Table.UnpivotOtherColumns(列の選択, {}, "属性", "値"), 値リスト = Table.Transpose(列のピボット解除){1} ][値リスト], type record}),
グループ化の結果を加工してレコードを作ってやる。この列を展開（Table.ExpandRecordColumn）すると……
こんな感じで横に並んでくれる。
見出し列がいらないならこれでいいんだけど、「品名1」「数量1」「品名2」「数量2」……となって欲しい場合は、見出しを作ってから展開と列名変更が必要。
旧列名リスト = [ リスト = 行のグループ化[テーブル], 編集 = List.Transform(リスト, each Record.FieldNames(_)), 結合 = List.Combine(編集), 重複削除 = List.Distinct(結合) ][重複削除], 新列名リスト = [ リスト = {1..List.Count(旧列名リスト)/2}, 編集 = List.Transform(リスト, each { Number.ToText(_, "品名0"), Number.ToText(_, "数量0") }), 結合 = List.Combine(編集) ][結合], 列の展開 = Table.ExpandRecordColumn( 行のグループ化, "テーブル", 旧列名リスト ), 列名の変更 = Table.RenameColumns( 列の展開, List.Zip({旧列名リスト, 新列名リスト}) )
ここまでしておくと、読み込み結果が分かりやすくなる。
入出力の区別がない表の場合、一覧表形式になっていることが多い。そしてそれがシート単位で増幅されていくという悪循環。
それは置いといて、これを列ごとに項目がまとまったテーブル情報に組み換えしたい時。横並びのデータが１種類だったら列のピボット解除一本で解決できるのだが、２列一組や３列一組となってくると単純にはいかない。
例えば、最初の一列だけが例外の２列一組のテーブル（テーブル1）だった場合どうするか。
まずは左端の「氏名」列以外を選択して列のピボット解除を実行。
列のピボット解除 = Table.UnpivotOtherColumns( テーブル1, {"氏名"}, "属性", "値" ),
次に属性のいらない部分を列の分割で取り除く。今回は後ろの数字だけ除去する。
列の分割 = Table.SplitColumn( 列のピボット解除, "属性", Splitter.SplitTextByCharacterTransition( (c) => not List.Contains({"0".."9"}, c), {"0".."9"} ), {"属性"} ),
続いて例によって行のグループ化＋連番列挿入。
行のグループ化 = Table.Group(列の分割, {"氏名", "属性"}, { "テーブル", each Table.AddIndexColumn(_, "連番", 1, 1), type table [氏名=text, 属性=text, 連番=number] }),
テーブル列以外を削除してから、列を展開。
不要列の削除 = Table.RemoveColumns(
行のグループ化,{"氏名", "属性"}
),
テーブル列の展開 = Table.ExpandTableColumn(
不要列の削除, "テーブル", {"氏名", "属性", "値", "連番"}
),列のピボットを実行して、連番列を削除。
列のピボット = Table.Pivot( テーブル列の展開, List.Distinct(テーブル列の展開[属性]), "属性", "値" ), 連番列の削除 = Table.RemoveColumns(列のピボット,{"連番"})
というのが一応のパターンなんだけども（長かった）。Table.Splitを使ってやれば、列のピボット解除から先の展開が楽になる。
列のピボット解除 = Table.UnpivotOtherColumns(
テーブル1, {"氏名"}, "属性", "値"
),
テーブル分割 = Table.Split(列のピボット解除, 2),
リスト編集 = List.Transform(テーブル分割, each
Table.FromRows(
{{[氏名]{0}} & [値]},
type table [氏名=text, 品目=text, 数量=number]
)
),
テーブル結合 = Table.Combine(リスト編集)リストを編集したり、リストからテーブルを作成したりするのには、多少慣れが必要だとは思う。ただどうせ避けては通れない道なんだし、必要を感じた時が勉強のタイミングだと思う。
例えば住所を地域別に分類したい時、アプローチの方法は色々ある。アイテムアクセスを使うか列のマージを使うか。一長一短あるので使いどころ次第かと思う。
最初に「住所一覧」と「地域区分」のテーブルがあるものとして、まずはそれぞれのクエリを作成する。この時ある程度、加工しておくと使い勝手がいい。住所一覧には「都道府県名」列を追加しておこう。都道府県名の抽出は、「左から４文字目が『県』だったら４文字、それ以外は３文字を左から抜き出す」でＯＫ。
住所一覧let ソース = Excel.CurrentWorkbook(){[Name="住所一覧"]}[Content], 型の変更 = Table.TransformColumnTypes(ソース, {"住所", type text}), 都道府県列の追加 = Table.AddColumn( 型の変更, "都道府県名", each Text.Start([住所], 3+Number.From(Text.Middle([住所], 3, 1)="県")), type text ) in 都道府県列の追加
地域区分表には順番列（最初から入っているなら飛ばしてＯＫ）と地域ごとの連番列を追加する。グループごとに連番を付加する方法は、前回の記事参照のこと。
地域区分let ソース = Excel.CurrentWorkbook(){[Name="地域区分"]}[Content], 変更された型 = Table.TransformColumnTypes(ソース, { {"区分", type text}, {"都道府県名", type text} }), 都道府県分割 = Table.TransformColumns( 変更された型, {"都道府県名", each Text.Split(_, "、"), type list} ), 順番列の追加 = Table.AddIndexColumn( 都道府県分割, "順番", 1, 1, Int64.Type ), 列の展開1 = Table.ExpandListColumn(順番列の追加, "都道府県名"), 行のグループ化 = Table.Group( 列の展開1, {"区分"}, { "テーブル", each Table.AddIndexColumn(_, "連番", 1, 1), type table [都道府県名=text, 順番=number, 連番=number] } ), 列の展開2 = Table.ExpandTableColumn( 行のグループ化, "テーブル", {"都道府県名", "順番", "連番"} ) in 列の展開2
こうしておくことと、後でソートしやすくなる。
ではまず、元の住所の順番通りに地域区分列を追加する場合。列のマージだと展開時に順番がずれてしまうので、アイテムアクセスのほうが便利はいい。
順番そのままで区分列を追加する方法let 区分列の追加 = Table.AddColumn( 住所一覧, "区分", each let 都道府県名=[都道府県名] in 地域区分{[都道府県名=[都道府県名]]}[区分], type text ), 列の削除 = Table.RemoveColumns(区分列の追加,{"都道府県名"}) in 列の削除
次は順番を地域区分の順にする場合。こっちはマージしてからソートするのがお勧め。
地域区分の順に区分列を追加する方法１let クエリのマージ = Table.NestedJoin( 住所一覧, {"都道府県名"}, 地域区分, {"都道府県名"}, "マージ", JoinKind.LeftOuter ), マージ列の展開 = Table.ExpandTableColumn( クエリのマージ, "マージ", {"順番", "連番", "区分"} ), 行のソート = Table.Sort(マージ列の展開, {{"順番", 0}, {"連番", 0}}), 列の選択 = Table.SelectColumns(行のソート, {"区分", "住所"}) in 列の選択
「地域区分のほうにマージしてやれば」と思うかもしれないけど、それをやると同じ都道府県の住所の中で順番がずれてしまう。
地域区分の順に区分列を追加する方法１let クエリのマージ = Table.NestedJoin( 地域区分, {"都道府県名"}, 住所一覧, {"都道府県名"}, "マージ", JoinKind.LeftOuter ), マージ列の展開 = Table.ExpandTableColumn( クエリのマージ, "マージ", {"住所"} ), 行の抽出 = Table.SelectRows(マージ列の展開, each [住所] <> null), 行のソート = Table.Sort(行の抽出, {{"順番", 0}, {"連番", 0}}), 列の選択 = Table.SelectColumns(行のソート,{"区分", "住所"}) in 列の選択
別に気にしないならこっちでも。
Table.Sortの「Order.Ascending」を「0」（「Order.Descending」は「1」）にするのは横着技なので、お勧めしない。手打ちがめんどくさい時、思わずやっちゃうけど。
今回は「行のグループ化」のその４。その３はグループ化の際に「区切り文字を使って１つの文字列にまとめる」方法を紹介したけど、今回は個別に横に並べたい時の話。
これまたグループ化だけでは完結しないので、多少加工が必要になる。
まずは「区分」列でグループ化。
新しい列目：テーブル
操作：すべての行
→［OK］
前回説明したとおり、「_」は「テーブルをそのまま表示してねの意味なので、ここを今回は「インデックス列を追加してね」に変更する。
「_」を「Table.AddIndexColumn(_, "連番", 1, 1)」に変更
「type table [……]」の「……」に「, 連番=number」を追加「品目」と「連番」にチェックを入れて ［元の列名をプレフィックスとして使用します］のチェックを外す →［OK］
［編集］→［書式］→［プレフィックスの追加］値：品目
これで連番が見出しとして使えるようになったので、後は列のピボットを実行するだけ。
「連番」列を選択した状態で［編集］→［列のピボット］ 値列：品目 詳細設定オプションを開いて 値の集計関数：集計しない →［OK］
以上で完成。
今回は「行のグループ化」のその３。その２はピボットテーブルではできない「個別の行数のカウント」を取り上げたけど、今回は機能には用意されていないことをやってみる。
このように区分（グループ）ごとに品目を取りまとめたい時。グループ化の操作には「文字列でまとめる」はないので、一足飛びにはいかない。
まずは「区分」列でグループ化。
新しい列目：品目
操作：すべての行
→［OK］
すると列には「Table」がそのまま表示される。数式バーの「_」となっているところが「テーブルをそのまま表示してね」の意味。ここが「Table.RowCount(_)」となっていたら「テーブルの行数をカウントしてね」となる。
ここから「カスタム列の追加」で「= Text.Combine([品目][品目], "、")」とすれば目的の列を作れるんだけど、作ったテーブル列（上でいう「品目」列）を後で削除しないといけなくなる。
どうせやるなら、数式バー上で「テーブルをそのまま表示してね」を「文字列でまとめてね」に書き換えてやればいい。
「_」を「Text.Combine(_, "、")」に変更
「type table [……]」を「type text」に変更どうせカスタム列の追加をしたって Text.Combine関数を入れないといけないんだから、それを数式バーに入力してやればひと手間省ける。