Generate Combinations from List

**muttleychess** · 2019-04-18, 03:02 AM

Hi

I have 5 names
BRAZIL
USA
ENGLAND
Germany
Bolivia

I would like to combine this 5 names 2 to 2 (or 3 to 3) , using Power query

C(5,2) = 10

I tried merge 2 query , but no work

**alansidman** · 2019-04-18, 05:27 AM

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

**muttleychess** · 2019-04-18, 09:27 AM

Originally Posted by alansidman

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Sorry

follows attached

to combine this five countries with the others,I did using power query, but it generated arrangements and not combinations

**horseyride** · 2019-04-18, 02:29 PM

The key is to add an index before self merging, then use a column to filter out index2 <= index

try_combinations3.xlsx

TBLCOUNTRY:

Code:

let
    Fonte = #"LISTCOUNTRY",
    #"Personalização Adicionada" = Table.AddColumn(Fonte, "COLLISTCOUNTRY", each LISTCOUNTRY),
    #"Expanded COLLISTCOUNTRY1" = Table.ExpandTableColumn(#"Personalização Adicionada", "COLLISTCOUNTRY", {"COUNTRY", "Index"}, {"COUNTRY2", "Index2"}),
    #"Added Custom" = Table.AddColumn(#"Expanded COLLISTCOUNTRY1", "Custom", each if [Index2] <=[Index] then 0 else 1),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index", "Index2", "Custom"})
in
    #"Removed Columns"

LISTCOUNTRY:

Code:

let
    Fonte = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Fonte, "Index", 1, 1)
in
    #"Added Index"

**Bill Szysz** · 2019-04-18, 10:26 PM

Or alternative to horseyride method ;-)
Leave LISTCOUNTRY as is.
TBLCOUNTRY code below:

Code:

let
    Fonte = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],
    #"Tipo Alterado" = Table.TransformColumnTypes(Fonte,{{"COUNTRY", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Tipo Alterado", "Indeks", 1, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "OTHER", each List.Skip(LISTCOUNTRY, [Indeks])),
    #"Expanded {0}" = Table.ExpandListColumn(#"Added Custom", "OTHER"),
    #"Filtered Rows" = Table.SelectRows(#"Expanded {0}", each ([OTHER] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Indeks"})
in
    #"Removed Columns"