How to get all combinations of a single column?

[mlpoole85]

Hi! I need to get all possible combinations of a single column of data within the column. 

For example, I have the Values A, B, C, D, in a column and I want a separate table with all possible combinations of A, B, C, D. The Values column below is the column I have, and the Combined Values column is the column I want to achieve. The Combined Values column is obviously not complete for this example, but I do want all possible combinations .

Everything I can find online is combinations of two columns and I can't figure out how to do it with just one. Any ideas? 

[lbendlin · Accepted solution]

/*List item combinations

need to multiply list by itself for Log(2) of list length
For 5 list items can use shortcut - two multiplications, and then add original list. Not really faster though. Easier to brute force powers of 2
*/
let
      input = List.Transform({"A".."F"},each {_}),
      output  = List.Distinct(List.TransformMany(input,  each input,   (o,c)=>List.Distinct(List.Sort(o & c)))),
      output2 = List.Distinct(List.TransformMany(output, each output,  (o,c)=>List.Distinct(List.Sort(o & c)))),
      output3 = List.Distinct(List.TransformMany(output2,each output2, (o,c)=>List.Distinct(List.Sort(o & c)))),
      output4 = List.Transform(output3, each Text.Combine(_,","))
in
      output4

[mlpoole85]

This works! Thank you so much @lbendlin !!!

[lbendlin]

Thanks to @dufoq3 for the inspiration around List.TransformMany 

Needless to say - this is a mere finger exercise. This data is immutable, there is absolutely no need to compute it over and over again.

[dufoq3]

I see that inspiration is working both ways 😉

[rajendraongole1]

Hi @mlpoole85 -  you could use Power Query and M code to generate combinations. 

combined value:

power query editor code:

let
Source = Table.FromColumns({{"A", "B", "C", "D"}}, {"Value"}),
Combinations = List.Transform({1..Table.RowCount(Source)}, each List.Combine(List.Repeat({Source[Value]}, _))),
AllCombinations = List.Combine(List.Transform(Combinations, each List.Transform(Combinations, each Text.Combine(_, ",")))),
#"Converted to Table" = Table.FromList(AllCombinations, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"

Hope this works in your scenerio. please check

[mlpoole85]

Hi @rajendraongole1 , thanks for the reply! Unfortunately, this does not work here. I need all unique combinations of A, B, C, D. Combinations including ABC, ABCD, AD, BC, BCD, D, CD, for example. I need a result that resembles the output of the Combined Values column I posted above. This code just repeats A, B, C, D for me and doesn't include any unique combinations. Anything else I could try?