pivotpowerquerynotion

How to turn comma separated list into Venn Diagram Format in Power Query


My goal is to create a ven diagram out of database data. I am happy to achieve this in 2 ways

  1. Using Flurish.com which requires me to solve this power query problem.

  2. 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.


Solution

  • 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"
    

    enter image description here