excelexcel-formulaexcelquery

Make combinations using Excel queries


I need to make all the possible combinations(not permutations) with three numbers using excel queries.

I have six numbers in a column. From A1:A6. I need to make all the possible combinations(not permutations) with three numbers using excel queries. Please help..

enter image description here


Solution

  • If by "excel queries" you mean Power Query, here is a script that will do that:

    Algorithm

    => 56 combinations (allowing repeats of numbers in the same combination)

    Note that the code below could be generated from the Power Query User interface

    let
    
    //Read in table and set data type
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"List", Int64.Type}}),
    
    //Add two more columns where the rows of each column = the full original table
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each #"Changed Type"),
        #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each #"Changed Type"),
    
    //Expand each of the two custom columns
        #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"List"}, {"Custom.List"}),
        #"Expanded Custom.1" = Table.ExpandTableColumn(#"Expanded Custom", "Custom.1", {"List"}, {"Custom.1.List"}),
    
    //Add a column which contains a Sorted List of the three data columns
        #"Added Custom2" = Table.AddColumn(#"Expanded Custom.1", "Sorted Row", each List.Sort({[List],[Custom.List],[Custom.1.List]})),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"List", "Custom.List", "Custom.1.List"}),
    
    //Remove the duplicates in the list => 56 items
        #"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
    
    //expand the list into a delimited list
        #"Added Custom3" = Table.AddColumn(#"Removed Duplicates", "Custom", 
            each Text.Combine(List.Transform([Sorted Row],each Text.From(_)),"~"), type text),
        #"Removed Columns1" = Table.RemoveColumns(#"Added Custom3",{"Sorted Row"}),
    
    //split the column by the delimiter
        #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns1", "Custom", 
            Splitter.SplitTextByDelimiter("~", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{
            {"Custom.1", Int64.Type}, {"Custom.2", Int64.Type}, {"Custom.3", Int64.Type}})
    in
        #"Changed Type1"
    

    Original Data
    enter image description here

    Combinations with Repeated Values
    enter image description here

    Edit to generalize the code
    Please note that this code is inefficient for large sets, as it first generates all the Permutations, and then eliminates the non-valid values by sorting the rows and removing the duplicates.
    For selections of 7 items out of a group of 8, the number of permutations is in excess of 2,000,000 which Excel Power Query does not handle efficiently, so it takes a while to run.
    The same code running on Power BI Desktop runs in one or two seconds

    I have written it as a custom function named fnCombos

    Custom Function
    Rename fnCombos

    (l as list, num as number)=>
    
    let
      t = Table.FromColumns({l}),
    
      #"Permutations" = List.Last(
        List.Generate(
          ()=>[T=t, idx=0],
          each [idx] < num,
          each [T = Table.AddColumn([T],Text.From([idx]), each l), idx=[idx]+1],
          each [T])),
    
      #"Expand Columns" = List.Accumulate({"0"..Text.From(num-2)},#"Permutations", (state, current)=>
          Table.ExpandListColumn(state,current)),
    
      //Sort each row
      //  Then remove duplicates
      #"Sort Rows and DeDupe" = List.Distinct(Table.TransformRows(#"Expand Columns", (r) => 
          Record.FromList(List.Sort(Record.FieldValues(r)),Record.FieldNames(r)))),
    
        #"Converted to Table" = Table.FromList(#"Sort Rows and DeDupe", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", Table.ColumnNames(#"Expand Columns"))
      
    in
        #"Expanded Column1"
    

    Main Query

    let
    
    //Read in table and set data type
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"List", Int64.Type}}),
    
    //Call custom function to create the combinations
        #"Combos" = fnCombos(#"Changed Type"[List],7)
    
    in #"Combos"