• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to generate all possible combinations

Kmahraz

Kmahraz

Member
Hi,
I am looking for some suggestions on how to generate all possible combinations from several lists,a Macro will be preferable if possible.

One challenge is that there are 2 selections that are optional so I want to include those in my results for all possible combinations.

Any help will be much appreciated.

File attached with more details.

Thank you,
K
 

Attachments

  • Chandoo - Combinations.xlsm
    14.1 KB · Views: 11
For optional ones. I'd personally add place holder character to represent when optional values are not selected.

That way, it won't alter string structure.
 
Solution was provided in priv. conversation. Posting method using PowerQuery here for reference.

1. Set up each list as table in a sheet (I named it tblA ~ tblH).
2. Load tblA to PowerQuery (Data tab ->"From Table")
3. Go to editor and open Advanced Editor.
4. Change "Source =" line to below. This is used to pull single column from table
Code: 
Source = Excel.CurrentWorkbook(){[Name="tblA"]}[Content][Designator]
Where Name="Tablename" and [Designator] is the column header.
5. Delete "Changed Type" step the system generated.
6. In List Tools, convert to Table (no need to change anything at this step).
7. Add custom column with same formula as "Source =" but change table name.
Code: 
= Excel.CurrentWorkbook(){[Name="tblB"]}[Content][Designator]
8. Repeat step 6 for all tables (except for tblA).
Result should look like below.
upload_2016-12-9_13-42-56.png
9. Expand each column.
10. Renamed column A to H
11. Changed column data type to Text for all columns.
12. Load data to sheet.

See attached.

Note: You can load entire tables and remove columns instead of loading single column as list. But performance will take a hit, depending on number of columns.
 

Attachments

  • Sample.xlsb
    585.5 KB · Views: 13
HI @Chihiro
Hope you are doing well!
In step 5, you indicated to delete "ChangedType" in my case I have 2 "ChangedType" which one should I delete?
upload_2016-12-12_11-57-40.png

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="tblB"]}[Content][Designator],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Description", type text}, {"Designator", type text}})
in
    #"Changed Type"

Can you please elaborate on step 5, 6 and 7 or if possible provide some screen shot.

Thank you,
K
 
There really is only one "Change type". "let" section is where the actual process is taking place. "in" is just place holder for next phase.

This step isn't always needed, but it is far safer to do Column Data Type change at near the end of process. Otherwise, it may cause issue during intermediate steps.

At any rate, don't modify "M" in advanced editor to delete a step. You'd delete a step in "Applied Steps" pane, by clicking on red "x".
(ONLY time that you need to edit "M" formula directly in this set up is when you edit the "Source")

upload_2016-12-12_12-10-20.png

Step 6: When you load a list (i.e. single column from table instead of entire table) it should automatically drop you in "List Tools" menu. At top corner of the menu you will see "To Table" button.
upload_2016-12-12_12-18-27.png

Step 7: Go to "Add column menu" and click on "Add Custom Column" and paste in the formula as is (just change table name).

FYI - You can follow most of steps taken via "Applied Steps" pane in the sample workbook I uploaded.
 
I did look at the steps via "Applied Steps" pane in the sample workbook you uploaded and now it make more sense,
I have another example where I will try your solution and will keep you posted.

Thanks again for help.

K
 
You must log in or register to reply here.
Back
Top