My goal is to create a ven diagram out of database data. I am happy to achieve this in 2 ways
Using Flurish.com which requires me to solve this power query problem.
Being suggested other software that can automatically create Ven Diagrams
The database table looks like this:
Name | Shared Interests |
---|---|
Person 1 | Camping, Road Trips, Acro, Firetwirling |
Person 2 | Camping |
Person 3 | Road Trips |
Person 4 | Road Trips |
Person 5 | Acro |
Person 6 | Firetwirling |
Person 7 | Camping, Road Trips |
etc |
Flurish.com requires me to get it in the format
Name | Value |
---|---|
Camping | 3 |
Road Trips | 4 |
Acro | 2 |
Firetwirling | 2 |
Camping-Road Trips | 2 |
Camping-Acro | 1 |
Camping-Firetwirling | 1 |
Camping-Road Trips-Acro | 1 |
Camping-Road Trips-Firetwirling | 1 |
Camping-Road Trips-Firetwirling-Acro | 1 |
... (etc with every possible combination separated by a -) | etc |
How can this be achieved?
I have attempted to create a unique list of all 'Shared Interests' but I don't know how to add new rows of every unique combination separated by a dash -
I then don't know how to calculate the value column of these overlapping groups. I suspect using pivot functions are the way to go here but so far I have not had any luck.
In powerquery try
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
// Combo algo adapted from from Bill Szysz 2017
process=(Items as list) as list =>
let AddIndex = Table.AddIndexColumn(Table.FromList(List.Sort(Items)), "Index", 0, 1),
ReverseIndeks = Table.AddIndexColumn(AddIndex, "RevIdx", Table.RowCount(AddIndex), -1),
Lists = Table.AddColumn(ReverseIndeks, "lists", each List.Repeat(List.Combine({List.Repeat({[Column1]}, Number.Power(2,[RevIdx]-1)),List.Repeat( {null}, Number.Power(2,[RevIdx]-1))}), Number.Power(2, [Index]))),
ResultTable = Table.FromColumns(Lists[lists]),
AllCombos = List.Sort(List.RemoveLastN(Table.AddColumn(ResultTable, "Custom", each Text.Combine(List.RemoveNulls(Record.ToList(_)),"-"))[Custom],1))
in AllCombos,
#"Added Custom" = Table.AddColumn(Source, "combos", each process(Text.Split([Shared Interests],", "))),
#"Expanded combos" = Table.ExpandListColumn(#"Added Custom", "combos"),
#"Grouped Rows" = Table.Group(#"Expanded combos", {"combos"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in #"Grouped Rows"