Forum Discussion
Create a Table with All Possible Combos from a Single Column and Data for Each Combo
See attached for a solution with SuperGroups
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.
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"
And here is the second part - calculating the averages
Is this what you are referring to with %CV?
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.