Results 1 to 10 of 10

Thread: Generate Combinations from List

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Seeker muttleychess's Avatar
    Join Date
    Sep 2017
    Posts
    8
    Articles
    0
    Excel Version
    2016

    Unhappy Generate Combinations from List

    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

  2. #2
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    155
    Articles
    0
    Excel Version
    2019
    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.

  3. #3
    Seeker muttleychess's Avatar
    Join Date
    Sep 2017
    Posts
    8
    Articles
    0
    Excel Version
    2016

    Question

    Quote Originally Posted by alansidman View Post
    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
    Attached Files Attached Files

  4. #4
    Acolyte horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    72
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    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"
    Last edited by horseyride; 2019-04-18 at 01:33 PM.

  5. #5
    Seeker muttleychess's Avatar
    Join Date
    Sep 2017
    Posts
    8
    Articles
    0
    Excel Version
    2016
    [QUOTE=horseyride;40932]The key is to add an index before self merging, then use a column to filter out index2

  6. #6
    Seeker muttleychess's Avatar
    Join Date
    Sep 2017
    Posts
    8
    Articles
    0
    Excel Version
    2016
    I would like thank you all for help all solutions work fine

  7. #7
    Acolyte Bill Szysz's Avatar
    Join Date
    Apr 2016
    Location
    Poznań, Poland Dublin, Ireland
    Posts
    95
    Articles
    0
    Excel Version
    Excel 365
    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"

  8. #8
    Acolyte horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    72
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    I got a method? Mom would be proud

  9. #9
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,203
    Articles
    0
    Excel Version
    Office 365 Subscription
    Nice one, Bill!
    Ali
    Enthusiastic self-taught user of MS Excel!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •