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