How to setup multiple columns for visualization

Hi all,

I have been wrestling with an issue that was partially addressed by this forum but I can't quite get to a final solution. I am hoping the explanation below will help paint a picture of what I'm trying to achieve and hopefully someone much more clever than me will have a suggestion :)

I have a pbi file with multiple tables linked to Oracle, two of which are of particular interest:

. one table 'Transactions' which pulls all purchase transactions (thousand of lines of purchase orders) with a column containing a stock number [STOCK NUMBER]. THis stock number appears multiple times in the table

. one table 'Current Inventory' which pulls all distinct stock numbers along with various data such as drawing number, manufacturer details, etc. I have 4 columns of interest [Manufacturer #1], [Manufacturer #2], [Manufacturer #3] and [Manufacturer #4]. This is because one part can theoritically be manufactured by 4 different companies (whilst bought from only one).

I have already concatenated the 4 manufacturer columns so I can have a text filter to display all purchases relating to items falling under a specific manufacturer.

Now here is what I'd like to do: have a graph (ie pie chart) which shows me who are the manufacturers that are being the stock numbers we purchase the most. Problem is, stock number 1 could have vendor A listed in [Manufacturer #1], stock number 2 could have nothing, stock number 3 could have Vendor A listed in Manufacturer #1 as well as Manufacturer #2, etc etc.

I thought of creating a new table with two columns, one listing stock numbers (duplicated) and the other the manufacturers (being a combination of all 4 fields above). I'm not sure how to do this and then wonder what relationship to create to be able to do all this filtering.

Does anyone have a clever idea?

Thanks in advance :)

OF