Generate Combinations from List

muttleychess

New member
Joined
Sep 25, 2017
Messages
9
Reaction score
0
Points
1
Excel Version(s)
2016
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 :Cry:
 
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.
 
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:confused:
 

Attachments

  • try_combinations.xlsx
    19.9 KB · Views: 21
The key is to add an index before self merging, then use a column to filter out index2 <= index

View attachment 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"
 
Last edited:
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"
 
I got a method? Mom would be proud
 
I would like thank you all for help all solutions work fine
 
Back
Top