Creating Objective Table from Many-to-Many analysis

[Anonymous]

Hello Power Bi Community,

I am currently facing a challenge in determining a solution for the analysis presented below. The table on the left illustrates my analysis data, while the table on the right represents the desired output that I aim to obtain.

Within the left table, I have two columns, namely 'ID' and 'Project,' which maintain a many-to-many relationship. My objective is to create formulas for the four columns in the right table (the displayed results have been manually computed). In essence, the 'Mapping Projects' column should initially search for all Project patterns that match with a minimum occurrence of three project patterns (for example, 'Project A & Project B' would not be included).

Subsequently, these matching project patterns should be appended to each row, separated by commas.

The second column, 'Mapping IDs,' should tally the distinct IDs that contain the matching project patterns. As for the 'Projects' and 'IDs Count' columns, they should simply count the number of Projects listed in the pattern and the number of mapping IDs respectively.

I would greatly appreciate your guidance on how to achieve the desired output in the right table by utilizing formulas in either Power Query or Excel.

Thanks in advance for your support; your assistance is highly valued!

[lbendlin]

Consider using a graphical solution.  Putting your data into a matrix yields

One project with four IDs, various combinations of projects with three ids (some matching, some not) and then the outliers with two or one IDs.

This may already be sufficient to answer your business question.