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:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 😉
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
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!
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!
I was a bit fast off the mark with my solution. Yes, it’s a bit of a complex problem. Cool stuff though.
No problem, yours is a good variation.
You’re always welcome!
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 :-))
Very pleased to hear Bill 🙂
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单元格插入超链接
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
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.
Thank you!
So whoever feels brave enough for the security threads that come with it : Enjoy 😉
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?
I will have to think that through – it might take a while 😉
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.
Too busy currently so couldn’t check further, but I see you’ve made some progress!
Added some quick tips in the forums article.