Create a Table with All Possible Combos from a Single Column and Data for Each Combo

[TheQ]

Hi everyone,

Posting here for the first time. This forum has helped a lot over the past few months of learning Power BI. I could use some help with something that I haven't seen in my googling.

Updated (7/25/22) the tables for clarification:

Let's say I have a data table that looks like this:

SuperGroup	Group	Value
A	1	5
A	1	5
A	2	7
A	2	7
A	3	2
A	3	1
A	4	4
A	4	6
B	5	3
B	5	3
C	6	3
C	6	2
C	7	9
C	7	8

My goal is to be able to tell which combination of groups would provide the lowest %CV within each SuperGroup. So ultimately, I'd like a table (table visual) that looks like this:

SuperGroup	Combo	Average	CV
A	1	5	0%
A	2	7	0%
A	3	1.5	47%
A	4	5	28%
A	1,2	6	19%
A	2,3	4.25	75%
A	3,4	3.25	68%
A	1,3	3.25	63%
A	1,4	5	16%
A	2,4	6	24%
A	1,2,3	4.5	56%
A	1,2,4	5.67	21%
A	2,3,4	4.5	58%
A	1,3,4	3.83	51%
A	1,2,3,4	4.625	48%
B	5	3	0%
C	6	2.5	28%
C	7	8.5	8%
C	6,7	5.5	64%

Another thing to note is that I will only ever be looking at 1-4 groups at a time, so the number of combos won't be endless, but the Groups will change over time (e.g. could be looking at combos with Groups 1201, 1202, 1203, 1204).

Is this possible?

Thanks in advance!

[lbendlin]

You can, but they come at a cost. You may run into Formula Firewall issues if not careful.  Note that you can also inline them into your main query, that should keep everything inside the same partition.

[tamerj1]

Hi @TheQ 

what is the %CV?

[TheQ]

%CV is the Standard Deviation / Average. By choosing the combo with the lowest %CV, I'd be able to tell which combination would have the lowest variability. This is just an easy DAX measure though. The part I'm having trouble with is setting up the table to provide each combo and values.

[lbendlin]

Interesting problem. Aren't you missing the four "groups of one"  ?

The Group average for 1,2 is 5.75, not 6.  Please correct your sample data.

Please explain what %CV means and how it is calculated.

[lbendlin]

Here is the first step - create all possible combinations:

Table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTJVitWBsEzALCMgyxyNZQxkGcFZhmCWCVwHiGWmFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Group = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group", type text}, {"Value", Int64.Type}})
in
    #"Changed Type"

Combo:

let
    Source = Table[Group],
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Removed Duplicates" = Table.Distinct(#"Converted to Table"),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Custom", each #"Removed Duplicates"),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1"}, {"Column2"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each #"Removed Duplicates"),
    #"Expanded Custom1" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"Column1"}, {"Column3"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded Custom1", "Custom", each #"Removed Duplicates"),
    #"Expanded Custom2" = Table.ExpandTableColumn(#"Added Custom2", "Custom", {"Column1"}, {"Column4"}),
    #"Added Custom3" = Table.AddColumn(#"Expanded Custom2", "Custom", each List.Sort(List.Distinct({[Column1],[Column2],[Column3],[Column4]}))),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom3", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Duplicates1" = Table.Distinct(#"Extracted Values", {"Custom"})
in
    #"Removed Duplicates1"

[lbendlin]

And here is the second part - calculating the averages

[lbendlin]

Is this what you are referring to with %CV?

[TheQ]

Sorry for the delayed response! Thank you for all this. I'm glad you think this is an interesting problem. I corrected the table in my original post. Also you're correct that the %CV is the standard deviation / average. Your solution worked perfectly based on the information I provided, so thank you! My exact situation is a little different though. So going one step up I don't need every combination ever across all entries, just related entries. Hopefully this table will help explain:

SuperGroup	Group	Value
A	1	5
A	1	5
A	2	7
A	2	7
A	3	2
A	3	1
A	4	4
A	4	6
B	5	3
B	5	3
C	6	3
C	6	2
C	7	9
C	7	8

To shorten the table so it doesn't spiral out of control as more and more Groups come in, how can I only look at combos within a SuperGroup? Essentially I don't want Groups from SuperGroup A to have combos with Groups from SuperGroups B and C, and vice versa.

[lbendlin]

This increases the complexity of the ask by at least an order of magnitude.  What you are basically asking is

"given a list of items, find all unique combinations across all the items in the list" 

My previous solution proposal assumed that you had four items in your list, but now this is becoming a dynamic size. It will take some time to refactor the code for this. 

[TheQ]

So now that I think about this again, I think what you originally provided solved "given a list of items, find all unique combinations across all the items in the list." The problem though is that I have a list of >20 items and it will continue to grow, so the size of the table is exploding. I only want to look at combinations of items with the SuperGroups to limit the number of combos to anywhere from 1-15 combos per SuperGroup.

[lbendlin · Accepted solution]

See attached for a solution with SuperGroups

[TheQ]

Ingenius! I didn't know you could create functions within Power Query. This worked perfectly with my much larger dataset. Thank you so much!

[TheQ]

Yeah, I think that sounds about right. Basically for SuperGroup A, I'd like to see all combos of Groups 1,2,3,4; for SuperGroup B, all combos of Group 5 (so just one); and for SuperGroup C, all combos of Groups 6,7. There will never be more than four Groups in a SuperGroup though so at least the number of columns would be constant. Thanks for all the help with this!