All combinations in power query with dynamic column numbers

[gargamalebarbosa]

I have a table consisting of 4 columns. I want to find all possible combinations of the desired columns using Power Query. For example, the A-B-D column, the A-B column, or the A-B-C-D columns.
I will write the combinations of the desired columns into a list, and Power Query will generate all the combinations based on that list.
For example, there will be a list in column F, and Power Query will create the combination table based on the column names in that list. The values in the resulting table will be unique

Thank you,

 

[smozgur]

Try the following:

Main Table - Name = Data

1236980.xlsx
	A	B	C	D
1	A	B	C	D
2	A1	B1	C1	D1
3	A2	B2	C2	D2
4	A3	B3	C3	D3
5	A4	B4		D4
6	A5			D5
7				D6
Sheet1

Combinations Table - Name = Combinations

1236980.xlsx
	F
1	Combinations
2	A-B-D
3	A-B
4	A-B-C-D
5	C-B-A
Sheet1

Query:

let
    Data = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    Combinations = Excel.CurrentWorkbook(){[Name="Combinations"]}[Content],
    Options = Table.ToColumns(Combinations){0},

    TableList = List.Accumulate(Options,
            {},
            (state, current) => 
                let 
                    Options = Text.Split(current, "-"),
                    Table = Table.SelectColumns(Data, Options),
                    
                    Combine = List.Accumulate(Options, 
                                Table.SelectColumns(Table, Options{0}),
                                (state, current) => 
                                    let
                                        AddTable = if current = Options{0} 
                                                    then state 
                                                    else Table.ExpandTableColumn(Table.AddColumn(state, current, each Table.SelectColumns(Table, current)), current, {current}),
                                        RemoveNulls = Table.SelectRows(AddTable, each Record.Field(_, current) <> null)
                                    in
                                        RemoveNulls

                            ),
                    Result = Table.ToColumns(Table.CombineColumns(Combine, Options, Combiner.CombineTextByDelimiter("-", QuoteStyle.None), Text.Combine(Options, "-"))) 

                in
                    state & Result
            ),
    Result = Table.FromColumns(TableList, Options)
in
    Result

Result:

A-B-D	A-B	A-B-C-D	C-B-A
A1-B1-D1	A1-B1	A1-B1-C1-D1	C1-B1-A1
A1-B1-D2	A1-B2	A1-B1-C1-D2	C1-B1-A2
A1-B1-D3	A1-B3	A1-B1-C1-D3	C1-B1-A3
A1-B1-D4	A1-B4	A1-B1-C1-D4	C1-B1-A4
A1-B1-D5	A2-B1	A1-B1-C1-D5	C1-B1-A5
A1-B1-D6	A2-B2	A1-B1-C1-D6	C1-B2-A1
A1-B2-D1	A2-B3	A1-B1-C2-D1	C1-B2-A2
A1-B2-D2	A2-B4	A1-B1-C2-D2	C1-B2-A3
A1-B2-D3	A3-B1	A1-B1-C2-D3	C1-B2-A4
A1-B2-D4	A3-B2	A1-B1-C2-D4	C1-B2-A5
A1-B2-D5	A3-B3	A1-B1-C2-D5	C1-B3-A1
A1-B2-D6	A3-B4	A1-B1-C2-D6	C1-B3-A2
A1-B3-D1	A4-B1	A1-B1-C3-D1	C1-B3-A3
A1-B3-D2	A4-B2	A1-B1-C3-D2	C1-B3-A4
A1-B3-D3	A4-B3	A1-B1-C3-D3	C1-B3-A5
A1-B3-D4	A4-B4	A1-B1-C3-D4	C1-B4-A1
A1-B3-D5	A5-B1	A1-B1-C3-D5	C1-B4-A2
A1-B3-D6	A5-B2	A1-B1-C3-D6	C1-B4-A3
A1-B4-D1	A5-B3	A1-B2-C1-D1	C1-B4-A4
A1-B4-D2	A5-B4	A1-B2-C1-D2	C1-B4-A5
A1-B4-D3		A1-B2-C1-D3	C2-B1-A1
A1-B4-D4		A1-B2-C1-D4	C2-B1-A2
A1-B4-D5		A1-B2-C1-D5	C2-B1-A3
A1-B4-D6		A1-B2-C1-D6	C2-B1-A4
A2-B1-D1		A1-B2-C2-D1	C2-B1-A5
A2-B1-D2		A1-B2-C2-D2	C2-B2-A1
A2-B1-D3		A1-B2-C2-D3	C2-B2-A2
A2-B1-D4		A1-B2-C2-D4	C2-B2-A3
A2-B1-D5		A1-B2-C2-D5	C2-B2-A4
A2-B1-D6		A1-B2-C2-D6	C2-B2-A5
A2-B2-D1		A1-B2-C3-D1	C2-B3-A1
A2-B2-D2		A1-B2-C3-D2	C2-B3-A2
A2-B2-D3		A1-B2-C3-D3	C2-B3-A3
A2-B2-D4		A1-B2-C3-D4	C2-B3-A4
A2-B2-D5		A1-B2-C3-D5	C2-B3-A5
A2-B2-D6		A1-B2-C3-D6	C2-B4-A1
A2-B3-D1		A1-B3-C1-D1	C2-B4-A2
A2-B3-D2		A1-B3-C1-D2	C2-B4-A3
A2-B3-D3		A1-B3-C1-D3	C2-B4-A4
A2-B3-D4		A1-B3-C1-D4	C2-B4-A5
A2-B3-D5		A1-B3-C1-D5	C3-B1-A1
A2-B3-D6		A1-B3-C1-D6	C3-B1-A2
A2-B4-D1		A1-B3-C2-D1	C3-B1-A3
A2-B4-D2		A1-B3-C2-D2	C3-B1-A4
A2-B4-D3		A1-B3-C2-D3	C3-B1-A5
A2-B4-D4		A1-B3-C2-D4	C3-B2-A1
A2-B4-D5		A1-B3-C2-D5	C3-B2-A2
A2-B4-D6		A1-B3-C2-D6	C3-B2-A3
A3-B1-D1		A1-B3-C3-D1	C3-B2-A4
A3-B1-D2		A1-B3-C3-D2	C3-B2-A5
A3-B1-D3		A1-B3-C3-D3	C3-B3-A1
A3-B1-D4		A1-B3-C3-D4	C3-B3-A2
A3-B1-D5		A1-B3-C3-D5	C3-B3-A3
A3-B1-D6		A1-B3-C3-D6	C3-B3-A4
A3-B2-D1		A1-B4-C1-D1	C3-B3-A5
A3-B2-D2		A1-B4-C1-D2	C3-B4-A1
A3-B2-D3		A1-B4-C1-D3	C3-B4-A2
A3-B2-D4		A1-B4-C1-D4	C3-B4-A3
A3-B2-D5		A1-B4-C1-D5	C3-B4-A4
A3-B2-D6		A1-B4-C1-D6	C3-B4-A5
A3-B3-D1		A1-B4-C2-D1
A3-B3-D2		A1-B4-C2-D2
A3-B3-D3		A1-B4-C2-D3
A3-B3-D4		A1-B4-C2-D4
A3-B3-D5		A1-B4-C2-D5
A3-B3-D6		A1-B4-C2-D6
A3-B4-D1		A1-B4-C3-D1
A3-B4-D2		A1-B4-C3-D2
A3-B4-D3		A1-B4-C3-D3
A3-B4-D4		A1-B4-C3-D4
A3-B4-D5		A1-B4-C3-D5
A3-B4-D6		A1-B4-C3-D6
A4-B1-D1		A2-B1-C1-D1
A4-B1-D2		A2-B1-C1-D2
A4-B1-D3		A2-B1-C1-D3
A4-B1-D4		A2-B1-C1-D4
A4-B1-D5		A2-B1-C1-D5
A4-B1-D6		A2-B1-C1-D6
A4-B2-D1		A2-B1-C2-D1
A4-B2-D2		A2-B1-C2-D2
A4-B2-D3		A2-B1-C2-D3
A4-B2-D4		A2-B1-C2-D4
A4-B2-D5		A2-B1-C2-D5
A4-B2-D6		A2-B1-C2-D6
A4-B3-D1		A2-B1-C3-D1
A4-B3-D2		A2-B1-C3-D2
A4-B3-D3		A2-B1-C3-D3
A4-B3-D4		A2-B1-C3-D4
A4-B3-D5		A2-B1-C3-D5
A4-B3-D6		A2-B1-C3-D6
A4-B4-D1		A2-B2-C1-D1
A4-B4-D2		A2-B2-C1-D2
A4-B4-D3		A2-B2-C1-D3
A4-B4-D4		A2-B2-C1-D4
A4-B4-D5		A2-B2-C1-D5
A4-B4-D6		A2-B2-C1-D6
A5-B1-D1		A2-B2-C2-D1
A5-B1-D2		A2-B2-C2-D2
A5-B1-D3		A2-B2-C2-D3
A5-B1-D4		A2-B2-C2-D4
A5-B1-D5		A2-B2-C2-D5
A5-B1-D6		A2-B2-C2-D6
A5-B2-D1		A2-B2-C3-D1
A5-B2-D2		A2-B2-C3-D2
A5-B2-D3		A2-B2-C3-D3
A5-B2-D4		A2-B2-C3-D4
A5-B2-D5		A2-B2-C3-D5
A5-B2-D6		A2-B2-C3-D6
A5-B3-D1		A2-B3-C1-D1
A5-B3-D2		A2-B3-C1-D2
A5-B3-D3		A2-B3-C1-D3
A5-B3-D4		A2-B3-C1-D4
A5-B3-D5		A2-B3-C1-D5
A5-B3-D6		A2-B3-C1-D6
A5-B4-D1		A2-B3-C2-D1
A5-B4-D2		A2-B3-C2-D2
A5-B4-D3		A2-B3-C2-D3
A5-B4-D4		A2-B3-C2-D4
A5-B4-D5		A2-B3-C2-D5
A5-B4-D6		A2-B3-C2-D6
		A2-B3-C3-D1
		A2-B3-C3-D2
		A2-B3-C3-D3
		A2-B3-C3-D4
		A2-B3-C3-D5
		A2-B3-C3-D6
		A2-B4-C1-D1
		A2-B4-C1-D2
		A2-B4-C1-D3
		A2-B4-C1-D4
		A2-B4-C1-D5
		A2-B4-C1-D6
		A2-B4-C2-D1
		A2-B4-C2-D2
		A2-B4-C2-D3
		A2-B4-C2-D4
		A2-B4-C2-D5
		A2-B4-C2-D6
		A2-B4-C3-D1
		A2-B4-C3-D2
		A2-B4-C3-D3
		A2-B4-C3-D4
		A2-B4-C3-D5
		A2-B4-C3-D6
		A3-B1-C1-D1
		A3-B1-C1-D2
		A3-B1-C1-D3
		A3-B1-C1-D4
		A3-B1-C1-D5
		A3-B1-C1-D6
		A3-B1-C2-D1
		A3-B1-C2-D2
		A3-B1-C2-D3
		A3-B1-C2-D4
		A3-B1-C2-D5
		A3-B1-C2-D6
		A3-B1-C3-D1
		A3-B1-C3-D2
		A3-B1-C3-D3
		A3-B1-C3-D4
		A3-B1-C3-D5
		A3-B1-C3-D6
		A3-B2-C1-D1
		A3-B2-C1-D2
		A3-B2-C1-D3
		A3-B2-C1-D4
		A3-B2-C1-D5
		A3-B2-C1-D6
		A3-B2-C2-D1
		A3-B2-C2-D2
		A3-B2-C2-D3
		A3-B2-C2-D4
		A3-B2-C2-D5
		A3-B2-C2-D6
		A3-B2-C3-D1
		A3-B2-C3-D2
		A3-B2-C3-D3
		A3-B2-C3-D4
		A3-B2-C3-D5
		A3-B2-C3-D6
		A3-B3-C1-D1
		A3-B3-C1-D2
		A3-B3-C1-D3
		A3-B3-C1-D4
		A3-B3-C1-D5
		A3-B3-C1-D6
		A3-B3-C2-D1
		A3-B3-C2-D2
		A3-B3-C2-D3
		A3-B3-C2-D4
		A3-B3-C2-D5
		A3-B3-C2-D6
		A3-B3-C3-D1
		A3-B3-C3-D2
		A3-B3-C3-D3
		A3-B3-C3-D4
		A3-B3-C3-D5
		A3-B3-C3-D6
		A3-B4-C1-D1
		A3-B4-C1-D2
		A3-B4-C1-D3
		A3-B4-C1-D4
		A3-B4-C1-D5
		A3-B4-C1-D6
		A3-B4-C2-D1
		A3-B4-C2-D2
		A3-B4-C2-D3
		A3-B4-C2-D4
		A3-B4-C2-D5
		A3-B4-C2-D6
		A3-B4-C3-D1
		A3-B4-C3-D2
		A3-B4-C3-D3
		A3-B4-C3-D4
		A3-B4-C3-D5
		A3-B4-C3-D6
		A4-B1-C1-D1
		A4-B1-C1-D2
		A4-B1-C1-D3
		A4-B1-C1-D4
		A4-B1-C1-D5
		A4-B1-C1-D6
		A4-B1-C2-D1
		A4-B1-C2-D2
		A4-B1-C2-D3
		A4-B1-C2-D4
		A4-B1-C2-D5
		A4-B1-C2-D6
		A4-B1-C3-D1
		A4-B1-C3-D2
		A4-B1-C3-D3
		A4-B1-C3-D4
		A4-B1-C3-D5
		A4-B1-C3-D6
		A4-B2-C1-D1
		A4-B2-C1-D2
		A4-B2-C1-D3
		A4-B2-C1-D4
		A4-B2-C1-D5
		A4-B2-C1-D6
		A4-B2-C2-D1
		A4-B2-C2-D2
		A4-B2-C2-D3
		A4-B2-C2-D4
		A4-B2-C2-D5
		A4-B2-C2-D6
		A4-B2-C3-D1
		A4-B2-C3-D2
		A4-B2-C3-D3
		A4-B2-C3-D4
		A4-B2-C3-D5
		A4-B2-C3-D6
		A4-B3-C1-D1
		A4-B3-C1-D2
		A4-B3-C1-D3
		A4-B3-C1-D4
		A4-B3-C1-D5
		A4-B3-C1-D6
		A4-B3-C2-D1
		A4-B3-C2-D2
		A4-B3-C2-D3
		A4-B3-C2-D4
		A4-B3-C2-D5
		A4-B3-C2-D6
		A4-B3-C3-D1
		A4-B3-C3-D2
		A4-B3-C3-D3
		A4-B3-C3-D4
		A4-B3-C3-D5
		A4-B3-C3-D6
		A4-B4-C1-D1
		A4-B4-C1-D2
		A4-B4-C1-D3
		A4-B4-C1-D4
		A4-B4-C1-D5
		A4-B4-C1-D6
		A4-B4-C2-D1
		A4-B4-C2-D2
		A4-B4-C2-D3
		A4-B4-C2-D4
		A4-B4-C2-D5
		A4-B4-C2-D6
		A4-B4-C3-D1
		A4-B4-C3-D2
		A4-B4-C3-D3
		A4-B4-C3-D4
		A4-B4-C3-D5
		A4-B4-C3-D6
		A5-B1-C1-D1
		A5-B1-C1-D2
		A5-B1-C1-D3
		A5-B1-C1-D4
		A5-B1-C1-D5
		A5-B1-C1-D6
		A5-B1-C2-D1
		A5-B1-C2-D2
		A5-B1-C2-D3
		A5-B1-C2-D4
		A5-B1-C2-D5
		A5-B1-C2-D6
		A5-B1-C3-D1
		A5-B1-C3-D2
		A5-B1-C3-D3
		A5-B1-C3-D4
		A5-B1-C3-D5
		A5-B1-C3-D6
		A5-B2-C1-D1
		A5-B2-C1-D2
		A5-B2-C1-D3
		A5-B2-C1-D4
		A5-B2-C1-D5
		A5-B2-C1-D6
		A5-B2-C2-D1
		A5-B2-C2-D2
		A5-B2-C2-D3
		A5-B2-C2-D4
		A5-B2-C2-D5
		A5-B2-C2-D6
		A5-B2-C3-D1
		A5-B2-C3-D2
		A5-B2-C3-D3
		A5-B2-C3-D4
		A5-B2-C3-D5
		A5-B2-C3-D6
		A5-B3-C1-D1
		A5-B3-C1-D2
		A5-B3-C1-D3
		A5-B3-C1-D4
		A5-B3-C1-D5
		A5-B3-C1-D6
		A5-B3-C2-D1
		A5-B3-C2-D2
		A5-B3-C2-D3
		A5-B3-C2-D4
		A5-B3-C2-D5
		A5-B3-C2-D6
		A5-B3-C3-D1
		A5-B3-C3-D2
		A5-B3-C3-D3
		A5-B3-C3-D4
		A5-B3-C3-D5
		A5-B3-C3-D6
		A5-B4-C1-D1
		A5-B4-C1-D2
		A5-B4-C1-D3
		A5-B4-C1-D4
		A5-B4-C1-D5
		A5-B4-C1-D6
		A5-B4-C2-D1
		A5-B4-C2-D2
		A5-B4-C2-D3
		A5-B4-C2-D4
		A5-B4-C2-D5
		A5-B4-C2-D6
		A5-B4-C3-D1
		A5-B4-C3-D2
		A5-B4-C3-D3
		A5-B4-C3-D4
		A5-B4-C3-D5
		A5-B4-C3-D6

 

[gargamalebarbosa]

Thank you ver much @smozgur ! . Perfect result. Çok teşekkür ederim.

 

[smozgur]

Thank you ver much @smozgur ! . Perfect result. Çok teşekkür ederim.

You're welcome! (İşe yaramasına sevindim)