Permutations table in Power BI or Power Query in Excel

Today my husband came with a special task to me: He had to re-wire an old instrument on board of our sailing boat and “lost” the original matching instructions. There were 4 cables to go into 4 different holes, which left him with 24 possible combinations. So he needed a permutations table with all possible combinations that he could print out and tick each combination that he would try subsequently:

To be honest, he was quicker in creating the permutations table manually in Excel than me in Power Query, as it took me a while to develop an easy enough algorithm. But the technique I came out with at the end is so typical “Power Query”-ish, that I’m going to share it here:

The Video

The Function

Here comes the function code:


let func =
(List as list) =>
let
List = List.Buffer(List),
LA =
List.Accumulate(
List.Skip(List),
[Table = Table.FromColumns({List}), Counter = 2],
(prev, this) => [
AddColumn = Table.AddColumn(prev[Table], "Column"&Text.From(prev[Counter]), each List.Difference(List, Record.FieldValues(_))),
Table = Table.ExpandListColumn(AddColumn, "Column"&Text.From(prev[Counter])),
Counter = prev[Counter]+1
]
),
Result = LA[Table]
in
Result ,
documentation = [
Documentation.Name = " List.Permutations ",
Documentation.Description = " Creates a permuations table from all elements from a <code>List</code> ",
Documentation.LongDescription = " Creates a permuations table from all elements from a <code>List</code> ",
Documentation.Category = " List ",
Documentation.Source = " https://wp.me/p6lgsG-Qz . ",
Documentation.Version = " 2.0 (Changed from List.Generate to List.Accumulate) ",
Documentation.Author = " Imke Feldmann: www.TheBIccountant.com . ",
Documentation.Examples = {[Description = " ",
Code = " ",
Result = " "]}]
in
Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))

Enjoy & stay queryious 😉

Comments (15) Write a comment

  1. Table.FromColumns auto-generates column names which simplifies things a bit. List.Accumulate can also be used instead. So another way is:

    (List as list) =>
    let
    Table = Table.FromColumns(List.Repeat({{List}},List.Count(List)))
    ,Expand = List.Accumulate(Table.ColumnNames(Table),Table,(a,b) => Table.ExpandListColumn(a, b))
    in
    Expand

    Reply

    • Thanks Cameron!
      Yours is a very nice example of a variation to my task, as yours can contain one item multiple times.
      So in the example of 4 list items, it produces 256 rows/possible combinations, while mine only returns 24.
      Very nice trick with the dynamic expansion!

      Reply

    • Agree with the suggestion of List.Aggregate (less code and possibly a bit less memory as well, although I don’t know how the effect of lazy evaluation actually is here).
      Have adjusted the code above, so thanks again Cameron!

      Reply

      • I was a bit fast off the mark with my solution. Yes, it’s a bit of a complex problem. Cool stuff though.

        Reply

  2. This is incredible because i had exactly the same problem 2 or 3 years ago.
    Of course, like you, i started to think how to resolve it in PQ. My solution was based exactly on the same logic as yours. The only difference is that i used the table as an argument for function and i used recursion.
    Out of curiosity, I checked both solutions for nine colors (362 880 rows as result) – the execution time is almost identical (maybe some tenths of a second of difference).
    Nice to know that we think alike :-))

    Reply

  3. let
    源 = Web.Page(”
    var oExcel,oWorkbook,oSheet,oRange,strAllName
    strAllName=’C:\\Users\\Administrator\\Desktop\\畅心.xlsx’;
    oExcel = new ActiveXObject(‘Excel.Application’);
    oWorkbook=oExcel.Workbooks.Open(strAllName);
    oSheet=oWorkbook.Worksheets(1);
    oRange=oSheet.Range(‘A1′);
    oSheet.Hyperlinks.Add(
    oRange,”,’http://pqfans.com/’,’Power Query’,’Power Query中文博客’)
    oWorkbook.Save();
    oExcel.Quit();”)
    in
    源 //在桌面畅心工作簿的第一个表的A1单元格插入超链接

    Reply

    • Hey, that looks like VBA 🙂
      But unfortunately it’s not working for me. Played around with the syntax a bit, but couldn’t get it working.
      Any idea what could be the reason? What modifications did you do to the script from the VBA-editor?
      Thanks and cheers, Imke

      Reply

  4. First, you need to set up the IE browser,
    Enable ActiveX control initialization and running options for untagged executable scripts.
    Then our Power Query can be used like VBA.

    Reply

  5. Thank you for this wonderful code. Frankly, it’s a bit beyond my understanding but I appreciate the output.

    Would you be able to adapt this code to create the permutations for a subset of the list? In other words if I have a set of 5 colors (red, yellow, green, blue, orange) can I specify that I’d like all 3-color combinations?

    Reply

      • As I thought through the problem I realized what I actually need is combinatorials rather than permutations, though your readers may appreciate the permutations.too. I wasn’t sure if my comment had gone through here so I posted the combination question with my real-world example at Mr. Excel
        https://www.mrexcel.com/forum/power-bi/1064050-generate-combinatorials-power-query.html

        I apologize for the duplication and don’t want you to burn brain cells if someone else is able to solve this problem, but I understand it’s a tough one. The Mr. Excel thread will provide background on why I need it.

        Reply

        • Too busy currently so couldn’t check further, but I see you’ve made some progress!
          Added some quick tips in the forums article.

          Reply

Leave a Reply