Forum Discussion
Create a Table with All Possible Combos from a Single Column and Data for Each Combo
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. ...
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"