なんでもエクセル(Nandemo Excel)

何でもエクセルでやってしまう そこのあなた。ようこそ

Power Query:ランダムに並び替える

データをランダムに並べ替えるには「重複しないランダム値」が必要で、Excelのワークシート関数なら「=RAND()」を並べれば簡単に作れる(表示された RAND関数の結果は絶対に重複しない)。なので、Power Queryにも「Number.Random」があるから、同じくカスタム列を追加してやるだけでできるんだろうと高をくくっていた。

失敗例

    乱数列の追加 = Table.AddColumn(テーブル1, "乱数", each Number.Random())

見た目には上手くいっているように見える。ところが読み込んでみると乱数列には同じ値がずらりと並ぶ。

ならばと、今度は読み込む前にソートを掛けてみる。

見た目は同じ値が並んでいるものの、乱数列に昇順のソートをかけると

失敗例

    乱数列の追加 = Table.AddColumn(テーブル1, "乱数", each Number.Random()),     行のソート = Table.Sort(乱数列の追加, {"乱数", Order.Ascending})

これまた一見するとランダムに並べ替えられているように見える。ところが何度更新をかけても結果が変化しない。

結果、このアプローチでは上手くいかないことが分かった。前振りが長い。

という訳で、一度リストにしてから、同行数の「List.Random」と結合して、テーブルに変換し直してやることにした。

learn.microsoft.com

予め「番号」「氏名」列を持つ、「テーブル1」クエリを作成しているものとする。

    タイプレコード = Type.TableRow(Value.Type(テーブル1)),
    タイプレコード2 = Type.ForRecord(
        Type.RecordFields(タイプレコード)
            & [乱数 = [Type=type number, Optional=false]], false
    ),
    リスト化 = Table.ToColumns(テーブル1),
    乱数リスト = List.Buffer(List.Random(List.Count(リスト化{0}))),
    テーブル変換 = Table.FromColumns(
        リスト化 & {乱数リスト}, type table タイプレコード2
    ),
    行のソート = Table.Sort(テーブル変換, {"乱数", 0}),
    列の削除 = Table.RemoveColumns(行のソート, {"乱数"})

これで一応、ランダムに並べ替えできた。ただタイプレコードを作るところが若干めんどくさい。

なので別案。テーブル1に連番列を追加し、乱数テーブルに連番列をマージしてやる。

    乱数テーブル = [
        乱数リスト = List.Random(Table.RowCount(テーブル1)),
        テーブル変換 = Table.FromColumns(
            {List.Positions(乱数リスト), 乱数リスト},
            type table [連番=number, 乱数=number]
        ),
        行のソート = Table.Buffer(Table.Sort(テーブル変換, {"乱数", 0}))
    ][行のソート],
    連番列の追加 = Table.AddIndexColumn(
        テーブル1, "連番", 0, 1, Int64.Type
    ),
    クエリのマージ = Table.NestedJoin(
        乱数テーブル, {"連番"}, 連番列の追加, {"連番"}, "マージ"
    ),
    マージ列の展開 = Table.ExpandTableColumn(
        クエリのマージ, "マージ", Table.ColumnNames(テーブル1)
    ),
    不要列の削除 = Table.RemoveColumns(
        マージ列の展開,{"連番", "乱数"}
    )

パフォーマンスはあまり変わらないけど、分かりやすくはなった気がする。

最後に別案。

    型リスト = {
        {"番号", Int64.Type}, {"氏名", type text}
    },
    テーブル1 = [
        ブック = Excel.CurrentWorkbook(),
        ソース = ブック{[Name="テーブル1"]}[Content],
        型の変更 = Table.TransformColumnTypes(ソース, 型リスト)
    ][型の変更],
    ランダムリスト = List.Random(Table.RowCount(テーブル1)),
    乱数列の追加 = Table.FromColumns(
        Table.ToColumns(テーブル1) & {ランダムリスト},
        Table.ColumnNames(テーブル1) & {"乱数"}
    ),
    型の変更 = Table.TransformColumnTypes(
        乱数列の追加, 型リスト & {{"乱数", type number}}
    ),
    行のソート = Table.Sort(型の変更, {"乱数", Order.Ascending}),
    乱数列の削除 = Table.RemoveColumns(行のソート, {"乱数"})

マージしない分、こっちのほうが無駄が少ない。List.Sortをしてからテーブルに戻してもいいのかも。