Power Queryで順列出力

3890 ワード
PowerBI Excel PowerQuery Excel テキストリンク

順列が何通りかを求めるのではなく、実際にその全通りを発生させるという試みです。
いわゆるクロス結合処理をした後、絞り込むという工程で作っています。
手元のPC（メモリ4GB、Celeron）だと、要素9個から9個を選択する順列で、プレビューが返るのに1分半くらいかかりました。こんなもんでしょうかね

コード

下記のコードを空のクエリに貼り付けて、「fx_順列」という名前をつけてください。

fx_順列
(InputList as list, NumberOfTimesToSelect as number)=>

if not List.Contains({1..List.Count(InputList)},NumberOfTimesToSelect) then
    "Error:NumberOfTimesToSelectは、1からInputListの要素数までの整数で入力してください。"

else
    let
        Source = Table.FromColumns({InputList}),
        クロス結合 =List.Last(List.Generate(()=>[id=0,tbl =Source],
                                           each [id]<NumberOfTimesToSelect,//―――「NumberOfTimesToSelect-1」だけ列追加するため。
                                           each [id=[id]+1,
                                                 Name="Column"&Number.ToText(id+1),
                                                 Added =Table.AddColumn([tbl],Name,each InputList),
                                                 tbl=Table.ExpandListColumn(Added,Name)],
                                           each [tbl]
                             )
                    ),
        整理 = Table.SelectRows( クロス結合, each List.Union({InputList,Record.ToList(_)})=InputList ),
        重複削除 =Table.Distinct(整理)
    in
        重複削除

実行例1：引数に重複がない場合

赤、青、緑、黄、白の5個の順列、組合せを出してみましょう。
関数を下記のコードを空のクエリに打ち込みます。

fx_順列({"赤","青","緑","黄","白"},5)

↓実行結果。ちゃんと5の階乗で120行になってますね。

実行例2：引数に同じものがある場合

試しに、赤、青、緑、白、白から5個の順列、組合せを出してみましょう。

fx_順列({"赤","青","緑","白","白"},5)

↓実行結果。重複がある分、減ってます。（この場合は2の階乗だけ重複があるので、それで割ることになる）
※要素に重複があるケースの公式など覚えてないので、興味のある方は調べてみてください。他のケースでも、たぶん結果は合ってます。

解説

「クロス結合」の箇所

列追加で引数に入れたリストを入れて、展開する、の単なる繰り返しです。
個数が決まっているなら、カスタム列追加で当該リストを入れて、展開するという手作業でもいいわけです。

「整理」の箇所

Table.SelectRowsの第2引数(condition)は、テーブルの行の値(Record型)を引数にした関数の形で書きます。Record型そのままではList.Union関数に渡せないので、Record.ToList関数でリストに変換しています。

List.Union関数は、その行の値がInputListの要素だけで成立しているかを判定するのに用いました。
List.Union関数の挙動は下記の通り。

List.Union({{1,1,1,2,2},{2,1,1}})
//第1引数の範囲に収まるので、 {1,1,1,2,2}が返る。

List.Union({{1,2,2,2},{2,1,1}})
//第1引数には1が1個しかないので、 {1,2,2,2,1}とおしりに追加される。

その他参考
Record.ToList関数：Microsoft Docs
Table.Distinct(List.Distinct)の使用例 | qiita