Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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. ...
Need Help
lbendlin's avatar
lbendlin
Super User
4 years ago

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's avatar
lbendlin
Super User
4 years ago

And here is the second part - calculating the averages

 

 

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    Is this what you are referring to with %CV?

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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:

      SuperGroupGroupValue
      A1

      5

      A15
      A27
      A27
      A32
      A31
      A44
      A46
      B53
      B53
      C63
      C62
      C79
      C78

       

      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's avatar
        lbendlin
        Super User
        4 years ago

        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.