cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TheQ
Frequent Visitor

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.


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

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

SuperGroupGroupValue
A1

5

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

 

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:

SuperGroupComboAverageCV
A150%
A270%
A31.547%
A4528%
A1,2619%
A2,34.2575%
A3,43.2568%
A1,33.2563%
A1,4516%
A2,4624%
A1,2,34.556%
A1,2,45.6721%
A2,3,44.558%
A1,3,43.8351%
A1,2,3,44.62548%
B530%
C62.528%
C78.58%
C6,75.564%

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!

1 ACCEPTED SOLUTION

See attached for a solution with SuperGroups

View solution in original post

13 REPLIES 13
lbendlin
Super User
Super User

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
Super User
Super User

Hi @TheQ 

what is the %CV?

TheQ
Frequent Visitor

%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
Super User
Super User

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"

 

lbendlin_0-1658606311950.png

 

And here is the second part - calculating the averages

 

lbendlin_1-1658607144664.png

 

Is this what you are referring to with %CV?

 

lbendlin_2-1658608449113.png

 

TheQ
Frequent Visitor

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.

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
Frequent Visitor

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.

See attached for a solution with SuperGroups

TheQ
Frequent Visitor

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
Frequent Visitor

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!

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.